Simple unpivot example in excel

One unpivoting example

(kohe teen sama loo ka eesti keeles)

Few days ago I had challenge to unpivot one quite complex crosstable.

And challenge was to use formulas to repeat in future with new data and prevent any macros

 

I did it!

 

I made simplification as example I’d like to share

 

http://1drv.ms/1JFMtGA (there is sample workbook)

 

Some step-by-step explanation

(sorry – I use ee-location, so in formulas I use ; rather than , and [#header..] is replace to [#päised..])

 

On my sample I have table with names and 8 columns with attributes

 

Name, Attr1, Attr2, Attr3, …

Henn, , 3, 5

Ants, 1, , 4

Peeter, , , 7

 

Unpivoting result have to be something like

Name, AttrName, AttrValue

Henn, Attr2, 3

Henn, Attr3, 5

Ants, Attr1, 1

Ants, Attr3, 4

Peeter, Attr3, 7

 

1st I create intermediate table. Actually I can extend origin table, but for safety I created new one

btw – in origin – the starting cross wasn’t table and contains several sections

  • 1st column:     Nr =ROW()-ROW(Tabel2)+1

this is trick to generate numbers to table – insensitive of table placement

  • 2nd column:     Nimi =VLOOKUP([@Nr];Tabel1;MATCH(Tabel2[#Päised];Tabel1[#Päised];0);FALSE)

this is another trick to get with VLOOKUP matching attribute from lookup table

  • 3rd in number of attributes > 0

=COUNTIF(OFFSET(Tabel1[Attr1];[@Nr]-1;0;1;COUNTIF(Tabel1[#Päised];”Attr*”));”>0″)

this is actually working with different table with different number of attributes in my actual example I had 17 attributes and with different names.

There I simply count how many attributes current line have

  • 4th is calculate starting rownumber in resulting table

=SUM(Tabel2[[#Päised];[NumOfAttr]]:[@NumOfAttr])-[@NumOfAttr]+1

this is “classical” running total formula working in table

  • 5th is trick – bitmask showing what attributes exist on particular row

Mask =SUMPRODUCT(

        (OFFSET(Tabel1[Attr1];[@Nr]-1;0;1;COUNTIF(Tabel1[#Päised];”Attr*”))>0)

        *    

        (2^(8-COLUMN(OFFSET($A$1;0;0;1;COUNTIF(Tabel1[#Päised];”Attr*”)))))

)

This SUMPRODUCT calculates bitmask on the way where each bit shows presence of attribute.

One vector is {true,false,…} vector comparing attribs

The second vector is list of 2-powers (in descending order) {256, 128, 64, …}

  • 6th step is to create same bitmask in binary format

Bitmask =DEC2BIN([@Mask];8)&”0″

on case more than 9 attributes – dec2bin have to be broken for 2 parts becouse limitation (? why) of dec2bin

This finalizes intermediate table

 

NExt I create resulting table

 

  • 1st     NR =ROW()-ROW(Tabel3)+1 — this is same as in previous table

it’s question of taste – on some cases I create max size table and unhide unneccessary rows with some IF-s. In that case I simply extend the table with autogenerated rows

  • 2nd     LineNr =MATCH([@Nr];Tabel2[ResLine];1)

this gives me line number in origin table🙂

  • 3rd     ResNR =COUNTIF(Tabel3[[#Päised];[LineNr]]:[@LineNr];[@LineNr])

gives me ordinar number of attribute

 

all the above garantees me that line number will never bigger than actual number of original lines and attribute number is never bigger than

number of attributes and start with 1.

 

  • 4th     AtrCol =IF([@ResNr]=1;

            FIND(“1”;INDEX(Tabel2[BitMask];[@LineNr]));

            FIND(“1”;INDEX(Tabel2[BitMask];[@LineNr]);OFFSET([@AttrCol];-1;0)+1)

            )

this finds the position of “1” in my bitmap mask – and actually position of attribute column in table

when attribute number = 1, I simply find “1” in bitmask

for next attributes I give additional “starting point” for find reading this from line above

 

Folllowing is simple

Nimi =VLOOKUP([@LineNr];Tabel1;MATCH(Tabel3[#Päised];Tabel1[#Päised];0);FALSE) — this is “old” trick

AttrName =OFFSET(Tabel1[[#Päised];[Attr1]];0;[@AttrCol]-1) — this read attribute name from Header

AttrValue =OFFSET(Tabel1[[#Päised];[Attr1]];[@LineNr];[@AttrCol]-1) — this read attribute value from correct line and column

 

Any comment are welcome

Henn

This entry was posted in Excel. Bookmark the permalink.

Lisa kommentaar

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Muuda )

Twitter picture

You are commenting using your Twitter account. Log Out / Muuda )

Facebook photo

You are commenting using your Facebook account. Log Out / Muuda )

Google+ photo

You are commenting using your Google+ account. Log Out / Muuda )

Connecting to %s