UNPIVOT

=UNPIVOT(range,range)

pivot_cols
range of cells to not be unpivoted
unpivot_cols
range of cells to be pivoted

Translates a set of columns in a table into attribute-value pairs, combined with the rest of the values in each row

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,963
Office Version
  1. 365
Platform
  1. Windows
UNPIVOT translates a set of columns in a table into attribute-value pairs, combined with the rest of the values in each row.

Based on the Power Query Table.Unpivot function. I used several lambda helper functions which will be listed below.

I will list the name of each lambda above the code for each formula.

UNPIVOT
Excel Formula:
=LAMBDA(pivot_cols,unpivot_cols,
    LET(
        pvt_rng,pivot_cols,
        upvt_rng,unpivot_cols,
        pvt_data,DROP(pvt_rng,1),
        upvt_data,DROP(upvt_rng,1),
        pvt_head,TAKE(pvt_rng,1),
        upvt_head,TAKE(upvt_rng,1),
        pvt_col_count,COLUMNS(pvt_data),
        upvt_col_count,COLUMNS(upvt_data),
        total_rows,ROWS(pvt_data)*upvt_col_count,
        pvt_idx,RPTCOL(total_rows,pvt_col_count),
        upvt_idx,RPTROW(total_rows,pvt_col_count,upvt_col_count),
        pvt_res,INDEX(pvt_data,upvt_idx,pvt_idx),
        atr,ATT(total_rows,upvt_head),
        val,PVAL(upvt_data,total_rows,upvt_col_count),
        HSTACK(
            HSTACK(
                VSTACK(pvt_head,pvt_res),
                VSTACK("Attribute",atr)
            ),
            VSTACK("Value",val)
        )
    )
)

UNPIVOT
ABCDEFGHIJ
1CompanyAreaColorIDLetterCompanyAreaAttributeValue
2CompanyANorthRed1ACompanyANorthColorRed
3CompanyBSouthBlue2BCompanyANorthID1
4CompanyCEastYellow3CCompanyANorthLetterA
5CompanyDWestGreen4DCompanyBSouthColorBlue
6CompanyBSouthID2
7CompanyBSouthLetterB
8CompanyCEastColorYellow
9CompanyCEastID3
10CompanyCEastLetterC
11CompanyDWestColorGreen
12CompanyDWestID4
13CompanyDWestLetterD
Sheet3
Cell Formulas
RangeFormula
G1:J13G1=UNPIVOT(A1:B5,C1:E5)
Dynamic array formulas.


Additional helper lambdas...

RPTROW
Excel Formula:
=LAMBDA(
    ro,co,div,
    MAKEARRAY(
        ro,co,
            LAMBDA(
                r,c,
                    INT((r-1)/div)+1
            )
    )
)

RPTCOLUMN
Excel Formula:
=LAMBDA(
    ro,co,
        MAKEARRAY(
            ro,co,
                LAMBDA(
                    r,c,c
                )
            )
)

ATT
Excel Formula:
=LAMBDA(
    ro,h,
        LET(
            co,COLUMNS(h),
            s,SEQUENCE(ro,1,0),
            INDEX(h,MOD(s,co)+1
        )
    )
)

PVAL
Excel Formula:
=LAMBDA(
    rng,ro,co,
        LET(
            s,SEQUENCE(ro,,0),
            rx,INT(s/co)+1,
            cx,MOD(s,co)+1,
            INDEX(rng,rx,cx)
        )
)
 
Upvote 0
Would you know how to apply the cosmetic grouping. In other words, every second instance of the same Index would appear as blank. I had a little hack where I expanded it with blanks and then sorted it. But I don't see how I could implement that here.
 
Would you know how to apply the cosmetic grouping. In other words, every second instance of the same Index would appear as blank. I had a little hack where I expanded it with blanks and then sorted it. But I don't see how I could implement that here.
For this quick fix (if you can call it that), calls "AREPT", but this is a first column only solution (my requirement is met). Above posted the multicolumn function. Thanks for that by the way, @lrobbo314
Excel Formula:
Unpivot = LAMBDA(pivot_cols, [cosmetic],
    LET(
        pvt_rng, CHOOSECOLS(pivot_cols, 1),
        upvt_rng, DROP(pivot_cols, , 1),
        pvt_data, DROP(pvt_rng, 1),
        upvt_data, DROP(upvt_rng, 1),
        upvt_head, TAKE(upvt_rng, 1),
        pvt_col_count, COLUMNS(pvt_data),
        upvt_col_count, COLUMNS(upvt_data),
        pvt_rows, ROWS(pvt_data),
        total_rows, pvt_rows * upvt_col_count,
        stacked_s, AREPT(SEQUENCE(upvt_col_count), pvt_rows),
        pvt_idx, RPTCOL(total_rows, pvt_col_count),
        upvt_idx, RPTROW(total_rows, pvt_col_count, upvt_col_count),
        pvt_res, IFERROR(
            INDEX(
                pvt_data,
                IF(
                    OR(ISOMITTED(cosmetic), NOT(cosmetic)),
                    upvt_idx,
                    IF(stacked_s = 1, upvt_idx, 999)
                ),
                pvt_idx
            ),
            IF(stacked_s = 2, "Remark", "")
        ),
        atr, ATT(total_rows, upvt_head),
        val, pval(upvt_data, total_rows, upvt_col_count),
        HSTACK(HSTACK(pvt_res, atr), val)
    )
);
 

Forum statistics

Threads
1,224,829
Messages
6,181,218
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top