CODEWITHSUNDEEP
excelexcel-2007excel-formula
rovy perlas
rovy perlas

Reputation: 11

How to compare 2 cells to another 2 cells in a different sheet in excel?

I want to know how to compare or know the difference of 2 cells to another 2 cells in a different sheet in excel 2007

Example:

Sheet 1

   A     B
1 101   KIWI
2 102   APPLE
3 103   BANANA

Sheet 2

   A     B
1 101   KIWI
2 102   APPLE
3 103   ORANGE

I want to show the there is a difference in 3rd row on Sheet 1 and 2

Thanks

Upvotes: 1

Views: 350

Answers (1)

pnuts
pnuts

Reputation: 59440

If you put =A1&B1 in C1 of each sheet and copy down then it is like comparing single columns. So with, in D1:

in Sheet1: =MATCH(C1,Sheet2!C:C,0) in Sheet2: =MATCH(C1,Sheet1!C:C,0)

copied down in each case the resulting numbers should show the row number where the match is on the 'other sheet' (the lists need not be sorted) and otherwise #N/A for where matches have not been found.

There is obviously no need for the concatenation for the example provided, since the two 'A' columns are identical, but I take it that was because the example was simplified.

SO18394321 example

Upvotes: 1

Related Questions