Struggling with Comparing 2 columns to 2 other columns

[jone05]

Hi all,
I've search Google and this forum and have been having a really tough time getting this formula right! I'm using Excel 2007 on Windows 7.

Here's the situation: I have 4 columns of data. Columns A and B are the product number(A) and customer info(B) from system 1. Columns C and D are the product number(C) and customer info(D) from system 2.

I need to be able to compare the info from A and B against the info from C and D. If the items are different, I need that to be noted in column E (doesn't matter if it says False, Variance, Different, etc.).

Thanks in advance! Please let me know if more information is needed.

 

[sfpowell]

Put this formula in E1 and copy down:

=IF(A1&B1=C1&D1,"","Different")

 

[jone05]

Thanks, sfpowell! This formula works great. However, my concern (and I should have mentioned this) is that the product number/customer info isn't always in the same row between AB and CD.

For example: Customer 1 is in row 2 in the AB pairing. However, Customer 1 is in row 8 in the CD pairing.

Does this makes sense?

 

[sfpowell]

I should have guessed it wouldn't have been that straightforward.
In that case, there might be an easier/better way to do it, but I would insert a column at the beginning and put this formula in the new Column A and drag down:

=B1&C1

This combines the data from the first source and makes it searchable.
Next put the following formula in Column F and drag down.

=IF(ISNA(VLOOKUP(D1&E1,$A$1:$A$100,1,0)),"Different","")

In this formula make sure you include the $'s when referencing the Column A data and change the '100' to be whatever the bottom row of your data is.
If you want to clean it up when you are done, you can do a Copy - PasteSpecial Values on Column F and then delete Column A.

 

[jone05]

I should have guessed it wouldn't have been that straightforward.
In that case, there might be an easier/better way to do it, but I would insert a column at the beginning and put this formula in the new Column A and drag down:

=B1&C1

This combines the data from the first source and makes it searchable.
Next put the following formula in Column F and drag down.

=IF(ISNA(VLOOKUP(D1&E1,$A$1:$A$100,1,0)),"Different","")

In this formula make sure you include the $'s when referencing the Column A data and change the '100' to be whatever the bottom row of your data is.
If you want to clean it up when you are done, you can do a Copy - PasteSpecial Values on Column F and then delete Column A.

This is incredible. Thanks so much for your assistance!