Excel Function Go!

[AnticorRifling]

Ok I need an excel function that does the following:

Finds a value in column P (values are blank or 1) if it's one looks at column A, then takes the value of column A and compares it to the value of column A on sheet 2. Output of any instance where P=1 As1 != As2.

GO!

[Danical]

Something like this?

=IF(P1=0,"skipped",IF(Sheet2!A1=A1,"equal","not equal"))

EDIT2: Try the above.

[Celephais]

a VBA function would work nicely but... um... what's wrong with just:
=IF(P1=1,IF(A1=Sheet2!A1, A1, ""),"")

in the Q column (or whatever) and copying it down?
Then select unique ... do you need it output in some special format?

[Danical]

What he said.

[AnticorRifling]

Ok close thank you. I need it to take First value of sheet1 column A [A2 (a1 being header)] and compare it to all values of sheet2 column A [A2 thru A4039]

Output to be a list on sheet three. So I can see any instance where sheet1 column A (with a P value of 1) does not exist anywhere in sheet 2 column 2 output being a list of those variances.


Basically I've got converted database data from another company and they kindly sent it in multiple formats and layouts and I need to marry up a reference number (column A) that could have additional attached data (P =1 means yes it's got more data) and sheet 2 column A should have that reference number and associated data. I want to know how many fucked up instances I have where I'm going to have to dig in and recreate this data.

[Danical]

Use an absolute reference.

[Danical]

Using Celephais's:

=IF(P2=1,IF($A$2=Sheet2!A2, A2, ""),"")

throw this function in the second row of whatever column and drag it down for your 4039 rows.

[Celephais]

edit: misread, yeah what dan said about the absolute ... the $ keeps the values from changing when you drag, otherwise:

right... paste what I said into sheet1 Q1 ... then drag the little block in the bottom right down to Q4039 ... then Ctrl-C, select R1, Ctrl-V, click the clipboard and select special paste or whatever, values only, then click the R column, and sort (which will compress it for you). done.

[Suppa Hobbit Mage]

Basically did the same thing they did. Download the txt file and rename the extention xls because we are retarded and can't upload excel files.

[Kuyuk]

it's a trojan!!!!

[AnticorRifling]

Silly face don't freaking fail me!

FYI this data conversion is for another analyst I really don't care one way or the other. I'm currently neck deep in resolving credit card authorization software issues. Apparently the data has to be valid for a CC to get authorized who knew?!

[AnticorRifling]

Either I'm an idiot (which is possible) and I'm doing it wrong or it's just not going to do what I want with basic excel. I'm tempted to have them try sending me data again, in correct formats per our agreements, but I think it will take them way too long because they are those kinds of people.

Man it would have been so easy if they would have given me the flat files I wanted to drop on the iSeries so I could just query them. Fuckers.

[Danical]

What are you trying to do now?

Would a pivot table be helpful?

[Kuyuk]

http://www.irintech.com/x1/images/jean/flowchart_coworker.gif

[AnticorRifling]

That's pretty accurate.

What I'm trying to do is compare to data sets with 3 variables that was provided by a client's old software company. They're moving to our ERP and we need to convert their historical data. The other company is being ass hurt that they lost this client so they're not helpful at all.

Data set 1 (sheet 1) has Customer information Data set 2 (sheet 2) has customer information that's supposed to exist if sheet 1 has a value in column P (that 1/blank I mentioned earlier) What I'm looking for is a way to compare if sheet 1 column P=1 take sheet 1 corresponding column A value and compare to sheet 2 column A and show me where a match does not exist.

It can't be straight Sheet1!A1=Sheet2!A1 there's a chance it could be Sheet1!A1=Sheet2!A4039 so it needs to search all of Column 1 for a match. If it finds a match disregard, if it doesn't find a match output Sheet1!A* value to a new column so I can dig into why there's no match.

I could do this real fast using query. The other company is going to be fined for failing to meet exit terms they agreed to including data provided in agreed formats but I don't care that doesn't fix my issue of providing accurate data moving foward.

[Kuyuk]

That's pretty accurate.

What I'm trying to do is http://4.bp.blogspot.com/_rsZ3PQM7ufM/SmXPYaVzw5I/AAAAAAAABh4/oAPgTHtjUTo/s320/Wingdings.gif

fixed.

[radamanthys]

