Excel: How do I 'gather' values to display in another cell

Question

I have two columns, Column A has a set of a few standard values and column B has all unique values. I'm only just experimenting with more complex ways of compiling data than the beginner level so I'm a bit at a loss.

I need to either have a lookup or create a macro that will list only the values in A (once each) but also display which values in B correspond to those in A

for example

A | B 
va1|abc
va1|bcd
Va2|xyz
va3|zab

will show (in a single cell) the following

va1: abc, bcd
va2: xyz
va3: zab

Please help!

@DanielMöller no, a pivot table will not return strings, only count of strings. — Scott Craner, Dec 14 '18 at 15:57
What have you tried so far? a search online for "excel formula unique values in column" yields several results including [this one](https://stackoverflow.com/questions/1429899/getting-unique-values-in-excel-by-using-formulas-only) — cybernetic.nomad, Dec 14 '18 at 15:58
tried the pivot table route but that only provides a list but isn't dynamic. I forgot to mention that column A and B will be a paste of data from an external source. Trying to find the simplest solution as this is the only pending field that I need to populate from a bunch of other things that run with a one click macro I have set up. — elkdee, Dec 14 '18 at 16:01
yea I though so VBA is the route...and I'm all up for using VBA (been fiddling with it lately and have a whole bunch of other things runinng on VBA macro on this workbook). but I can't seem to come up with a decent query to find the appropriate results :( — elkdee, Dec 14 '18 at 16:04
If you put both columns in the "left side" of the pivot table, you will get everything grouped and listed. — Daniel Möller, Dec 14 '18 at 16:07
Add the vba tags and show what you have to this point that is not working. — Scott Craner, Dec 14 '18 at 16:07
https://stackoverflow.com/questions/20557489/how-to-concatenate-unique-values-in-column-b-by-singular-values-in-column-a — SJR, Dec 14 '18 at 16:07
@SJR thanks for that link. it's perfect! Managed to use that successfully. and then will use another cell to compile the results all in one! — elkdee, Dec 14 '18 at 16:45

dwirony · Accepted Answer · 2018-12-14T16:21:47.973

Option Explicit
Sub Test()

Dim i As Long, j As Long, k As Long

k = 1

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Application.CountIf(Range("C:C"), Cells(i, 1).Value) = 0 Then
        Cells(k, 3).Value = Cells(i, 1).Value

        For j = 1 To Cells(Rows.Count, 1).End(xlUp).Row
            If Cells(j, 1).Value = Cells(k, 3).Value And _
               InStr(Cells(k, 4).Value, Cells(j, 2).Value) = 0 Then
                If Cells(k, 4).Value = "" Then
                    Cells(k, 4).Value = Cells(j, 2).Value
                Else
                    Cells(k, 4).Value = Cells(k, 4).Value & ", " & Cells(j, 2).Value
                End If
            End If
        Next j

        k = k + 1
    End If
Next i

For i = 1 To Cells(Rows.Count, 3).End(xlUp).Row
    Cells(i, 3).Value = Cells(i, 3).Value & ": " & Cells(i, 4).Value
    Cells(i, 4).ClearContents
Next i

End Sub

Edited for single cell

score 1 · Answer 2 · answered Dec 14 '18 at 16:29

1

In case your requirement is to "have the grouped data", and not exactly "have one single string per A", you can do this with a "pivot table" putting A and B in the row labels, like in the following picture:

answered Dec 14 '18 at 16:29

Daniel Möller

84,878
18
192
214

1

+1 as I think this is much more piratical solution. Later down the road, the single string output may need to be parsed which will lead to extra work just to arrive here. – urdearboy Dec 14 '18 at 17:09

Excel: How do I 'gather' values to display in another cell

2 Answers2