Can anyone help with an Excel formula plz??

rokerij

rokerij

rokerij

Associate
Joined
21 Jul 2007
Posts
55
Hi guys, I have done an Excel course but it was some time ago & I have forgotten most of it:rolleyes:

What I have is 4 columns of data (letters/numbers/letters/mixed) on one spreadsheet and 4 supposedly identical columns on another sheet (can be upto 600 lines)

I need to compare 4 against the other 4 and highlight any cells that are different, I can put all 8 columns on one sheet if that helps.

Thanks in adv for any help, I have tried searching the answer but im gettin bogged down & confused like - do i need a formula for this or macro or vlookup etc???
 
Use conditonal formatting, it's under the format menu from memory, it will higlhight in the colour of your choice if the conditions do/do not meet your criteria
 
Conditional formatting will do it but you haved to manually scroll down to find the highlighted cells.
I would put the columns side by side on one sheet (not essential but it makes it easier). So the first set are in A:D and the second set in E:H.
In Column I compare A&E with a formula in I1 of: =A1=E1 This will show True if they match, False if they don't.
Copy this across to J, K, L .
In column M use the formula: =AND(I1,J1,K1,L1)
That will give a result of True if all 4 columns match.
Highlight I1:M1 and fill down.

Filter your data to find any False values in column M. These are the ones that do not match.

If your two data sets get out of sync then you will need to insert a blank row in one of the data sets. Then copy the formulae down again.
 
Thanks guys, I have not tried to use conditional formatting but i'll have a look, highlighting them sounds good.


I have just tried your formulas Namsni on a small file and they are working so thanks for your help:)
 
No probs.
Same approach can of course be used in Conditional format. Combined one is:
And(A1=E1,B1=F1,C1=G1,D1=H1) which will return True if all 4 columns match. Be careful to remove the $ signs if wou are copying the conditional format to other rows.
 
You must log in or register to reply here.
Back
Top Bottom