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

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,929
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.
 
jaeiow said:
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.
Click to expand...
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)
    )
);
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

Share this page

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