Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing sets of data, where criteria met, sum certain columns.
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing sets of data, where criteria met, sum certain columns.
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing sets of data, where criteria met, sum certain column
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" wrote: 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing sets of data, where criteria met, sum certain column
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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing sets of data, where criteria met, sum certain column
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing sets of data, where criteria met, sum certain column
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing sets of data, where criteria met, sum certain column
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 "Otto Moehrbach" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing two sets of columns | Excel Discussion (Misc queries) | |||
Comparing two different sets of data with common element | Excel Worksheet Functions | |||
Comparing two sets data for different month | Excel Discussion (Misc queries) | |||
help comparing two sets od data to find the odd data | Excel Worksheet Functions | |||
Comparing Data in 2 columns | Excel Worksheet Functions |