You are here:

# Excel/Matching Identical cells

Question
QUESTION: Hi Tom,

I'm trying to make a tool for work and I'm stuck, I was wondering if I could pick your brain.

We have three different files that exist that each contain a client roster from different sources.

I made a new workbook that displays the data from all three of those other files at once. There's one sheet per original source file. (IE: Sheet 1 displays data that is in file 1, etc).

That part was easy, but I'm stuck on the next part. Sometimes, we have duplicate referrals from different sources. Column A of the sheet is "Last name, First name" of the patients. I'm trying to set it up to check Sheet 1, Column A against Sheet 2/Column A, and Sheet 3/Column A and mark any exact duplicates with a different color. I was thinking possibly conditional formatting might work, but I'm not sure what formula to use for that.

Assuming you have Excel 2007 or later, you can use the new countifs formula that can check multiple conditions

=if((countifs(Sheet2!A:A,A2,Sheet2!B:B,B2)+Countifs(Sheet3!A:A,A2,Sheet3!B:B,B2))>0,"Dup","")

Put that in say C2 of your sheet1 and drag fill down the column.  You could then use conditional formatting to color any cell or row that has "Dup" in column C, but you could also just put a filter on column C and show only the duplicate rows (or just the non-duplicate rows).

You could employ as similar formula/approach on the other two sheets.

Hope that gives you something to work with.
--
Regards,
Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Hi Tom, that's helpful, thank you. I think I'm running into problems becuase the formula that I'm using to draw the codes in from the other sheets. For, example, My "A2" cell already contains the following formula:
=('V:\Children''s Outpatient\Staff Folder\[Copy of NAC HNCM Youth91115.xlsx]Sheet1'!F2)

I need to know to modify that into the CountIF line that you sent me.

I appreciate the help.
-Johnathan

Johnathan,

I didn't suggest that your put the formula in A2.  Recall:

>Put that in say C2 of your sheet1 and drag fill down the column

so you would put in the formula to discover duplicates in another currently blank column.  You will need the formula (in column A) that pull in the data to do just that: "pull in the data". so you don't want to replace that.  You want to check against the results of the formula; the data in column A.

Hopefully that is clearer.

(but just to further explain, my formula assume you are using the workbook that has data from all three separate workbooks in that single workbook in sheet1, sheet2, and sheet3.  Then my formula counts the data in the single workbook. )

--
Regards,
Tom Ogilvy

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Tom Ogilvy

##### Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

##### Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.