That's pretty accurate.

What I'm trying to do is compare to data sets with 3 variables that was provided by a client's old software company. They're moving to our ERP and we need to convert their historical data. The other company is being ass hurt that they lost this client so they're not helpful at all.

Data set 1 (sheet 1) has Customer information Data set 2 (sheet 2) has customer information that's supposed to exist if sheet 1 has a value in column P (that 1/blank I mentioned earlier) What I'm looking for is a way to compare if sheet 1 column P=1 take sheet 1 corresponding column A value and compare to sheet 2 column A and show me where a match does not exist.

It can't be straight Sheet1!A1=Sheet2!A1 there's a chance it could be Sheet1!A1=Sheet2!A4039 so it needs to search all of Column 1 for a match. If it finds a match disregard, if it doesn't find a match output Sheet1!A* value to a new column so I can dig into why there's no match.

I could do this real fast using query. The other company is going to be fined for failing to meet exit terms they agreed to including data provided in agreed formats but I don't care that doesn't fix my issue of providing accurate data moving foward.

You're probably gonna wanna use a VBA macro for that. Something like "if cell in range 1 does not match any cell in range 2, output to range 3" I haven't written any vba in a while, so I'm probably not the best person to be throwing code at you (*cough*Celephais*cough*). I'd likely want to nest 2 loops to compare each record in order... which would be about 16 million passes. I loathe algorithm efficiency.

And in the end... who cares about data integrity? Make up the numbers!

[AnticorRifling]

If that would work then ya that would be great. I'm just pissed at myself that I can't figure out a way to do it quickly outside of my comfort zone.

[radamanthys]

If that would work then ya that would be great. I'm just pissed at myself that I can't figure out a way to do it quickly outside of my comfort zone.

There's one other option. Go to 'data' and sort each column. Open both sheets, put them next to eachother and manually compare them. It's only 4000 records. You'll be able to figure out how many records are missing by comparing the size of the tables (One goes to row 4099 the other to row 4199, means you've got a difference of 100 records. Then you'll know if you caught them all when you compare).

Just another option.



Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
With ws2.UsedRange
lr2 = .Rows.Count
lc2 = .Columns.Count
End With
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2
DiffCount = 0
For c = 1 To maxC
Application.StatusBar = "Comparing cells " & Format(c / maxC, "0 %") & "..."
For r = 1 To maxR
cf1 = ""
cf2 = ""
On Error Resume Next
cf1 = ws1.Cells(r, c).FormulaLocal
cf2 = ws2.Cells(r, c).FormulaLocal
On Error GoTo 0
If cf1 <> cf2 Then
DiffCount = DiffCount + 1
Cells(r, c).Formula = "'" & cf1 & " <> " & cf2
End If
Next r
Next c
Application.StatusBar = "Formatting the report..."
With Range(Cells(1, 1), Cells(maxR, maxC))
.Interior.ColorIndex = 19
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
On Error Resume Next
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
On Error GoTo 0
End With
Columns("A:IV").ColumnWidth = 20
rptWB.Saved = True
If DiffCount = 0 Then
rptWB.Close False
End If
Set rptWB = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox DiffCount & " cells contain different formulas!", vbInformation, _
"Compare " & ws1.Name & " with " & ws2.Name
End Sub
This example macro shows how to use the macro above:
Sub TestCompareWorksheets()
' compare two different worksheets in the active workbook
CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
End Sub


Google hit.
http://www.exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Exce l/477.html

[Liagala]

It can't be straight Sheet1!A1=Sheet2!A1 there's a chance it could be Sheet1!A1=Sheet2!A4039 so it needs to search all of Column 1 for a match. If it finds a match disregard, if it doesn't find a match output Sheet1!A* value to a new column so I can dig into why there's no match.

I'm probably missing something here, but... Sheet1, Column B =Countif(Sheet2!A$1:A$4039,Sheet1!A1). Sheet 3, column A =IF(Sheet1!B1=0,Sheet1!A1,"")

It'll add the number of times each entry in sheet 1 appears in sheet 2. If that number >0, Sheet 3 will have a blank line. If 0, it'll put the name in. Sort it to toss the blanks and condense the lines, and you're good. No?

[AnticorRifling]

I only want it to look at sheet1!A if Sheet1!P=1

[AnticorRifling]

