CODEWITHSUNDEEP
excelexcel-formula
JSCRB
JSCRB

Reputation: 33

Stack columns and transpose

I have data such as this:

enter image description here

I would like to separate each cell at the | and then transpose it like this:

enter image description here

Some sku's have hundreds of values, so it is not time effect to use "Text to Columns" and then transpose each sku manually. Is there a way to do this via formulas?

Upvotes: 0

Views: 268

Answers (2)

bosco_yip
bosco_yip

Reputation: 3802

Here is a formula solution

Assume "Input table " put in A1:B2,

In "output" table A5, formula copied down :

=IFERROR(INDEX(A$1:A$2,MATCH(1,INDEX(--(COUNTIF($A$4:A4,A$1:A$2)<LEN($B$1:$B$2)-LEN(SUBSTITUTE($B$1:$B$2,"|",""))+1),0),0)),"")

In "output" table B5, formula copied down :

=IF(A5="","",TRIM(MID(SUBSTITUTE("|"&VLOOKUP(A5,$A$1:$B$2,2,0),"|",REPT(" ",99)),COUNTIF(A$5:A5,A5)*99,99)))

Upvotes: 0

Terry W
Terry W

Reputation: 3257

Please refer to this article to find out how to use Power Query on your version of Excel. It is available in Excel 2010 Professional Plus and later versions. My demonstration is using Excel 2016.

Steps are:

  1. Use From Table function under the Data tab to load your 2-column data table to the power query editor;
  2. Highlight the second column, use Split Columns function under the Transform tab to split the column by custom delimiter |, and in the advanced option choose to put the results into Rows;
  3. Close & Load the data to a new worksheet (by default).

Let me know if you have any questions. Cheers :)

Upvotes: 2

Related Questions