Match Rows

[jeffmb]

How can I determine if two rows are an exact match?

 

[danzon]

highlight the first row,
hold CTRL
highlight the second row

hit F5

click special

select ROW DIFFERENCES

excel has now highlighted any non matches.... select a red cell background color so that all highlighted cells are turned red for easy finding.

 

[jeffmb]

Danzon

Thanks for the quick reply.

I have 2 worksheets with several hundred lines each. I am comparing each sheet line by line and just need to know if there are any differences, not necessarily what those differences may be. I was hoping for something in a third sheet like "=if(SheetA!A1:Z1 = SheetB!A1:Z1,0,1)" for each row.

 

[danzon]

are they all numbers ??? if so you can use,

{=IF(MAX(Sheet1!A1:Z1-Sheet2!A1:Z1)=0,MIN(Sheet1!A1:Z1-Sheet2!A1:Z1))}

Array formula > confirm with CTRL SHIFT ENTER


any returned value other than 0 means they do not match up perfectly

 

[jeffmb]

The fields have some numbers but are mostly text.

 

[danzon]

how about this...


{=IF(SUM(--(Sheet1!A1:Z1=Sheet2!A1:Z1)) < COUNTA(Sheet1!A1:Z1)+COUNTBLANK(Sheet1!A1:Z1),"PROBLEM","OK")}
<?XML:NAMESPACE PREFIX = COUNTA(Sheet1!A1 /><COUNTA(Sheet1!A1:Z1)+COUNTBLANK(Sheet1!A1:Z1),"PROBLEM","OK")}< p>
again an ARRAY confirmed with CTRL > SHIFT > ENTER


let me know
</COUNTA(Sheet1!A1:Z1)+COUNTBLANK(Sheet1!A1:Z1),"PROBLEM","OK")}<>

 

[jeffmb]

Thanks. That did the trick.