Find Common Numbers Between 2 Columns

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Hi all,

    I hope somebody can help me with this - I have cracked my head over it and cant seem to figure out a way.

    I need to find out common numbers between columns. Each column has multiple number entries seperated by a comma.

    For example:
    Column A: "5592,12222" and Column B: "1,2,3,4,5592,123123" and Column C: "3, 4"

    I need to find out any numbers that are found in more than 1 column. Hence, the result should be something like "3,4,5592". If no match is found then maybe can indicate by coloring the cell.

    My data is over 50,000 rows and 30 columns. I need to do this for each row one by one.

    Please help.


  • Re: Find Common Numbers Between 2 Columns

    hey thanks for ur reply ...[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Guess my message din go thru

    I need to go row by row thru each column and find out numbers which occur more than once in the row. Each column has multiple numbers seperated by a comma ... for example "5592,12222,2,3,4,5592,123123"

    I need to list out which ever numbers occur in more than 1 column for the same row.

  • Re: Find Common Numbers Between 2 Columns

    Wow, who did you upset to get this job? ;)

    There will be a VBA solution, and I'm sure there'll be other threads on here to give you some ideas, BUT see attached for a not-very-elegant solution which might suit if this is a one-off or infrequent task.

    Use Data > Text to Columns to split up the data.
    Do a COUNTIF per each separated item to check for duplicates.
    Concatenate all the resulting values.

    Feels like there's a better way, but can't think of it right now.


  • Re: Find Common Numbers Between 2 Columns


    There must be a better way... hmmm too... and also - how do you choose those colours?!


    Actually, more seriously, this might not work with 30 columns each splitting out into several columns with text to columns.

  • Re: Find Common Numbers Between 2 Columns

    Would you be able to attach a small section of the spread sheet?

    And just so we get the scope clear again. You have 50,000 Rows by 30 Columns, and each cell in that area contains a list of comma separated numbers?

    Also, your question is making me think of something I did in the past, Locating the duplicates is often never the end result. What will you need to do with these numbers once they are found? Maybe your final result can be reach with out having to show you the duplicates.


  • Re: Find Common Numbers Between 2 Columns


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!