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:

Column C	Column E	Column H	Column I	Column K	Column L
Peter Parker	154.90	Henry Rodney	214.67
Peter Parker	234.02	Nate Robinson	456.56
Nate Robinson	124.34	Dale Williams	345.36
John Smith	312.42	Roberto Stockton	789.45
Janet Anderson	524.14	John Smith	568.48
Dale Williams	344.42	Peter Parker	154.90
Dale Williams	344.42	Janet Anderson	782.64
Roberto Stockton	258.24
Henry Rodney	214.67
Henry Rodney	214.67
Henry Rodney	412.44

<tbody>
</tbody>

This is what i want it to look like:

Column C	Column E		Column H	Column I		Column K	Column L
Peter Parker	154.9		Henry Rodney	214.67		Henry Rodney	214.67
Peter Parker	234.02		Nate Robinson	456.56		Henry Rodney	214.67
Nate Robinson	124.34		Dale Williams	345.36		Henry Rodney	214.67
John Smith	312.42		Roberto Stockton	789.45		Henry Rodney	412.44
Janet Anderson	524.14		John Smith	568.48		Nate Robinson	456.56
Dale Williams	344.42		Peter Parker	154.9		Nate Robinson	124.34
Dale Williams	344.42		Janet Anderson	782.64		Dale Williams	345.36
Roberto Stockton	258.24					Dale Williams	344.42
Henry Rodney	214.67					Dale Williams	344.42
Henry Rodney	214.67					Roberto Stockton	789.45
Henry Rodney	412.44					Roberto Stockton	258.24
						John Smith	568.48
						John Smith	312.42
						Peter Parker	154.9
						Peter Parker	154.9
						Peter Parker	234.02
						Janet Anderson	782.64
						Janet Anderson	524.14

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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

Column C	Column E	Column H	Column I	Column K	Column L
Peter Parker	154.90	Henry Rodney	214.67
Peter Parker	234.02	Nate Robinson	456.56
Nate Robinson	124.34	Dale Williams	345.36
John Smith	312.42	Roberto Stockton	789.45
Janet Anderson	524.14	John Smith	568.48
Dale Williams	344.42	Peter Parker	154.90
Dale Williams	344.42	Janet Anderson	782.64
Roberto Stockton	258.24
Henry Rodney	214.67
Henry Rodney	214.67
Henry Rodney	412.44

<tbody>
</tbody>

 

[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

Column C	Column E	Column H	Column I	Column K	Column L
Peter Parker	154.90	Henry Rodney	214.67	Henry Rodney	214.67
Peter Parker	234.02	Nate Robinson	456.56	Henry Rodney	214.67
Nate Robinson	124.34	Dale Williams	345.36	Henry Rodney	412.44
John Smith	312.42	Roberto Stockton	789.45
Janet Anderson	524.14	John Smith	568.48
Dale Williams	344.42	Peter Parker	154.90
Dale Williams	344.42	Janet Anderson	782.64
Roberto Stockton	258.24
Henry Rodney	214.67
Henry Rodney	214.67
Henry Rodney	412.44

<tbody>
</tbody>

 

[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