Haven’t I done this before? It was almost identical except for two details.
(Download my Excel file here)
Why didn’t I remember that I’d solved this and created a post?
Who knows but there are two important differences I’ll review in this post:
- preferred formula solution
- power query variation
1. Formula Solution
The person I helped preferred a simple solution with a couple of quick manual steps:
- create unique ID list =UNIQUE($A$4:$A$13)
- concatenate Codes =TEXTJOIN(“, “,TRUE,IF($A$4:$A$13=$L4,$B$4:$B$13,””))
- copy/paste formula above as values
- text to columns to split the codes
Q: Why not fully automate it?
A: It’s a quick one time task. Fully automating could take more time.
That’s fair. Now I’ll examine a Power Query solution variation.
Power Query Variation
This variation uses Text.Combine using only the menu (no manual M code changes).
Menu Only Solution Steps:
Load data into Power Query.
The magic is Operation ‘All Rows’ in the Group By below.
When I group by ID and use ‘All Rows’ in new column ‘AllCodes’, M creates a table in each row.
I don’t need the entire table for each ID (row). I just need the values from column Code. The Custom Column allows me to extract these values into a list.
Tip: click to the right of text ‘List’ to see the values in bottom left.
In the column headers press the double arrows, select ‘Extract Values’.
We end up with this:
I did a couple of cleanup steps and then split column ‘Code 1’ by delimiter (comma).
Close and Load to a new sheet. Yes it’s fully automated but it does take time to learn this approach.
Simple or Robust?
Business users with a 1 time task may prefer a formula solution despite a couple of manual steps.
Data nerds like me prefer to invest time to fully automate and learn something new along the way.
I did get confused in Step 2. What am I supposed to extract and from where?
Answer was [AllCodes][Code] (column Code that’s inside the table in column AllFields).
I always try to review what I’ve learned so I don’t forget for the next time it comes in handy (hence making this post to practice!).
I’m a Data Analyst from Markham Ontario Canada (near Toronto). I need to keep my Excel skills up to date for my job but I also find that playing around in Excel helps pass the time during Covid.