Conditional Formatting Between Two Worksheets

[liiv17]

I have 2 spreadsheets of names (~2500 and ~1800) and a bunch of corresponding data continuing down the row. both are structured this same way:

Example:
Row1: LAST, FIRST, data1, data2, data3, etc...

How can I construct a formula so it will "check" the larger sheet for duplicate names (a row containing exactly the same FIRST and LAST names), & then change the cells' fill colors in the smaller sheet from white to red for all rows meeting this condition


One other piece of information which might be important:
For these rows containing duplicate first & last names, the entire row is not a duplicate entry; only the names will match (columns A & B)... While the other columns down the row will have different values between sheet1 & sheet2. Not sure if this changes anything.... Thanks

[Ger Plante]

Hi Liiv - this is possible to do alright with Coniditonal Formatting I think... When you say an Exact match... do you really mean EXACT... for example

does "John", "Smith" = "JOHN", "SMITH"

Also - I presume these are two sheets in one workbook, right?

Ger

[liiv17]

do you really mean EXACT... for example
does "John", "Smith" = "JOHN", "SMITH"

well, all names happen to already be formatted as "John" "Smith" (there is no variation in capitalization). So, let's operate under whichever assumption allows for the easiest formula

these are two sheets in one workbook

they weren't, but I can very easily just cut & paste them as sheets in the same workbook.... so, yes-- now they are two in a single workbook

[Ger Plante]

Hmmm.... me an my big mouth

Not so easy when I sat down to do this with Conditional Formatting.... sorry three more questions...

1) by any chance are the columns sorted by Last Name / First Name? If not can they be...? (it would make it a lot easier if they were sorted).
2) Do you absolutely require a Formaula for this? Would VBA do?
3) Is it possible (are you allowed) to add an additional column to one of the sheets to help us along?

Thanks Liiv.

[ByTheCringe2]

Can you attach your workbook, or a part of it, showing the layout and the cells to be formatted please. I will use formulas.

[Ger Plante]

I'm sure the Gringe will nail this, but in the mean time, attached is a sample...

On Sheet 1, I have used a helper Column where I just combined last name and first name.

One Sheet 2, I have used conditional formatting. The condition on the formatting uses a vlookup of a combination of LastName and FirstName to lookup the helper column in Sheet 1. Not sure if this is helpful.

Ger

[liiv17]

attached a sample work book trimmed down so both sheets only contain entries where last names begin with the letter J or K.

both are sorted alphabetically by LAST then FIRST.

My hope is for a formula in sheet1 to change the fill color so it stands out, IF the full-name in that entry is duplicated in sheet2.

In my actual workbook, the data would extend over to column AF. It doesn't matter if the formula fills the entire row w/ a new color or just the first and last name.. I really just want to be able to quickly identify the duplicate entries

thanks for the help

[liiv17]

On Sheet 1, I have used a helper Column where I just combined last name and first name.

One Sheet 2, I have used conditional formatting. The condition on the formatting uses a vlookup of a combination of LastName and FirstName to lookup the helper column in Sheet 1. Not sure if this is helpful.

it's definitely helpful, but i just dont know the excel language (vlookup for instance) to say as a formula:
if sheet1 "First&Last" equals sheet2 "First&Last," then fill the sheet1 cell red for that row

in fact-- you actually just taught me how to combine data in two cells (=A1&B1)... yeah im pretty much brand new

[Ger Plante]

Find attached a spreadsheet as requested.

Now for the explanation....

Firstly, go to Sheet 2 and enter the this formula in Cell E3:
=A3&B3
This will concatenate the first name and last name.

Selecting Sell E3, drag the formula down to the last row to fill all the rows. This should now have concatenated all rows with First Name and Last name.

Highlight the Entire Column E with the mouse (it should go blue). When the Column is Selected enter "MY_NAMES" in the top left box (not the formula bar). "MY_NAMES" now refers to the entire column E and we will use this name shortly.

On the first sheet, select cell A3 and click and drag to select all the cells down to D159.

When all the cells are highlighted, go to Menu "Format" - "Conditional Formatting"... select "Condition1" as "Formula Is".

In the box that appears copy paste this formula into it.
=ISERROR(VLOOKUP($A3&$B3,MY_NAMES,1,FALSE))

Set the formatting background to red.

This is all done in the attached worksheet. I had to delete some of the rows to keep the size of the attachment down....

You should be able to make this work for a larger sample as well. Post back if your confused.

Ger

[ByTheCringe2]

I'll find out how to do this tomorrow - it's been posted here in the last week, but I can't do it tonight.