Dear Fellows
Need your help, I have two sheets that need to be compared to find the differentiate and highlight it with conditional formating
in sheet 1 there are some unit and booking, and it should be compared with sheet 2
Dear Fellows
Need your help, I have two sheets that need to be compared to find the differentiate and highlight it with conditional formating
in sheet 1 there are some unit and booking, and it should be compared with sheet 2
Last edited by azbi; 05-08-2018 at 03:40 AM.
Formula for CF of Column A Sheet1
=ISERROR(MATCH($A2,Sheet2!$A$2:$A$400))
Formula for CF of Column C Sheet1
=ISERROR(MATCH($C2,Sheet2!$B$2:$B$400))
Formula for CF of Column E Sheet1
=ISERROR(MATCH($E2,Sheet2!$C$2:$C$400))
Choose different colours for Fill using Format.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Select A2:E266 in sheet1
Then in conditional formatting rule paste below formula and choose conditional formatting as you wish.
Formula:Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Dear kvsrinivasamurthy
not match,
Dear shukla.ankur281190
Almost perfect, but highlight the correct data, how about the uncorrect data only
and hot about if I select A:E in sheet1, whats the amendment of the formula for this?
Just add NOT function in above formula.
Formula:Please Login or Register to view this content.
shukla.ankur281190
I've Select A2:E266 in sheet1 but nothing happened for this formula
=NOT(MATCH($A2&$E2,Sheet2!$A$1:$A$500&Sheet2!$C$1:$C$500,0))
how about, if data more than 266 row? maybe 1000 or more?
Oh! My bad.
Try
Formula:Please Login or Register to view this content.
How far your data is need to select no matter it is 10 or 1000, it determine that conditional formatting need to implement on selection only.
Dear shukla.ankur281190
perfect, but how about if data in sheet 2 more than 2000 data?
any change for the formula?
Then the formula range would be increase.
Formula:Please Login or Register to view this content.
In above highlighted part of formula is, have taken 500 now you can change according your second sheet data. You can implement as large as you wish may be 2500 or 25000
Dear shukla.ankur281190
thanks a lot
Thank you for feedback and added rep !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks