find duplicates and concatenate

  • Thread starter Thread starter rpick60
  • Start date Start date
R

rpick60

I am trying to find all duplicates in a range R8:R1000 and then grab
the value in Column C of the the duplicate rows and concatenante them
all to the cell in S of that row

example
As you can see find each cell in range R8:R1000 and the grab value in
column C
and put all duplicates in column s
for each P1 in the range the results are 2,4,5 for each duplicate.


c d ....... r s (results)
2 P1 2,4,5
3 P2 3
12 P5 12,13
13 P5 12,13
4 P1 2,4,5
5 P1 2,4,5
10 P22 10


Any help would be great!


and "Go Wings"
 
Sub Cat_Dups()

For Each Cell1 In Range("R8:R1000")
CatStr = ""
For Each Cell2 In Range("R8:R1000")
If Cell1.Value = Cell2.Value Then
If CatStr = "" Then
CatStr = Range("C" & Cell2.Row).Value
Else
CatStr = CatStr & ", " & Range("C" & Cell2.Row).Value
End If
End If
Next Cell2
Cell1.Offset(0, 1).Value = CatStr
Next Cell1
End Sub
 
Sub Cat_Dups()

For Each Cell1 In Range("R8:R1000")
   CatStr = ""
   For Each Cell2 In Range("R8:R1000")
      If Cell1.Value = Cell2.Value Then
         If CatStr = "" Then
            CatStr = Range("C" & Cell2.Row).Value
         Else
            CatStr = CatStr & ", " & Range("C" & Cell2.Row).Value
         End If
      End If
   Next Cell2
   Cell1.Offset(0, 1).Value = CatStr
Next Cell1
End Sub









- Show quoted text -
Click to expand...

Joel
Thanks for th help. But i only get 1 value in column S. I am looking
for all of the duplicates to be listed.
Am i missing something
thanks again
 
Joel
Thanks for th help. But i only get 1 value in column S. I am looking
for all of the duplicates to be listed.
Am i missing something
thanks again- Hide quoted text -

- Show quoted text -
Click to expand...

Joel
Sorry my mistake I figured it out.
It works great, thanks for the quick response
 
It should work. Try an experiment by emptying some cell in column R. the
code presently considers two empty cell a duplicate. If this works, then the
cell do not equal each other in column R.

Also make sure you are looking at column S.
 
Back
Top