Comparing sets of data, where criteria met, sum certain columns.

[Kimberly]

Microsoft 2003 - I have to compare data extracted from one application to
data extracted from another application and resolve all discrpancies between
the two files. Any ideas on how to do this?
The file has 8 columns - 4 from one application and 4 from the other
application (columns a1:d55 is company "XYZ", columns e1:h500 is company
"ABC"). I need the formula to subtract the contents of column 'd' from
column 'h' where contents of column 'a' are found in column 'e'. These are
not always on the same row. Sometimes the match occurs on row one of column
'a' and row three of column 'e'. In addition it is also possible that no
match is found at all. Any suggestions would be appreciated. Thanks.

 

[Otto Moehrbach]

You want to take every value in Column A and find a match of that value
anywhere in Column E, if it's there. If it is there, you want to subtract
the value in Column D, in the row of the Column A item, from what's in
Column H in the row of the same item found in Column E. Is that right?
Question: Where do you want the result of the subtraction to be placed?
Another question: Do the items in Column A appear, if at all, only once in
Column E. If they appear more than once, what do you want to do with each
one? HTH Otto

 

[Kimberly]

Thanks so much for the quick response, Otto!
To answer your questions -
yes, "subtract the value in Column D, in the row of the Column A item, from
what's in Column H in the row of the same item found in Column E"
I would like the results in column I (the lookup) and J (sum of the values)

yes - items in A should only occur once in column E

 

[Otto Moehrbach]

Kimberly
You say:
I would like the results in column I (the lookup) and J (sum of the values).
Of what row? The row of the Column A item or the row of the Column E item?
Also, what is the "the lookup" that you want in Column I? I take the "sum
of the values" to mean the difference that you want calculated. FYI, I plan
on writing a macro to do this. Otto

 

[Kimberly]

Cool! O.K. I would like column 'I' to be the content of 'A' that was found
in 'E'. Column 'J' would be the sum of 'D' (on the same row as 'A') and 'H'
on same row matching content s of 'A'
For example:
Col. A B C D E F G H I J
eee-123 CA xxx 100 aaa-123 CA xxx 100 eee-123 0 =SUM(D1-H3)
eee-256 CA xxx 105 bbb-256 CA xxx 101
efd-111 CA xxx 101 eee-123 CA xxx 100
elu-2001 CA xxx 103 efd-111 CA xxx 101
elu-2002 CA xxx 104 elu-2001 CA xxx 103
kjl-123 CA xxx 102 elu-2003 CA xxx 104
fff-256 CA xxx 105 kjl-123 CA xxx 102
IF(A1 is found in column E, go back to row1 column D and
subtract that amount from row3 column H

I hope that helps.

 

[Otto Moehrbach]

Kimberly
Your use of the word "Sum" and the word "subtract" is confusing. I
think you mean subtract. If you were using a formula for that you would use
=D1-H3, not =SUM(D1-H3), although both give the same answer.
The following macro will do what I think you want. This macro will operate
on the active sheet. This macro loops through all the values in Column A
and searches for each of these values in Column E. If the value is found,
the macro will place the value of the Column A item in Column I in the same
row as the column A item. It will also subtract the value in Column H in
the row of the found value in Column E, from the value in Column D in the
row of the value in Column A. This difference will be placed in the same
row as the Column A item. That is a tongue twister! Come back if you need
more. Otto
Sub FindDups()
Dim rColA As Range
Dim rColE As Range
Dim i As Range
Dim FoundCell As Range
Application.ScreenUpdating = False
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set rColE = Range("E2", Range("E" & Rows.Count).End(xlUp))
For Each i In rColA
If Not rColE.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing
Then
Set FoundCell = rColE.Find(What:=i.Value, LookAt:=xlWhole)
Cells(i.Row, 9).Value = i.Value
Cells(i.Row, 10).Value = i.Offset(, 3).Value -
FoundCell.Offset(, 3).Value
End If
Next i
Application.ScreenUpdating = True
End Sub

 

[Kimberly]

Hey Otto. I have to admit I think this is above my skill level. I attempted
to run the macro and got the following message:
"compile error
syntax error"
with 'Dim rColA As Range' highlighted

What do I do next?
Thanks.
Kim