Need to concatenate data in one column based on values in another

I've got a rather long spreadsheet with 2 columns.  There are about 200,000 rows overall and about 40,000 unique values in column A.  Column B only has about 3000 values.

I need to create a list of the unique values in column A with the values from Column B being concatenated so that the result is a list of the about 40,000 unique values in Column A with Column B being anywhere from 1-10 values (combined with some delimiter).

This can be a list with these 2 columns on the same sheet, a different sheet, whatever...

Is there a way to do this with pivot tables or some plug-in (free or low cost)?  Both my columns are Text.

An example of something like this would be a list of last names (Column A) and first names (Column B) with me wanting to show all the first names (combined from Column B) separated by commas for every unique Last name (Column A).  That's not a perfect example, but roughly what I need to do.

This is Excel 2016 on Windows 10.
LVL 1
Gene KlamerusTechnical ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil FlemingConsultant and developerCommented:
You can certainly do this with VBA. Do you want unique "first names" as well, as it were?

Like this:list.jpg
0
Gene KlamerusTechnical ArchitectAuthor Commented:
Well, I used names as an example.  Every combination of the 2 columns is actually unique, so for every value in column A there will be only one row in Column B with any particular value.  Probably should have said that.  Both columns have repeating values, just no repeats of the same 2 values.
0
Neil FlemingConsultant and developerCommented:
Try this.

The code below does the following:
  • clones the contents of worksheet "source" to worksheet "result"
  • sorts the cloned list alphabetically (the sort assumes no header, but this is easily changed)
  • copies the first cell of the list to a target cell, along with the value in column two
  • moves through subsequent list rows
  • if the first cell is the same as preceding, adds the value from the second cell to the target (comma-separated)
  • if a new value appears in the first cell, starts a new target row
  • loops till it finds a blank cell in the list
  • stops
  • autosizes the target column


Here is the code
Sub GetValues()
Dim rSource As Range, rTarget As Range, rCheck As Range
Dim sCol1 As String

Set rSource = ActiveWorkbook.Worksheets("Source").Cells(1, 1).CurrentRegion
'clone the source range
Set rTarget = ActiveWorkbook.Worksheets("Result").Cells(1, 1).Resize(rSource.Rows.Count, rSource.Columns.Count)
'empty target worksheet
rTarget.Parent.Cells.ClearContents
'clone values
rTarget.Value = rSource.Formula

'switch worksheets
rTarget.Parent.Activate
'now work on the cloned version, leaving original in the "source" worksheet
Set rSource = rTarget
'sort for greater speed in rest of routine. Assumption is there is no header row (easily changed)
rSource.Sort rSource.Columns(1), xlAscending, rSource.Columns(2), , xlAscending, , , xlNo

'set rCheck to first row
Set rCheck = rSource.Resize(1, rSource.Columns.Count)
sCol1 = rCheck(1)
'create output one column away from source
Set rTarget = rSource.Offset(0, rSource.Columns.Count + 2).Resize(1, 1)

'loop through all rows
Do
'add to target
If rTarget = "" Then
rTarget = sCol1 & ": " & rCheck(2)
Else
rTarget = rTarget & ", " & rCheck(2)
End If

'check next row
Set rCheck = rCheck.Offset(1, 0)

'if next row is different from current col1 value, start a new target row
If rCheck(1) <> sCol1 Then
sCol1 = rCheck(1).Value
Set rTarget = rTarget.Offset(1, 0)
'keep current target visible on screen:
If rTarget.Row > 20 Then Application.Goto rTarget.Offset(-10, (-1 * rTarget.Column) + 1), True
End If
'enable interruption
DoEvents
Loop Until rCheck(1) = ""

'adjust target column width
rTarget.EntireColumn.AutoFit

End Sub

Open in new window


A sample workbook is attached. The button on the "source" worksheet page runs the code above.

Hope this does what you want. It may take a while to run on many thousands of rows.
Unique-concat.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gene KlamerusTechnical ArchitectAuthor Commented:
Thanks for the code.  It will take some time to play with I'm sure.

In the meantime I did find something cool on the Internet that is very reasonably priced.  It's called Kutools for Excel.  It has a lot of nice functionality.  Unfortunately I can't see the VBA behind it (but of course I would protect code like this too).

You might want to play with it some.  Their site is https://www.extendoffice.com
1
Neil FlemingConsultant and developerCommented:
Thanks. I MIGHT want to launch a competing product :)
0
Gene KlamerusTechnical ArchitectAuthor Commented:
Always good to look at the competition then.
1
Neil FlemingConsultant and developerCommented:
Always interesting to see what people can get away with charging for... :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.