- BY Kevin Lehrbass
- POSTED IN Solutions
- WITH 3 COMMENTS
- PERMALINK
- STANDARD POST TYPE

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