Search code examples
exceldatatablestransform

Transform Excel table into lists below each table parameter (and skip blanks)

I know there are similar questions, and I've tried to use all of the codes mentioned in them - but something isn't working for me. Please help.

My input is an excel table set up like this:

VISITCODE dm1 dm2 dm3 dm4
thing1 B A A
thing2 A B B
thing3 A B A
thing4 B B A

enter image description here

I'd like the output to look something like this:

enter image description here

Solution

  • You can use Excel formulas. The following spills the entire result including the headers all at once:

    =LET(in,A1:E5, h,TAKE(in,1), data,DROP(in,1), nh, {"VISIT CODE","Req","Type"},
 t, TAKE(data,,1), REDUCE(nh,DROP(h,,1),LAMBDA(ac,x, LET(
  col, INDEX(data,,XMATCH(x,h)), f, FILTER(HSTACK(t, col), col<>""), n, ROWS(f), 
  fill, IF(SEQUENCE(n)<=n,""), VSTACK(ac, VSTACK(HSTACK(x,"",""), 
  HSTACK(fill, f)))))))

    Here is the output: output

    It uses REDUCE/VSTACK pattern, check my answer to the question: how to transform a table in Excel from vertical to horizontal but with different length. The following:

    IF(SEQUENCE(n)<=n,"")

    is a trick to generate an array of empty values on each iteration (but there are multiple ways of doing it, this is just one of them). We filter by non-empty rows (f) on each iteration. The rest is just to accommodate the information into the desired output format on each iteration.

    All the required variables depend only on a single range (in) so it is easier to maintain. We play with DROP and TAKE to extract the information we need from in.

    If you don't want to generate the title, here is the updated formula:

    =LET(in,A1:E5, h,TAKE(in,1), data,DROP(in,1),
 t, TAKE(data,,1), DROP(REDUCE("",DROP(h,,1),LAMBDA(ac,x, LET(
 col, INDEX(data,,XMATCH(x,h)), f, FILTER(HSTACK(t, col), col<>""), n, ROWS(f), 
 fill, IF(SEQUENCE(n)<=n,""), VSTACK(ac, VSTACK(HSTACK(x,"",""), 
 HSTACK(fill, f)))))),1))