array compare

[newbie777]

Hi guys

I have a question/task if you can help me with please + explanation I will really appreciate it.

I have two columns in two sheets (4 columns). Basically what i want to do is to compare between the concatenation of the two columns in each sheets.

So compare A2&B2 in sheet 1 with A2&B2 in sheet 2.

Whatever in sheet1 and not in sheet two, we copy A2 in sheet3 and B2 in sheet 3 and in the cell next to it add
Whatever in sheet2 and not in sheet one, we copy A2 in sheet3 and B2 in sheet3 and in the cell next to it drop.

All under each other.

In the below link a live example so it would be more clear.

Many thanks in advance.
https://www.dropbox.com/s/iqrpjj54ou...rops.xlsx?dl=0


Hassan

 

[Andrew Fergus]

Hello

You don't have to use a 3rd sheet to do this or a macro. If the idea is to identify and keep only the duplicates, then we can add a helper column to each sheet and then remove it once you are finished cleaning up the data.

I'm assuming there is no data in column C (I have not looked at the drop box file) - if there is data in column C then enter the formulas below to a different column.

In Sheet 1, cell C2 enter the following array formula:
{=IF(ISERROR(MATCH(A2&B2,Sheet2!A:A&Sheet2!B:B,0)),"add", "ok")}
and copy down.
Enter the formula without the curly brackets {} and before you press enter, instead press Ctrl+Shift+Enter and the curly brackets should automatically appear around the formula. This converts the formula to an array formula.

And anter the following array formula in sheet 2, cell C2:
{=IF(ISERROR(MATCH(A2&B2,Sheet1!A:A&Sheet1!B:B,0)),"drop", "ok")}
again, copy this formula down. This has to be entered as an array formula like the one above using Ctrl+Shift+Enter.