Is there an alternative formula or add-in that can unpivot a table like Excel Power Query?

1
votes

I need to unpivot a table in Excel, by shifting (not transposing) some columns of data into a single column of data over multiple rows.

From what I have read, the Power Query add-in is exactly what I would need. However, my company still runs Excel 2010 and does not have the "Software Assurance" package, so I cannot install the Power Query add-in. Is there a formula or other method to unpivot a table without Power Query?

My data looks as follows, with the numbers under each day showing the day's consumption:

  • SKU Monday Tuesday Wednesday Thursday
  • ABC 5 7 2 3
  • DEF 1 5 2 7

I want it to look like this:

  • SKU Day Consumption
  • ABC Monday 5
  • ABC Tuesday 7
  • ABC Wednesday 2
  • ABC Thursday 3
  • DEF Monday 1
  • DEF Tuesday 5
  • DEF Wednesday 2
  • DEF Thursday 7

I thought that transposing would work, but it swaps the rows and columns instead.

1
excel

1 Answers

0
votes

I know this is not what you're asking, however: Transposing does work in your case, all you need to do after is to fill down an extra column which has all ABC/DEF/etc. values according to your transposed table's header positions. In other words - it is just a matter of copying the header from a column to the leftmost column of your data, and copying its values down.

Assuming your data is not astronomically large - it is doable in a few minutes. However I see that I get the same result you wish to get, so I assume this is an option:

enter image description here

Alternatively, I found this online: https://www.excelforum.com/excel-formulas-and-functions/1181023-formula-to-unpivot-data.html

Re: Formula to unpivot data You need a helper in c2, copied down:

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

Then in G2, this array formula: =IFERROR(INDEX($A$2:$A$5,MATCH(0,--(COUNTIF($G$1:G1,$A$2:$A$5)=$C$2:$C$5),0)),"")

Then in H2, this ordinary formula: =IF(G2="","",--TRIM(MID(SUBSTITUTE(","&VLOOKUP(G2,$A$2:$B$5,2,FALSE),",",REPT(" ",125)),125*COUNTIF($G$2:G2,G2),125)))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...