Need help matching 2 sets of columns!

**StiltsCC** · 02-15-2013, 04:27 PM

Hello- First post here, as I am desperately trying to complete a tax project.

I have 4 columns of data (essentially the same 2 sets: 1 column of account numbers, and 1 column of account values), from 2 different sources, next to each other on the same worksheet.

Unfortunately, the amount/length of data between the 2 sets doesn't match up, or this might be easier (the set on the right is about 140 rows longer).

Example:

Number Value Number Value

441577 79111.62 441520 250023.96
441576 76524.28 441577 79111.62
442029 122389.32 441576 76524.28
442066 111582.95 442029 122389.32
442080 27570.9 442066 111582.95
442084 84623.45 442080 27570.90
442087 25449.06 442084 84623.45
442105 98158.47 442087 25449.06

The end goal is to make sure the values in the available columns match up with the corresponding account numbers/values in the other set, so I can fix the accounts whose values don’t match.

I can disregard and delete any number/value that is in 1 set but not the other for now.

Thank you in advance! I'll be standing by to answer any questions

**Melvinrobb** · 02-15-2013, 05:01 PM

You want to use VLOOKUP
Put the second list in columns F & G, your first list in columns B and C, and the formula in Column D returns the appropriate value.

**newdoverman** · 02-15-2013, 05:20 PM

If your first set of data is in columns A and B, insert 2 helper columns between the data sets and enter this formula in the new column C and copy down the length of your data:

=IF(COUNTIF(E:E,A1)>0,1,"") This will enter a 1 beside every entry that is found in the second data set that is in the first data set.

The second data set being in columns E and F enter this formula in column G and copy down the length of the data. Column D will be empty just to make it easier to read.

=IF(COUNTIF(A:A,E1)>0,1,"") This will enter a 1 beside every entry that is found in the first data set that is in the second data set.

Sort both data sets using column C for the first Data set and column G for the second data set. All the data that have blank cells in columns C and G are unique values. Now you have to decide what you want to do with these unique values.