I have a spreadsheet with 20+ columns. Each column contains a list of invoice numbers, eg, 1234 . The lists are all of different lengths (see below).
I want to find a way to find/highlight/identify those invoice numbers that appear more than once in the entire sheet (eg if invoice 1234 is listed in column B, and also in column H, and/or if invoice 5678 is listed in column A, and also in column J, and also perhaps in column D, etc.)
I've searched this forum but haven't found anything about searching for multiple duplicates in multiple columns. Ideally the matches would be highlighted in different colours, (eg all cells that contain 1234 would be red, those that contain 5678 would be blue, etc., for ease of identification) but I'm not sure if that's possible.
I've attached an example that I've shaded myself to show what I mean. I've tried the MODE function but that only tells me which invoice number occurs the most.
Is anyone able to help me?
(Basically each column represents a folder in a filing cabinet. The invoices mentioned above come in two parts and some weren't stapled and are mixed up. This spreadsheet is to automatically show what invoices are contained in more than one folder in the filing cabinet. If any invoice numbers appear twice in different columns (folders in the cabinet) then it means there are two unstapled pages and I know where to look to find the two parts and staple them together.)
Bookmarks