+ Reply to Thread
Results 1 to 3 of 3

Identify and display matching cells in a single column

  1. #1
    Registered User
    Join Date
    03-22-2015
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    42

    Identify and display matching cells in a single column

    Hello

    I have a very large spreadsheet with ~10,000 rows and data in columns A - AK. I need to be able to identify any cells where the text in Column B matches any other cell in Column B and then display the data in Column A for those matches...

    A B Expected Output
    154 OrangeRed 1531, 7898
    2504 RedYellow
    5134 BluePink
    1531 OrangeRed 154, 7898
    8645 GreenBlue
    5484 BluePink
    7898 OrangeRed 154, 1531
    1235 YellowGreen


    Any help would be greatly appreciated!

    Thanks and Regards

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Identify and display matching cells in a single column

    Note that this suggestion assumes that all data in column A is numeric, and that all values in column A will be <> 0.

    Copying your sample into A2:B9.

    In C2 and filled down

    =COUNTIF(B$2:B2,B2)

    In D2 and filled down and right

    =IF(C2="","",IFERROR(1/(1/SUMIFS($A$2:$A$9,$B$2:$B$9,$B2,$C$2:$C$9,COLUMNS($D2:D2))),""))

    Not sure how well that will work with 10k rows of data though, a vba method might be a better approach.

  3. #3
    Registered User
    Join Date
    03-22-2015
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Identify and display matching cells in a single column

    Thankyou!! That will work

+ 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. [SOLVED] Identify cells that contain partially matching text?
    By MetroBOS in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-28-2015, 05:31 PM
  2. Replies: 6
    Last Post: 07-29-2013, 02:11 AM
  3. [SOLVED] Count text in one column Matching single or multiple Criteria from Other Column Excel 2003
    By Jose Macieira in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-17-2013, 06:55 AM
  4. Replies: 1
    Last Post: 04-23-2013, 12:46 AM
  5. Replies: 3
    Last Post: 10-02-2011, 05:06 PM
  6. Identify/Display Protected & Unprotected Cells
    By ShaneBell in forum Excel General
    Replies: 1
    Last Post: 09-14-2009, 09:48 AM
  7. [SOLVED] How do I identify cells with matching numbers within a column.
    By dbmeyer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-26-2006, 06:40 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