Is it ok to make one manual adjustment to an automation task to avoid the complexity of a fully automated task?
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) ?
Solutions
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!
Power Query
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.
Ultra Modern
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?
About Me
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.
Hi Kevin,
I inserted another Sheet in your file. My non-array formula in D2 under Code is:
=IF(ROW()-1>SUMPRODUCT(1/COUNTIF(A$2:A$19,A$2:A$19)),””,
LOOKUP(2,1/(COUNTIF(A$2:A$19,”<="&A$2:A$19)=AGGREGATE(15,6,
COUNTIF(A$2:A$19,"<="&A$2:A$19)/(COUNTIF(D$1:D1,A$2:A$19)=0),1)),A$2:A$19))
In E2 under Batch 1, my array formula is:
=IF(D2="","",
TEXTJOIN(", ",1,SMALL(IF(A$2:A$19=D2,B$2:B$19,""),
ROW(A$1:INDEX(A:A,COUNT(IF(A$2:A$19=D2,B$2:B$19,"")))))))
I grew older but wiser today! Like me, I consider the foregoing formulas as Old but Wise! I upload your updated file here:
https://1drv.ms/x/s!AiBw-RHGcxVvggSSV1vt3yEc_gst?e=6nopmd
Cheers and wish me a Happy Birthday,
Robert
Hi Robert,
Thanks for your ‘Old but Wise’ formula solution. I like how you used the INDEX function and also the AGGREGATE function!
And happy birthday!
Cheers,
Kevin
Pingback: aggregating text part 2 | My Spreadsheet Lab