1

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

Mikayla Baer
  • 49
  • 6

1 Answers1

1

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))
David Leal
  • 6,373
  • 4
  • 29
  • 56
  • Is there a way to do this without using the "titles" or with a data set that has different dimensions? I want to apply this to a variety of different projects that will all be formatted the same - but would have different column titles and have many more columns/rows. – Mikayla Baer Mar 28 '23 at 17:35
  • @MikaylaBaer check the formula at the end with the solution that doesn't populate the headers. Please if my solution works, check the following link and consider accepting it and/or upvoting it at least. Thanks. [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers). You can use with different dimensions, it is not dimension specifics – David Leal Mar 28 '23 at 17:57