Excel problem: Comparing data between worksheets

m5rcc

I wonder if any one can assist me in what is probably an easy problem.

I have two worksheets, whereby the headers pm each worksheet are the same. the data however may not be. If the entries are different, I have used =IF(ISNA(VLOOKUP(B2,'Master 0202'!B2:B50,1,FALSE)),"New","") to pick up new entries. Fine.

However, those that are not new, i.e. they are both in the old worksheet and the new worksheet, I need to find out whether there is any change in any of the cells. The headers run from columns A to W.

How can I find out if there are any changes in any of the cells in one go? Can I use another VLOOKUP?

Thanks in advance

DevCoder

I can only post quickly, so haven't given it much thought but a bodge way of doing it is to add a column and concatenate all columns for that row , ie

In new column

=A1&B1&C1&D1...

and so on

do the same for the other workbook

Now you have a single lookup which contains the entire string of all cells in each column

so combine that with your intial lookup (so you can see if the record exists) and then it will check if the entire data is the same.

m5rcc

krisdorey wrote: »

I can only post quickly, so haven't given it much thought but a bodge way of doing it is to add a column and concatenate all columns for that row , ie

In new column

=A1&B1&C1&D1...

and so on

do the same for the other workbook

Now you have a single lookup which contains the entire string of all cells in each column

so combine that with your intial lookup (so you can see if the record exists) and then it will check if the entire data is the same.

That's too primitive. I need to find if there any changes in any of the cells in that row.

Jivesinger

If you want to know which colummn has the difference then I don't think it's possible to do it all in one formula unless you concatenate as krisdorey suggests.
(My variation on that is to insert an uncommon character in between so you get something like)
a1&"_"&b1&"_"&c1
Otherwise
22 1 12
looks like
2 21 12
)

Do you have something in column B which identifies the record then?

If so you could try
Column X: =match(B2,'Master 0202'!$B:$B,0)
Column Y: =if(INDEX('Master 0202'!C:C,$X2)=c2,0,1)
And then copy that formula from column Y for as many as you need to drag the data from the other spreadsheet.

Finally add a total to add up columns Y onwards. If the total is more than one, you need to look at that row.

It's not pretty or elegant though...

bengalknights

As others suggest concatenate the lines then use a if formula to find matches those that dont have a match should be new lines