Excel - Concatenate all values with same number assigned in row

Question

no clue where to start with this one - essentially this is my issue.

I need to create a function or macro that can concatenate all values with the same number in their row.

I'm using an employee list at work and each employee has cost centres attached.

What I need is to be able to concatenate all names next to 12510 + 13030 etc. etc. into their own cells.

Any help would be appreciated.

EDIT: I've tried all simple things which simply put was concatenating the values manually. The cost centre codes have more than one person attached, I want to concatenate the values of everyone who has that cost centre number into the one sell similar to =Concatenate(A1, " ", B1) etc

What have you tried? FYI `=A1&B1` is the same as `=Concatenate(A1,B1)`. So you have duplicate numbers in a row, and for each of those duplicates, you want to put them all together in one cell? — BruceWayne, Oct 27 '16 at 22:08
See edit, I've tried all simple things which simply put was concatenating the values manually. The cost centre codes have more than one person attached, I want to concatenate the values of everyone who has that cost centre number into the one sell similar to =Concatenate(A1, " ", B1) etc. — R Mannix, Oct 27 '16 at 22:18
https://stackoverflow.com/questions/18925313/vlookup-multiple-columns — Tim Williams, Oct 27 '16 at 22:27
If I was doing this as a one-off exercise, I would sort the data on column C, then place a formula in D2 which says `=IF(C2=C1,D1&", ","")&A2&" "&B2` (or something similar) and in E2 I would put `=IF(C2=C3,"",D2)`. I would then copy D2 and E2 down through all rows. Then I would paste values from column E to column F. Then I would sort everything on column F and get rid of the ones that had blanks in column F, and then delete columns D and E (and A and B). (But I would do something entirely different if it wasn't a one-off exercise.) — YowE3K, Oct 28 '16 at 01:47
this is a wild guess, but maybe you just want to group them in a tabbular PivotTable https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576 — Slai, Oct 28 '16 at 09:11

score 0 · Accepted Answer · answered Oct 28 '16 at 17:55

this should help you:

Option Explicit

Sub ListCentreCostNames()
    Dim empNamesRng As Range, ccRng As Range, ccCell As Range, cell As Range
    Dim names As String

    With Worksheets("centrecost") '<--| change "centercost" to your actual worksheet name
        Set ccRng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
        Set empNamesRng = Intersect(.Columns(2), ccRng.EntireRow)
        With .UsedRange
            With .Resize(1, 1).Offset(, .Columns.Count + 1)
                With .Resize(ccRng.Rows.Count)
                    .Value = ccRng.Value
                    .RemoveDuplicates Array(1), Header:=xlYes
                    For Each ccCell In .SpecialCells(xlCellTypeConstants)
                        ccRng.AutoFilter field:=1, Criteria1:=ccCell.Value
                        If Application.WorksheetFunction.Subtotal(103, ccRng) - 1 > 0 Then
                            names = ""
                            For Each cell In empNamesRng.Offset(1).Resize(empNamesRng.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
                                names = names & cell.Value & " "
                            Next cell
                        End If
                        ccCell.Offset(, 1) = names
                    Next ccCell
                End With
            End With
        End With
        .AutoFilterMode = False
    End With
End Sub

Excel - Concatenate all values with same number assigned in row

1 Answers1