Table columns to stack into pivot rows

With O365, you can have an all-in-one solution. Delete all expected results.

Formula:

Please Login or Register  to view this content.

Change the bits in red to suit.

With some conditional formatting to tart up the appearance of the table.

Amazing Glenn, thankyou! Only been on 365 for a short time so need to get familiar with these new functions

The www have remarkably FEW examples of how to build these up. However, use this variant first:

Formula:

Please Login or Register  to view this content.

Make sure there's pleanty of space around the cell with the formula. Replace the red F with any of the other sub-formula letters to see what each one returns.

F itself is fairly self-explanatory... just bringing the individual bits together at the end.

E is harder to understand. See how you get on with B, C and D and I'll scribble an explanation of E (as best I can).

MAP(INDEX(D,,1),INDEX(D,,2),INDEX(D,,3),LAMBDA(x,y,z,SUM(IF(INDEX(C,,1)=x,IF(INDEX(B,,2)=y,IF(INDEX(B,,3)=z,INDEX(B,,4),0))))))

Normally you'd use SUMIFS to get an answer like this. But SUMIFS will not accept arrays in place of ranges. So this sort of thing is needed.

It uses MAP in combination with LAMBDA. The main block of data after the reorganisation and stacking of columns is B.

Change the last letter of the main formula to D. The first row looks like:

CR 100000 12354


Using this construction, MAP goes through each row of D, one at a time.

MAP(CR,10000,12354,LAMBDA(x,y,z,SUM(IF(INDEX(C,,1)=x,IF(INDEX(B,,2)=y,IF(INDEX(B,,3)=z,INDEX(B,,4),0))))))

the bits in RED are the 3 criteria for the "SUMIFS" that comes at the end. These need to be assigned names, inside LAMBDA, which does all the hard work. I called them x, y, and z.

MAP(INDEX(D,,1),INDEX(D,,2),INDEX(D,,3),LAMBDA(x,y,z,SUM(IF(INDEX(C,,1)=x,IF(INDEX(B,,2)=y,IF(INDEX(B,,3)=z,INDEX(B,,4),0))))))


So CR = X, 100000=Y, etc.

MAP(INDEX(D,,1),INDEX(D,,2),INDEX(D,,3),LAMBDA(x,y,z,SUM(IF(INDEX(C,,1)=x,IF(INDEX(B,,2)=y,IF(INDEX(B,,3)=z,INDEX(B,,4),0))))))

The bits in red (above) are the equivalent to ranges in a normal SUMIFS. So where:

column 1 of B =x AND
column 2 of B =y AND
column 3 of B =z...

it adds up the value(s) from column 4 of B. It then goes on to the next row of C, namely:

CR 100000 12453


and starts again. Clear as mud???

FWIW, I learned how to do these things by mucking around with problems like yours. Two years ago, I hadn't a clue how they worked... and I'm still not nearly as good as some others here in applying them. But, I can get them to work (at least some of the time...).