There's one other option. Go to 'data' and sort each column. Open both sheets, put them next to eachother and manually compare them. It's only 4000 records. You'll be able to figure out how many records are missing by comparing the size of the tables (One goes to row 4099 the other to row 4199, means you've got a difference of 100 records. Then you'll know if you caught them all when you compare).

Just another option.



Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
With ws2.UsedRange
lr2 = .Rows.Count
lc2 = .Columns.Count
End With
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2
DiffCount = 0
For c = 1 To maxC
Application.StatusBar = "Comparing cells " & Format(c / maxC, "0 %") & "..."
For r = 1 To maxR
cf1 = ""
cf2 = ""
On Error Resume Next
cf1 = ws1.Cells(r, c).FormulaLocal
cf2 = ws2.Cells(r, c).FormulaLocal
On Error GoTo 0
If cf1 <> cf2 Then
DiffCount = DiffCount + 1
Cells(r, c).Formula = "'" & cf1 & " <> " & cf2
End If
Next r
Next c
Application.StatusBar = "Formatting the report..."
With Range(Cells(1, 1), Cells(maxR, maxC))
.Interior.ColorIndex = 19
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
On Error Resume Next
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
On Error GoTo 0
End With
Columns("A:IV").ColumnWidth = 20
rptWB.Saved = True
If DiffCount = 0 Then
rptWB.Close False
End If
Set rptWB = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox DiffCount & " cells contain different formulas!", vbInformation, _
"Compare " & ws1.Name & " with " & ws2.Name
End Sub
This example macro shows how to use the macro above:
Sub TestCompareWorksheets()
' compare two different worksheets in the active workbook
CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
End Sub


Google hit.
http://www.exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Exce l/477.html

Only problem is if the value of sheet1!P=1 then it can have limitless values of Sheet2!A and be acceptable. So they aren't really a 1 for 1 per say.

[Liagala]

I only want it to look at sheet1!A if Sheet1!P=1

Sheet1, Column B =If(Sheet1!P1=1,Countif(Sheet2!A$1:A$4039,A1),1). Sheet 3, column A =IF(Sheet1!B1=0,Sheet1!A1,"")

There you go. If it's a one, it'll look. If it isn't, it'll pretend there's a match (since it seems that you have absolutely no interest in things that match, this removes it from consideration).

[AnticorRifling]

Sexy, I owe you naked time.

[AnticorRifling]

Liagala not quite working. Looking at my output on sheet three if I take returned values I can find them on sheet 2. I need values on sheet three to exist on sheet1 (with a positive P column value) and not exist on sheet 2.

Gettin closer though, I appreciate you guys taking the time to at least look at it.

[Celephais]

*cough*Celephais*cough*
Sorry, busy killing zombies.

[Liagala]

Liagala not quite working. Looking at my output on sheet three if I take returned values I can find them on sheet 2. I need values on sheet three to exist on sheet1 (with a positive P column value) and not exist on sheet 2.

Gettin closer though, I appreciate you guys taking the time to at least look at it.

Before I go poking into this... they're all spelled the same, without extraneous spaces, right? That'll make it think there's no match, when there really is.

[radamanthys]

Sorry, busy killing zombies.

Haha, way better than coding and actually being a zombie.

[Jorddyn]

Ok I need an excel function that does the following:

Finds a value in column P (values are blank or 1) if it's one looks at column A, then takes the value of column A and compares it to the value of column A on sheet 2. Output of any instance where P=1 As1 != As2.

GO!

Ok, so I understand this:

Sheet 1, Column A has data. Sheet 1, Column P is sometimes blank, sometimes 1.

