Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|