Help
 

Have data in 2 columns (A and B). I want to return in column C all the
values of column B for that particular contact in column A. If more than one
value is entered, then the remainder of cells in column C for that contact is
remain blank

What formula can be used to do this, as I have 20000 records, and doing it
manually may take some time

Column A Column B Column C
HAYES 47 47
LESTER 47 47
SMITH 47 47
TIFFANY 47 47
JACKSON 47 47
RICKMAN 47 47, 82
RICKMAN 82
CHARTER 47 47
DUGGETT 47 47
HARKNESS 47 47
JONES 47 47, 144, 146
JONES 144
JONES 146

Help
 

Try this macro:

Sub Summarise()

Dim ws1 As Worksheet
Dim irow As Long, orow As Long
Dim Lastrow As Long
Dim col As Integer

Set ws1 = Worksheets("Sheet1")

col = 1 '<=== column for lastrow calculation

With ws1

Lastrow = .Cells(Rows.Count, col).End(xlUp).Row
irow = 2
Set rnga = Range("A2:A" & Lastrow)
Do
n = Application.CountIf(rnga, .Cells(irow, "A"))
orow = irow
StrB = ""
For i = 1 To n
StrB = StrB & .Cells(irow, "B") & ","
irow = irow + 1
Next i
StrB = Left(StrB, Len(StrB) - 1)
.Cells(orow, "C") = StrB
Loop Until irow = Lastrow

End With

End Sub

HTH

"holyman" wrote:

Have data in 2 columns (A and B). I want to return in column C all the
values of column B for that particular contact in column A. If more than one
value is entered, then the remainder of cells in column C for that contact is
remain blank

What formula can be used to do this, as I have 20000 records, and doing it
manually may take some time

Column A Column B Column C
HAYES 47 47
LESTER 47 47
SMITH 47 47
TIFFANY 47 47
JACKSON 47 47
RICKMAN 47 47, 82
RICKMAN 82
CHARTER 47 47
DUGGETT 47 47
HARKNESS 47 47
JONES 47 47, 144, 146
JONES 144
JONES 146

Help
 

Many thanks. Is this possible to do as a formula as well

"Toppers" wrote:

Try this macro:

Sub Summarise()

Dim ws1 As Worksheet
Dim irow As Long, orow As Long
Dim Lastrow As Long
Dim col As Integer

Set ws1 = Worksheets("Sheet1")

col = 1 '<=== column for lastrow calculation

With ws1

Lastrow = .Cells(Rows.Count, col).End(xlUp).Row
irow = 2
Set rnga = Range("A2:A" & Lastrow)
Do
n = Application.CountIf(rnga, .Cells(irow, "A"))
orow = irow
StrB = ""
For i = 1 To n
StrB = StrB & .Cells(irow, "B") & ","
irow = irow + 1
Next i
StrB = Left(StrB, Len(StrB) - 1)
.Cells(orow, "C") = StrB
Loop Until irow = Lastrow

End With

End Sub

HTH

"holyman" wrote:

Have data in 2 columns (A and B). I want to return in column C all the
values of column B for that particular contact in column A. If more than one
value is entered, then the remainder of cells in column C for that contact is
remain blank

What formula can be used to do this, as I have 20000 records, and doing it
manually may take some time

Column A Column B Column C
HAYES 47 47
LESTER 47 47
SMITH 47 47
TIFFANY 47 47
JACKSON 47 47
RICKMAN 47 47, 82
RICKMAN 82
CHARTER 47 47
DUGGETT 47 47
HARKNESS 47 47
JONES 47 47, 144, 146
JONES 144
JONES 146