Sheet 2 has some data that is a subset of Sheet 1 (or a subset of sheet 1 plus some data you don't care about).

If Sheet 1 Column P is blank, you want to ignore what is in column A.

If column P is a 1, and the value in column A exists on sheet 2, you want to ignore it.

If column P is a 1 and the value in column A does not exist on sheet 2, you want to display.

If your data on sheet 1 starts in A1, then on sheet 3, do this in a1:

=if(sheet1!p1="","",if(isna(vlookup(sheet1!a1,sheet2!a:a,1,false)),sh eet1!a1,""))

[AnticorRifling]

You guys are so smrt.

[Jorddyn]

You guys are so smrt.

Cookies, please.

[AnticorRifling]

What the sperm burping mother fuck?!

Ok I've got a brand spankin new spreadsheet, no data, no formulas, I set the column to numeric, I put a 16 digit number in and it fucking rounds it. Changes the last digit from a 0 to a 1, it's a whole number, and I'm sure as fuck not requesting rounding. Fucking hell morning wasted and afternoon looking shitty.

Fix it.

[AnticorRifling]

Well fuck me:

That is as per Excel's specifications (see documentation) - the max
number of significant figures for a numric value is 15.

Easy fix, pain my ass but easy fix. Waste of a morning. Damnit.

[Cephalopod]

Obvious answer, if you aren't doing math with them: text fields will work for 16-digit numbers. Probably not what you want in this case, though.

[AnticorRifling]

Obvious answer, if you aren't doing math with them: text fields will work for 16-digit numbers. Probably not what you want in this case, though.

Yeah. I'm just going to split it into two columns, then concatenate them. Should give me the result I need.

[AnticorRifling]

Ok new problem.


Sheet 1 is my consolidated totals

Sheet 2 totals from report X

Sheet 3 totals from report Y

When I use a formula of =sum(sheet 2 c1, sheet 3 c2) and both numbers are positive it's fine. If one of the numbers is a negative value it shits the bed and returns a #VALUE! Why? I've got the fields set as numeric and displaying negatives. What do I need to adjust?

It's also pissing me off if my budget is 0 and my actual spent is 5, then I can't have Budget - Actual to get a variance because 0 - 5 yielding -5 is too tough apparently.....fuckin hell

[Celephais]

You're missing something... I had no problems just having a sheet sum two cells from two other sheets when one was negative and the other was positive.

[Cephalopod]

You're definitely doing something wrong. I just did exactly what you described, and it worked fine.

=SUM(Sheet2!C1,Sheet3!C2)

Sheet 2, C1 = 50000
Sheet 3, C2 = -50500

Result on sheet 1: -500

[AnticorRifling]

Well fuck let me see what I'm doing wrong.

[AnticorRifling]

Ok let's pretend for some reason when the data imported into excel the negative sign is on the right, so I'm seeing data as 50500-.

So if I do

=sum(Sheet2!C1,Sheet3!C2)

Sheet 2, C1 = 50000
Sheet 3, C3 = 50500-

result on sheet 1 = value of sheet 2, C1

Grrr

[Celephais]

Are you just going to be summing a defined cell? Or do you need to sum a range?

[Celephais]

Bit of a hack, but if you make a row, D, and set it equal to:
IF(ISNUMBER(Sheet2!C1), Sheet2!C1, LEFT(Sheet2!C1, LEN(Sheet2!C1)-1)*-1)
Drag the formula down and then do the same for Row E except:
IF(ISNUMBER(Sheet3!C1), Sheet3!C1, LEFT(Sheet3!C1, LEN(Sheet3!C1)-1)*-1)

Then change your original sum to SUM(D1, E2) it'll work.

[ElvenFury]

Bit of a hack, but if you make a row, D, and set it equal to:
IF(ISNUMBER(Sheet2!C1), Sheet2!C1, LEFT(Sheet2!C1, LEN(Sheet2!C1)-1)*-1)
Drag the formula down and then do the same for Row E except:
IF(ISNUMBER(Sheet3!C1), Sheet3!C1, LEFT(Sheet3!C1, LEN(Sheet3!C1)-1)*-1)

Then change your original sum to SUM(D1, E2) it'll work.

http://unrealitymag.com/wp-content/uploads/2009/09/pwned8_20090902_2025217188.jpg

[AnticorRifling]

Bit of a hack, but if you make a row, D, and set it equal to:
IF(ISNUMBER(Sheet2!C1), Sheet2!C1, LEFT(Sheet2!C1, LEN(Sheet2!C1)-1)*-1)
Drag the formula down and then do the same for Row E except:
IF(ISNUMBER(Sheet3!C1), Sheet3!C1, LEFT(Sheet3!C1, LEN(Sheet3!C1)-1)*-1)

Then change your original sum to SUM(D1, E2) it'll work.
I think I get what you're saying. I'll give it a go here once I change something on the iseries that's being a bitch.

[Jorddyn]

I think I get what you're saying. I'll give it a go here once I change something on the iseries that's being a bitch.

Insert column to the right. Copy paste original data in. Text-to-columns delimited with a -, choose "do not import" for any column after the first, tada! Now you have what you want in the next column.

Or, if you don't care about keeping the original data in the first column, just use replace (ctrl-H) and replace "-" with "".

Edited to add: Ok, crap, you want to actually make it a negative and not just remove the dash?

Insert a column to the right. =if(right(a1,1)="-",-val(left(a1,len((a1)-1)),a1)). Or what Cele said, just mildly modified.

Don't remember if it is "val" or "value", though.

[AnticorRifling]

I'll be trying both tomorrow at work. Right now I'm not wearing pants.

[Mighty Nikkisaurus]

http://unrealitymag.com/wp-content/uploads/2009/09/pwned8_20090902_2025217188.jpg

WHHHHHHHY.

:(

[Celephais]

WHHHHHHHY.

:(
Haven't you heard? That's pretty much what I look like.

And Jord, it's Value, not val, but you don't even need it.

[AnticorRifling]

This is why I hate retarded financial controllers/comptrollers. We've got four companies with X and Y selling to Z but we want the P&L to only reflect certain accounts that are hitting the G/L in regards to sales of T. So yeah can you do that? Of course I can. Oh can you also get it off the iSeries because we're retarded and want paper copies of shit for no reason? Yes, I can do that. Oh can you also give us an inclusive consolidated report that excludes Y? Uhh if it's excluding Y how is it inclusive? ....

Fun fun fun

[Jorddyn]

Haven't you heard? That's pretty much what I look like.

And Jord, it's Value, not val, but you don't even need it.

I've gotten so used to our report writing software where you would need it, my Excel-fu is slacking.

Or I just suck at this.

Either way.

/beer

[AnticorRifling]

Works like a dream. Thanks guys I appreciate it. Basically took this task and reduced the time involved for getting the aggregate by about 1 hour and 45 minutes :)

[Stanley Burrell]

Right now I'm not wearing pants.

Lookin' like a fool with yo pants on the ground.

[ElvenFury]

WHHHHHHHY.

:(

You mean you didn't know that Cel is a 600 lb man who regularly teabags his conquered foes?!

[Cephalopod]

You mean you didn't know that Cel is a 600 lb man who regularly teabags his conquered foes?!

From that picture, it appears he also goes by the superhero nickname 'Lobsterman'. Although to be honest, that looks sorta like Earthquake with dyed hair to me...

[Celephais]

That's actually Bacl, but he stole the move from me.

[Clove]

This is why I hate retarded financial controllers/comptrollers. We've got four companies with X and Y selling to Z but we want the P&L to only reflect certain accounts that are hitting the G/L in regards to sales of T. So yeah can you do that? Of course I can. Oh can you also get it off the iSeries because we're retarded and want paper copies of shit for no reason? Yes, I can do that. Oh can you also give us an inclusive consolidated report that excludes Y? Uhh if it's excluding Y how is it inclusive? ....

Fun fun funVP of Sales "Are monthly minimum fees included in the daily revenue summary?"
Me "Only on the last day of the month..."

[Liagala]

I was pulling a report for our CFO one day.

Him - I need the last 365 days, it can't be 12 months.
Me - Sure, no problem
Him - I'm not sure how you're going to do it though.
Me - ... 10/21/06 - 10/20/07?
Him - But it has to be 365 days.
Me - ... that is 365 days.
Him - But don't the 30 day months screw it up?

[Celephais]

I forget the exact line but... something like one of the easist ways for a manager to get rid of an unproductive stupid employee in a corporation is to promote them. You don't want to promote away your best employees.

He didn't become CFO for no reason.

[Clove]

I was pulling a report for our CFO one day.

Him - I need the last 365 days, it can't be 12 months.
Me - Sure, no problem
Him - I'm not sure how you're going to do it though.
Me - ... 10/21/06 - 10/20/07?
Him - But it has to be 365 days.
Me - ... that is 365 days.
Him - But don't the 30 day months screw it up?Only on leap years.

[AnticorRifling]

Tell him you're using the mayan calendar so it's ok.

[michaelkeenan]

Basically did the same thing they did. Download the txt file and rename the extention xls because we are retarded and can't upload excel files.

damn... that is not a text file???

[robertbell]

what is the formula to find duplicate values in excel 2007??