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

 

[MikeDBMan]

Why wouldn't the second row have Nate Robinson in column K. You said you wanted to match what is in column H and put it in column K. But H & K aren't matching. And what does the phrase "while including column H and I's values, respectively...." mean? Not following your request at all.

 

[Tonx]

Thanks for reply MikeDBMan,

I'm sorry, if i wasn't clear enough...pretty much i want to compare the names in Column H to Column C and then copy the names and the values( Column E over to Column k and l.

As for the phase "while including column H and I's values, respectively", i wantED to make sure that in the case of Nate Robinson... Column K and L will have two entries for him ( an entry from Column C and E and also from Column H and I)

Hope that makes sense

 

[MikeDBMan]

None of it makes sense to me. Maybe someone else can help you. I can't understand what you want to do.

 

[Tonx]

None of it makes sense to me. Maybe someone else can help you. I can't understand what you want to do.

Im sorry...i will reword my problem

 

[Tonx]

Why wouldn't the second row have Nate Robinson in column K. You said you wanted to match what is in column H and put it in column K. But H & K aren't matching. And what does the phrase "while including column H and I's values, respectively...." mean? Not following your request at all.

Another explanation

Here’s my problem

I have an excel document with over 13,000 entries filled with names and number values. What I want to do is to check for duplicates in a particular column and place them somewhere else in the excel sheet. If you look below I have columns c, e, h, and i.

Column H is my original list of names and column I is their value. I want to be able to let’s say take Henry Rodney from column H and search the entire column C to see if his name appears there. Now if it appears there I want to take his name along with its values which is column E and place it into two new columns (k and l)

At the end of the process I want henry Rodney entries from column c to copy over to column k and its values to L

[TABLE="class: cms_table_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]

 

[Tonx]

At the end of the process I want henry Rodney entries from column c to copy over to column k and its values to L. As shown below

[TABLE="class: cms_table_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]Henry Rodney[/TD]
[TD]214.67[/TD]
[/TR]
[TR]
[TD]Peter Parker[/TD]
[TD]234.02[/TD]
[TD]Nate Robinson[/TD]
[TD]456.56[/TD]
[TD]Henry Rodney[/TD]
[TD]214.67[/TD]
[/TR]
[TR]
[TD]Nate Robinson[/TD]
[TD]124.34[/TD]
[TD]Dale Williams[/TD]
[TD]345.36[/TD]
[TD]Henry Rodney[/TD]
[TD]412.44[/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]

 

[MikeDBMan]

If it is okay to do one at a time why not set up an advanced filter. On the Data Ribbon, Select the advanced filter. The dialog box has several choices:
Before starting type "Name" in P2
Before starting type "Dale Williams" in P3 (no Double-quote marks).

Select the advanced filter button:
Select the Radio Button "Copy to another location"
Select list range of $c:$E
Select Criteria Range as $P$2:$P$3
Select Output Range as $K:$K
Then click okay. Then your results are in K (names) and Amounts in M.
Select another name like "Nate Robinson" in P3 and click the advanced filter button again. Be sure to re-select the radio button to copy to another location.

Does this do all you need or do you want the entire list at once. If so I will give you the macro for that (in the morning). Later.

 

[Tonx]

If it is okay to do one at a time why not set up an advanced filter. On the Data Ribbon, Select the advanced filter. The dialog box has several choices:
Before starting type "Name" in P2
Before starting type "Dale Williams" in P3 (no Double-quote marks).

Select the advanced filter button:
Select the Radio Button "Copy to another location"
Select list range of $c:$E
Select Criteria Range as $P$2:$P$3
Select Output Range as $K:$K
Then click okay. Then your results are in K (names) and Amounts in M.
Select another name like "Nate Robinson" in P3 and click the advanced filter button again. Be sure to re-select the radio button to copy to another location.

Does this do all you need or do you want the entire list at once. If so I will give you the macro for that (in the morning). Later.

I thought about the advance filter, but it would take way too long


That would be great. I want to be able to do the entire list at once.... thank you so much

 

[MikeDBMan]

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