Excel/Matching Identical cells
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
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.
---------- 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.
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. )