29 Aug 2020

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.

 

 

2 Comments

  1. Robert H. Gascon says:

    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

    1. Kevin Lehrbass says:

      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

Post a comment