+ Reply to Thread
Results 1 to 7 of 7

Identify duplicates across multiple colums and rows

  1. #1
    Registered User
    Join Date
    12-22-2008
    Location
    Australia
    Posts
    3

    Unhappy Identify duplicates across multiple colums and rows

    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.)
    Attached Files Attached Files
    Last edited by oldchippy; 12-23-2008 at 05:50 AM.

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Hi,

    Welcome to the forum, take a look at this link on comparing lists

    http://spreadsheetpage.com/index.php...al_formatting/
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    I would recommend you take your multicolumn lists and make them into one column for this project, and just use the Conditional Formatting trick to Highlight Duplicates found here:

    http://www.ozgrid.com/Excel/highlight-duplicates.htm
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    12-22-2008
    Location
    Australia
    Posts
    3
    JBeaucaire - if I combine them into one list and identify the duplicates that way, how can I identify which column/folder that invoice is in? At the moment if an invoice number exists in Column (Folder) A and Column (Folder) F, then I know to look in Folders A and F in the filing cabinet to get the two pages. If everything's in one column, is there a way for me to tell which column/folder they are in?

    oldchippy - thanks for that, I will have a look today.

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    Making them into one column should include the column header so you can see where the highlighted cells are sectionwise. So, one column, headers copied down and all. If those headers are missing, add them as you copy them over.

  6. #6
    Registered User
    Join Date
    12-22-2008
    Location
    Australia
    Posts
    3
    Thanks for the help. I did put them all into the one column with headers and =COUNTIF($A$1:$A$100,A1)>1 and although it's not as elegant as I'd hoped it works just fine.

    Cheers.

  7. #7
    Registered User
    Join Date
    02-26-2013
    Location
    Utah
    MS-Off Ver
    Excel 2010
    Posts
    1

    Unhappy Identify duplicates across multiple colums and rows

    I was trying to look for which duplicates in others rows I had verses the first, but these could help anyway.
    I used a lot of functions in a row to get this done right. I will list them from innermost to outermost

    MATCH( value, array, [match_type] )
    This will give you an error for something that doesn't match, between A1 and anything in column B: rows 1 to 50, and a count if it does match.
    MATCH(A1,$B$1:$B$50,0)

    ISERROR( value )
    This will fix the MATCH so anytime there is a duplicate it will return FALSE, if not it will return TRUE.
    ISERROR(MATCH(A1,$B$1:$B$50,0))

    OR( condition1, [condition2], ... )
    This will make the duplicates across all the columns show up. Make sure to change the match columns for each entry.
    OR(ISERROR(MATCH(A1,$B$1:$B$50,0)),ISERROR(MATCH(A1,$C$1:$C$50,0)),...)

    IF( condition, [value_if_true], [value_if_false] )
    Now that you have FALSEs for all duplicates across all the columns, and TRUEs for all non-duplicates do something with it. I like returning the value in row 1. My line will write Duplicate for any duplicates and leave the other columns blank.
    IF(OR(ISERROR(MATCH(A1,$B$1:$B$50,0)),ISERROR(MATCH(A1,$C$1:$C$50,0))),"","Duplicate")

    Just copy that to the top of an empty column and copy the formula all the way down.
    Last edited by JBeaucaire; 02-26-2013 at 02:15 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1