How to Concat or VBA Based on a cell value

[scottyyboyy]

hi all,

i have 800,00 rows of data and i need to move the text into 1 cell where they have a matching value in another cell.

[TABLE="width: 300, align: center"]
<tbody>[TR]
[TD]11723[/TD]
[TD]scott smith[/TD]
[TD]scott smith[/TD]
[/TR]
[TR]
[TD]11723[/TD]
[TD]jack harry[/TD]
[TD]scott smith, jack harry[/TD]
[/TR]
[TR]
[TD]11723[/TD]
[TD]ben phillips[/TD]
[TD]scott smith, jack harry, ben phillips[/TD]
[/TR]
[TR]
[TD]11122[/TD]
[TD]carrie cloud[/TD]
[TD]carrie cloud[/TD]
[/TR]
[TR]
[TD]11122[/TD]
[TD]gemma hugh[/TD]
[TD]carrie cloud, gemma hugh[/TD]
[/TR]
[TR]
[TD]11122[/TD]
[TD]billy temps[/TD]
[TD]carrie cloud, gemma hugh, billy temp[/TD]
[/TR]
[TR]
[TD]11122[/TD]
[TD]harry gent[/TD]
[TD]carrie cloud, gemma hugh, billy temp, harry gent[/TD]
[/TR]
</tbody>[/TABLE]


so if the number matches in column A then i need it to look like column c

i have tried using a normal concatenate but this doesn't work due to the amount or rows and data

Can anyone help with this and sorry if i'm not making sense

 

[rpaulson]

try this on a copy of your file.

Sub do_it()
Range("C:C").ClearContents
[C1] = [B1]

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row

If Cells(r, "A") = Cells(r - 1, "A") Then

Cells(r, "C") = Cells(r - 1, "C") & ", " & Cells(r, "B")


Else
Cells(r, "C") = Cells(r, "B")
End If
Next r
End Sub

hth,

Ross

 

[Eric W]

If your data starts on row 2, and is sorted by the value in A, put this formula in C2:


=IF(A2<>A1,B2,C1&", "&B2)

and drag down. If your data is not sorted, use this formula:

=IFERROR(LOOKUP(2,1/($A$1:$A1=A2),$C$1:$C1)&", ","")&B2


With 800,000 rows though, you might be better off running a macro as needed. Ross's is a good start, but we can speed it up by using internal arrays.