How to Concat or VBA Based on a cell value

scottyyboyy

New Member
Joined
Feb 2, 2015
Messages
41
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.

11723scott smithscott smith
11723jack harryscott smith, jack harry
11723ben phillipsscott smith, jack harry, ben phillips
11122carrie cloudcarrie cloud
11122gemma hughcarrie cloud, gemma hugh
11122billy tempscarrie cloud, gemma hugh, billy temp
11122harry gentcarrie 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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,105
try this on a copy of your file.

Code:
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,212
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,659
Messages
5,524,147
Members
409,562
Latest member
meeranaskar

This Week's Hot Topics

Top