Conditional Formatting - Finding Duplicates across Multple Sheets

[iKloke]

I have been searching all over the net to find the right answer. I use conditional formatting all the time on a single sheet but I am still very much so an excel noobie. I am now wanting to have it work across multiple sheets on the same workbook.

I currently have a worksheet that I keep my Inventory up to date in. I need to make sure that the information is only on one sheet at any one time. I want to make sure that if at any time it is two places anywhere in the Workbook that it will highlight it on all sheets so I correct the duplicate.

As an example, the common column is in question is called Serial Numbers. So basically I need something that says if a Serial Number is found on multiple pages in the Serial Number Column it will be highlighted as a duplicate on all the pages.

My Current Sheets list that have the Serial Number Column are: Small Cage, 2020 Changes, 2020 Refresh List, 2020 Completed Refresh or Surplus Cage.

Hopefully that makes sense and I can provide more information if needed.

I truly appreciate your help ahead of time.

Thank You,
Aaron

[FDibbins]

If you cant get CF to work across sheets, add a helper colunm and use something like MATCH to find dup's, then use that column as teh basis for your CF rule

[iKloke]

I have not even started trying to do it. I have found quite a few places where people explained how to do it across separate workbooks so I know it can be done but they were not doing it across the same workbook. I am sure it is all over the internet but in the first two pages of each search I have not found the exact answer. Being a noob I need some sort of how to do the code etc.

Somewhere I came across =AND(A1<>" ",OR(COUNTIF(sheet1,A1)=1,COUNTIF(sheet2,A1)=1)) but I do not know how to use that or how to specify my specific sheets. I am hoping someone knows off the top of their head.

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[iKloke]

Sounds great , here we go. Attached is the document.

[FDibbins]

I see no column called Serial Number?

As an example, the common column is in question is called Serial Numbers.

[iKloke]

Sorry, I forgot how I have them named.

It is the columns with the colors.

New CI / New CI S/N / Old CI Name / Old CI S/N

[FDibbins]

OK so are you looking for duplicates in the same column to be colored, or duplicates in any column to be colored?
eg on Small, I see lt301100 repeated twice in F and twice in K. Would you want to ID them all as duplicates?

[iKloke]

I swear I am not helping myself here by using those numbers. This was an error on my part and I apologize about that. I should have used numbers that looked different because I can see how close they look. In the example in F it is lt301100 but in K it is lt3011010. I should have used very different numbers so it made more sense.

To answer the question though, which is easier? To be honest, I would probably use both at different times so if I can get examples for both that would be wonderful. But the other answer is, if only one is realistic, whichever is easier to do and easier to explain would work best. I would guess if there are duplicates in any of the 4 columns then highlight them would be easiest but you are by far an expert compared to me.

This is more of a making sure there is no way that I missed it being on two sheets so I do not provide false numbers to my boss thing. If it would help, I can answer any questions by phone as well. Just shoot me a PM and I will send you my number or I am happy to do it here as well.

I truly do appreciate your help with this.

[FDibbins]

Sorry for the delay, had a bunch of appointments today.

Here is the approach I took.

1. I made a list of all your sheet names, and gave that list a range name (SheetNames) (This is a once off step, only on 1 of your sheets)
2. I added a helper column to (for now) just Small sheet, you could add the same helper and formulas to the others as well, to ensure you are covered. I used col AP, but you could use any column, just adjust the refs in the CF formula
The helpers can be hidden if needed
AP3=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetNames&"'!F3:F20"),F3))
copied down
This will count all instances of what is in Small F3, across all sheets

Now that you have something to ID, you can then create a CF rule that tests the value in AP for being >1

I will attache your modified WB so you can see what I have done

If you have any more questions, give me a shout

[iKloke]

Ok, I tried that but it does not seem to be working. If I type something on sheet one and then past it onto sheet two etc, it does not highlight them as duplicates. I am sure I am just not understanding what I am doing to do it correctly. When I am pasting it I am making sure to paste where it just puts the numbers and keeps the formatting of the current sheet so that I do not overwrite any formulas.

[FDibbins]

1. I did not add the CF rules to your file, I figured you could manage that for yourself?
2. I just added my SUMPRODUCT to 1 sheet, I should have mentioned it needs to be on all sheets so the CF can reference it - I have now done that, you will probably need to increase the ranges though
3. I have added a CF rule to the 1st sheet col F:G, you will need to finish it of for K:L and for the other sheets.

See how far you get, and again, shout if you need more help