Transpose Table

[Danilão]

Good afternoon, everyone.
I got this formula from an old article on another website, but it's causing problems.

Could someone help me?

 

[MikeVol]

=IFERROR(
    OFFSET(
        $A$5,
        SMALL(
            IF(($B$6:$D$9<>"")*(COLUMN($B$6:$D$9)=MATCH(G6,$B$5:$D$5,0)+1),
                ROW($B$6:$D$9)-5
            ),
            COUNTIF($G$6:G6,G6)
        ),
        MATCH(G6,$B$5:$D$5,0)
    ),
"")

 

[p45cal]

Since you're now using a version of Excel at least version 2016 then you can use Power Query to do this. See table at cell J14 of the attached. Should you change the table at A5 you'll need to refresh the table at J14 by right-clicking somewhere within it and choosing Refresh.

 

[Danilão]

=IFERROR(
    OFFSET(
        $A$5,
        SMALL(
            IF(($B$6:$D$9<>"")*(COLUMN($B$6:$D$9)=MATCH(G6,$B$5:$D$5,0)+1),
                ROW($B$6:$D$9)-5
            ),
            COUNTIF($G$6:G6,G6)
        ),
        MATCH(G6,$B$5:$D$5,0)
    ),
"")

Thanks a lot, Mike !
Exactly that.

 

[Danilão]

Since you're now using a version of Excel at least version 2016 then you can use Power Query to do this. See table at cell J14 of the attached. Should you change the table at A5 you'll need to refresh the table at J14 by right-clicking somewhere within it and choosing Refresh.

Thanks p45Cal .

 

[Peter Bartholomew]

=IFERROR(
    OFFSET(
        $A$5,
        SMALL(
            IF(($B$6:$D$9<>"")*(COLUMN($B$6:$D$9)=MATCH(G6,$B$5:$D$5,0)+1),
                ROW($B$6:$D$9)-5
            ),
            COUNTIF($G$6:G6,G6)
        ),
        MATCH(G6,$B$5:$D$5,0)
    ),
"")

An impressive bit of work! I corrected the syntax error but wasn't getting far with solving the problem.
I think I will have to stick with 365 and use

= UNPIVOTλ(place, date, event)

 

[Danilão]

An impressive bit of work! I corrected the syntax error but wasn't getting far with solving the problem.
I think I will have to stick with 365 and use

= UNPIVOTλ(place, date, event)

Thanks for the idea, Peter.

 

[Peter Bartholomew]

Odd that I didn't post a workbook. Maybe I assumed it could not be used by the OP but, for the sake of completeness:

// --- Workbook module ---

// Function to broadcast an array over the dimensions of a second array
B∕CASTλ = LAMBDA(array, over, IF({1}, array, over));

// Function to unpivot multiple columns of crosstab data
UNPIVOTλ
= LAMBDA(rowHdr, fldHdr, data, [ignore],
    LET(
        recordHdr,  TOCOL(IF(data<>"", B∕CASTλ(rowHdr, fldHdr), NA()), 3),
        attrName,   TOCOL(IF(data<>"", B∕CASTλ(fldHdr, rowHdr), NA()), 3),
        fieldVal,   TOCOL(data, 1),
        result, HSTACK(attrName, fieldVal, recordHdr),
        SORT(result)
    )
);