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.

11723	scott smith	scott smith
11723	jack harry	scott smith, jack harry
11723	ben phillips	scott smith, jack harry, ben phillips
11122	carrie cloud	carrie cloud
11122	gemma hugh	carrie cloud, gemma hugh
11122	billy temps	carrie cloud, gemma hugh, billy temp
11122	harry gent	carrie cloud, gemma hugh, billy temp, harry gent

<tbody>
</tbody>

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.