I have a Excel file with two sheets.
Both sheets have names on it - First name and Last Name. I need to compare the sheets to see if there are duplicates in the first sheet and if there is then find the value against the name in Sheet 2 - highlight the name in Sheet 1.
For example:
If Sheet 1 has a duplicate name as Sheet 2 and that name has the value Card in Sheet 2 then need to highlight that name in a particular colour otherwise another colour.
See attachement
I don't think this can be done as pure formulas, but this VBA macro will do it
VLOOKUP.xlsm
Okay, try this in Sheet1!C3:
Formula:
Please Login or Register to view this content.
Confirm with Ctrl+Shift+Enter (this is an array formula, so just using enter will NOT work), if it is entered right curl braces will be at the start ({) and end (}) of the formula
Drag this down to C19
This will give the Value of Sheet2 Column A if there is a match
Next, select the range of A3:C19 on sheet1,
go to Conditional Formatting, New Rule, Use Formula
Formula:Please Login or Register to view this content.
Format,Fill, select a fill color for a match, OK,OK
go to Conditional Formatting, New Rule, Use Formula
Formula:Please Login or Register to view this content.
Format,Fill, select a fill color for a non-match, OK,OK
Note - the name matches must be exact, if there are leading or trailin spaces in one name that is not in the "matching name", the result will be negative on the match
See Attached
Hope this helps
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
Another option is to add a duplication check sheet. On Sheet 3 it will concenate each name from sheet1 and sheet2 (in case of any same name situations), then compare them to see if there is a match. If so it will check to see if "Card" is also listed with the duplicate name (From Sheet 1) and then puts "Duplicate" in the proper cell. Where you see "Duplicate" will indicate that the same name in the row from sheet1 is the one with the duplicate listed on sheet 2.
VLOOKUP(w Dupesheet).xlsx
Janos S. Vamos
Data Systems Technician/Fire Controlman PO1(SW/AW)
US Navy Retired
Remember, Record Macro can be your friend for figuring out solutions.
Good or Bad, let me know how I did by clicking on the "Add Reputation" * just to the lower left of here. it only takes a few seconds to let someone know.
Thank you very much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks