Comparing 4 columns and displaying duplicate entries

[Tonx]

Hi everyone ,

Here's my problem i have an excel sheet with appoxiamtely 13,000 entries and i need to compare 2 columns versus two other columns within the same excel sheet by looking for the same entry.

So i want to have something that will look at the name in Column H and then look for that name in Column C and then copy that column( C) along with its value in Column E to the newly created Columns K and L, while including column H and I's values, respectively.... duplicates are fine. i don't want to remove that.

What i am looking for: i am looking for either macro (preferably) or a formula that will accomplish this task.

Here is an example of what it looks like for better understanding

* Column H and I are the orginal two columns and Column C and E are the columns we are looking at to find duplicate names from Column H

* Column K and L would be where i want the new data to be shown

This is what i have:

[TABLE="class: outer_border, width: 700"]
<tbody>[TR]
[TD]Column C[/TD]
[TD]Column E[/TD]
[TD]Column H[/TD]
[TD]Column I[/TD]
[TD]Column K[/TD]
[TD]Column L[/TD]
[/TR]
[TR]
[TD]Peter Parker[/TD]
[TD]154.90[/TD]
[TD]Henry Rodney[/TD]
[TD]214.67[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Peter Parker
[/TD]
[TD]234.02[/TD]
[TD]Nate Robinson[/TD]
[TD]456.56[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nate Robinson[/TD]
[TD]124.34[/TD]
[TD]Dale Williams[/TD]
[TD]345.36[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]312.42[/TD]
[TD]Roberto Stockton[/TD]
[TD]789.45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Janet Anderson[/TD]
[TD]524.14[/TD]
[TD]John Smith[/TD]
[TD]568.48[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dale Williams[/TD]
[TD]344.42[/TD]
[TD]Peter Parker[/TD]
[TD]154.90[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dale Williams[/TD]
[TD]344.42[/TD]
[TD]Janet Anderson[/TD]
[TD]782.64[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Roberto Stockton[/TD]
[TD]258.24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Henry Rodney[/TD]
[TD]214.67[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Henry Rodney[/TD]
[TD]214.67[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Henry Rodney[/TD]
[TD]412.44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



This is what i want it to look like:

[TABLE="class: outer_border, width: 700"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Column C[/TD]
[TD]Column E[/TD]
[TD][/TD]
[TD]Column H[/TD]
[TD]Column I[/TD]
[TD] [/TD]
[TD]Column K[/TD]
[TD]Column L[/TD]
[/TR]
[TR]
[TD]Peter Parker[/TD]
[TD]154.9[/TD]
[TD] [/TD]
[TD]Henry Rodney[/TD]
[TD]214.67[/TD]
[TD] [/TD]
[TD]Henry Rodney[/TD]
[TD]214.67[/TD]
[/TR]
[TR]
[TD]Peter Parker [/TD]
[TD]234.02[/TD]
[TD] [/TD]
[TD]Nate Robinson[/TD]
[TD]456.56[/TD]
[TD] [/TD]
[TD]Henry Rodney[/TD]
[TD]214.67[/TD]
[/TR]
[TR]
[TD]Nate Robinson[/TD]
[TD]124.34[/TD]
[TD] [/TD]
[TD]Dale Williams[/TD]
[TD]345.36[/TD]
[TD] [/TD]
[TD]Henry Rodney[/TD]
[TD]214.67[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]312.42[/TD]
[TD] [/TD]
[TD]Roberto Stockton[/TD]
[TD]789.45[/TD]
[TD] [/TD]
[TD]Henry Rodney[/TD]
[TD]412.44[/TD]
[/TR]
[TR]
[TD]Janet Anderson[/TD]
[TD]524.14[/TD]
[TD] [/TD]
[TD]John Smith[/TD]
[TD]568.48[/TD]
[TD] [/TD]
[TD]Nate Robinson[/TD]
[TD]456.56[/TD]
[/TR]
[TR]
[TD]Dale Williams[/TD]
[TD]344.42[/TD]
[TD] [/TD]
[TD]Peter Parker[/TD]
[TD]154.9[/TD]
[TD] [/TD]
[TD]Nate Robinson[/TD]
[TD]124.34[/TD]
[/TR]
[TR]
[TD]Dale Williams[/TD]
[TD]344.42[/TD]
[TD] [/TD]
[TD]Janet Anderson[/TD]
[TD]782.64[/TD]
[TD] [/TD]
[TD]Dale Williams[/TD]
[TD]345.36[/TD]
[/TR]
[TR]
[TD]Roberto Stockton[/TD]
[TD]258.24[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Dale Williams[/TD]
[TD]344.42[/TD]
[/TR]
[TR]
[TD]Henry Rodney[/TD]
[TD]214.67[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Dale Williams[/TD]
[TD]344.42[/TD]
[/TR]
[TR]
[TD]Henry Rodney[/TD]
[TD]214.67[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Roberto Stockton[/TD]
[TD]789.45[/TD]
[/TR]
[TR]
[TD]Henry Rodney[/TD]
[TD]412.44[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Roberto Stockton[/TD]
[TD]258.24[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]John Smith[/TD]
[TD]568.48[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]John Smith[/TD]
[TD]312.42[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Peter Parker[/TD]
[TD]154.9[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Peter Parker[/TD]
[TD]154.9[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Peter Parker [/TD]
[TD]234.02[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Janet Anderson[/TD]
[TD]782.64[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Janet Anderson[/TD]
[TD]524.14[/TD]
[/TR]
</tbody>[/TABLE]



Thank you, so much in advance

 

[Tonx]

I think this will do what you want:

Sub FindEm()
Dim X, Z, Fnd As Long
Dim DataArray(50000, 3) As Variant
Dim ONRow, DoingRow As Long

For X = 1 To 60000
    If Cells(X, 3).Value <> Empty Then
        Fnd = Fnd + 1
        DataArray(Fnd, 1) = Cells(X, 3).Value
        DataArray(Fnd, 2) = Cells(X, 5).Value
    End If
Next

X = 1
Do While True
    If Cells(X, 8).Value = Empty Then Exit Do
    Let ONRow = ONRow + 1
    Let DoingRow = ONRow
    Cells(ONRow, 11).Value = Cells(X, 8).Value
    Cells(ONRow, 13).Value = Cells(X, 9).Value
    For Z = 1 To Fnd
        If DataArray(Z, 1) = Cells(DoingRow, 11).Value Then
            ONRow = ONRow + 1
            Cells(ONRow, 11).Value = DataArray(Z, 1)
            Cells(ONRow, 13).Value = DataArray(Z, 2)
        End If
    Next
X = X + 1
Loop

End Sub

This is perfect!!! Thank you so much for your time. you're a lifesaver

 

[MikeDBMan]

Glad to be of help. It was hard for your to explain so I could understand your mission but once we got past the communication issue, it only took about 6-8 minutes to generate the code for it.