Is it ok to make one manual adjustment to an automation task to avoid the complexity of a fully automated task?
For each ‘Code’ concatenate ‘Batch 1’ numbers. Sort ‘Code’ and sort ‘Batch 1’ values for each ‘Code’.
Sounds easy but what approach should we use:
- fully automated (possibly complex solution) ?
- mostly automated (minor manual step needed but simpler solution) ?
Download my Excel file to see solution details (includes Robert Gascon’s ‘Old but Wise’ solution!).
Pivot & Formula
My favorite. If you can live with one manual step (sorting ‘Batch 1’ values) then this is it!
My other favorite. I had to learn a trick in Power Query (modifying the M code: link).
Dynamic Array Formula
It was good practice but it did get complicated combining dynamic array functions.
Old School Formula
This just got ugly. I’m sure there’s a non dynamic array formula solution but mine was ugly 🙁
Old But Wise
Robert Gascon sent me his amazing solution. Notice how he used the INDEX function and also AGGREGATE function. Thanks Robert.
Daniel Choi sent me various dynamic array solutions and also a power query solution. Thank you Daniel. I will update the file later.
Which solution do you prefer?
Do you have a better solution?
This is me Kevin Lehrbass. I’m a Data Analyst and live in Markham Ontario Canada.
If you haven’t noticed I really like Microsoft Excel. It keeps evolving with new features so I’ll never learn everything. I guess that’s good. I’ll always have something to do.