So i have two list that i want to compare and see if the names and the numbers match with the other list. Column A has the names and Column B has the amounts from the first list. Column C has the names and column D has the amounts form the second list. On the second list the names might appear more than once with different amounts, so i would like it to add those amounts together if the name is the same and compare it to the first list. Can anybody please help??
Answers (1)
it is not very clear to me whether the second list would have different names compared to the first one.
However, to add together the numbers in the second list is simple: just pivot the table by name (column C) and choose to sum the numbers in column D.
If you then whant to see if there is a perfect match between the tables: copy/paste special "values" of bot tables in a separate worksheet. Then use "concatenate(A,B)/(CD)" to create a single string of name & number. Sort both colums. Use "exact()" to compare the 2 new columns. When you see "FALSE" in the result look at that row and adjust the tables in case the following lines are OK. Repeat the procesure till the last "FALSE" is resolved. Hope you do not have thousands of lines.