+ Reply to Thread
Results 1 to 5 of 5

Comparing one column with multiple columns (1000 columns) to find identical items

  1. #1
    Registered User
    Join Date
    06-18-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    76

    Comparing one column with multiple columns (1000 columns) to find identical items

    Dear all,

    I have about 1000 columns with different length in the excel file; please kindly find the attached short example. I would like to compare the first column with the rest of columns and find identical items between the first column and column 2, between the first column and column 3, and so on; finally, print the identical items at the end of each column and the last row in the excel file as I show in the example file in red color.

    Please kindly note that, in this file, the header name of columns, except for the first column is identical with the contents within the first column; this identical name between each column and its header name should be ignored when searching for identical items. For example, in the attached example, there are 7 columns; I would like to compare the column 1 with each of 6 columns. As you see, the header name of these 6 columns (A, B, C, D, E, F) is identical with the names in the column 1. So when, column 1 compare with column 2, A should be ignored (because it is identical to the header name of column 2) and B and F should be considered as the identical item between column 1 and column 2, and printed (as B,F) , and so on for other columns.

    Your help would be highly appreciated in advance.

    All the best
    Attached Files Attached Files
    Last edited by Mary22; 04-23-2019 at 02:51 PM.

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Comparing one column with multiple columns (1000 columns) to find identical items

    Hi

    an attempt from an Excel 2010 user


    I'm using a UDF I've found on Google TEXTJOIN, as TEXTJOIN is not available on my Excel


    Please Login or Register  to view this content.
    In B8 to be confirmed with control+shift+enter

    =SUBSTITUTE(TEXTJOIN(",",1,IF(COUNTIF($A2:$A7,B2:B7)=0,1/0,B2:B7)),B1&",","")

    I'm sure you'll get better solutions.

    Regards
    Attached Files Attached Files
    Last edited by canapone; 04-24-2019 at 01:23 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Comparing one column with multiple columns (1000 columns) to find identical items

    Hi again

    a "brute force attack" formula to intercept first 4/5 matches: in B8 to be copied to the right.

    Please Login or Register  to view this content.

    Regards

  4. #4
    Registered User
    Join Date
    06-18-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    76

    Re: Comparing one column with multiple columns (1000 columns) to find identical items

    Hi Canapone,

    Many thanks for your solution. I used your second formula, It worked well.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Comparing one column with multiple columns (1000 columns) to find identical items

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Comparing two columns & showing identical values
    By Sham Sundar in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-18-2016, 07:46 AM
  2. Replies: 5
    Last Post: 05-14-2014, 08:35 AM
  3. Replies: 3
    Last Post: 12-19-2013, 10:00 AM
  4. Comparing 2 WB's with identical rows and columns, then Hightlight the differences
    By crispybadger in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2013, 05:17 PM
  5. Replies: 0
    Last Post: 09-25-2012, 03:42 PM
  6. Replies: 4
    Last Post: 03-28-2010, 10:58 AM
  7. Comparing two columns of data that are near identical
    By Shaunclippo in forum Excel General
    Replies: 1
    Last Post: 01-21-2008, 02:16 PM

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