Align 2 sets of data on a single row to compare corresponding values

Hi all,

Hopefully somebody can help me.

I have a spreadsheet which is something like the 'example' spreadsheet attached. It has 2 sets of data in it (colums A and B are 1 set, columns C and D are set 2). This data contains an overlap in reference numbers which you should be able to see from the data. However, I know that data set 1 is correct while the quantities in data set 2 needs updating.

What I am looking to do is make it so that the reference numbers line up along with their corresponding 'quantity' so that I can compare the two quantities to see if there are any discrepancies in the 2 sets of data.

I have (manually) on 'sheet 2' completed what I am looking to achieve or something along those lines so that I can compare the respective quantity fields.

I was wondering if anybody could help me in producing something similar for a spreadsheet containing hundreds of entries?

Many thanks,

Andy

There are a couple of ways to do this without needing to manually align values across the 2 sets.

You could for example do a VLOOKUP to pull the values from one to the same row in the other list and compare that way.

Ex (in E2 on sheet "Example")

Formula:

Please Login or Register  to view this content.

This will pull the qty from the 1st set next to the qty in the second set, regardless of the order of both sets. Do be aware that VLOOKUP stops at the first match it finds, so this works only when your Reference 1/Reference2 are unique values, ie ABC only appears in reference 1 a single time.

You could then manually update if you like, which could be as simple as copying the value of the formula over your previous qty 2 values. You could expand the formula to compare what was pulled to the qty 2 values and produce results that better fit your needs, IE having it only return/show the number from VLOOKUP when it is different than existing qty 2.

If VLOOKUP returns N/A error it means the lookup value was not in the lookup array, ie it cant find "ABC" in the 1st set. This could be valid, its really not there, or it could be due to differences in how they are entered. Ex: 1 of them has an extra space in it ("ABC" is not the same as "ABC "). It could be data type issue if the lookup values are numeric (1 as a number and 1 as text are not the same).

Hope this helps