Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find duplicates and concatenate
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find duplicates and concatenate
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 "rpick60" wrote: 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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find duplicates and concatenate
On Jun 4, 11:39*pm, Joel wrote:
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 "rpick60" wrote: 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"- Hide quoted text - - Show quoted text - 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find duplicates and concatenate
On Jun 5, 12:10*am, rpick60 wrote:
On Jun 4, 11:39*pm, Joel wrote: 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 "rpick60" wrote: 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"- Hide quoted text - - Show quoted text - 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 - Joel Sorry my mistake I figured it out. It works great, thanks for the quick response |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find duplicates and concatenate
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. "rpick60" wrote: On Jun 4, 11:39 pm, Joel wrote: 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 "rpick60" wrote: 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"- Hide quoted text - - Show quoted text - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concatenate 2 columns (A2+B2) and compare for duplicates | Excel Discussion (Misc queries) | |||
find duplicates and concatenate | Excel Worksheet Functions | |||
find duplicates and concatenate | Excel Worksheet Functions | |||
Concatenate with no duplicates | Excel Discussion (Misc queries) | |||
Find Duplicates & Concatenate (cpm) | Excel Discussion (Misc queries) |