Find Common Numbers Between 2 Columns

tachyon · Aug 26th 2008

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.

thanks

Dave Hawley · Aug 26th 2008

Re: Finding Common Numbers In Adjacent Cells

=if(a1=b1,true)

tachyon · Aug 26th 2008

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.

Yard · Aug 26th 2008

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.

Hmmmmmm....

Cheeky Charlie · Aug 26th 2008

Re: Find Common Numbers Between 2 Columns

Urk.

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.

hatman555 · Aug 26th 2008

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.

Dave

jindon · Aug 26th 2008

Re: Find Common Numbers Between 2 Columns

try

Code

Sub test()
Dim a, b(), i as long, ii as long, e
With Range("a1").CurrentRegion
    Z = .Columns.Count
    a = .Value
    ReDim b(1 To UBound(a, 1), 1 To 1)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a, 1)
            For ii = 1 To UBound(a, 2)
                For Each e In Split(a(i, ii), ",")
                    If Not .exists(e) Then
                        .Add e, Nothing
                    Else
                        b(i, 1) = b(i, 1) & IIf(b(i, 1) = "", "", ",") & e
                    End If
                Next
            Next
            .RemoveAll
        Next
    End With
    With .Offset(, .Columns.Count + 2).Resize(, 1)
        .Value = b
        On Error Resume Next
        .SpecialCells(4).Interior.Color = vbRed
    End With
End With
End Sub

Display More

Find Common Numbers Between 2 Columns

Participate now!

Share