Forum Discussion
Multiple fields reduced Excel
This is very easy for an Excel 365 user but a little messier using traditional techniques.
= LET(
k,SEQUENCE(4,5),
INDEX(source,k))
- You'll need highlight the data in row 2 and use the Name box to name it as SOURCE (at least I'm assuming that's what Peter did
Thanks, I probably should put more effort into describing the defined Names; just because I haven't used a direct cell reference since 2015 certainly doesn't mean others are familiar with the approach. In this instance, I was unsure whether it is 'Source' that is the problem or my use of the 365 functions LET and SEQUENCE.
I am an infrequent user of the Power tools so I still find it somewhat difficult to visualise the result of pivoting and unpivot steps. I presume it becomes easier with practice.PeterBartholomew1 and @Wyn Hopkins Thank you both very much! Great team work!
If with legacy formulae, here
in A16
=INDEX($A$2:$T$6, INT( (ROW() - ROW($A$16) -1)/4)+1, 5*MOD( ROW() - ROW($A$16) -1, 4)+COLUMN() - COLUMN($A$16)+1 )drag it to the right and when entire line down till zero values appear.
