Change data in vertical table to individual rows

Question

I have an Excel workbook with the following format:

 | A  | B     | C     | D     | E     |   
 |----|-------|-------|-------|-------|  
1|    | po#1  | po#2  | po#3  | po#1  |
2|    | date1 | date2 | date3 | date4 |
3|sku1| qty1  | qty4  | qty7  | qty10 | 
4|sku2| qty2  | qty5  | qty8  | qty11 |
5|sku3| qty3  | qty6  | qty9  | qty12 |

that I need to convert to the following format:

  | A      | B     | C     | D     |
  |--------|-------|-------|-------|
 1|  po#1  | date1 | sku1  | qty1  |
 2|  po#1  | date1 | sku2  | qty2  |
 3|  po#1  | date1 | sku3  | qty3  |
 4|  po#1  | date4 | sku1  | qty10 |
 5|  po#1  | date4 | sku2  | qty11 |
 6|  po#1  | date4 | sku3  | qty12 |
 7|  po#2  | date2 | sku1  | qty4  |
 8|  po#2  | date2 | sku2  | qty5  |
 9|  po#2  | date2 | sku3  | qty6  |
10|  po#3  | date3 | sku1  | qty7  |
11|  po#3  | date3 | sku2  | qty8  |
12|  po#3  | date3 | sku3  | qty9  |

without using VBA.

create a temporary column and transpose the data – itzmurd4 Nov 03 '15 at 20:09 — itzmurd4, Nov 03 '15 at 20:09

score 0 · Accepted Answer · edited May 23 '17 at 12:32

0

On a copy, delete Row2 and apply the reverse pivot technique detailed (with pictures!) here. When you get to the Table, switch the order of the Row and Column columns and insert a new column between the two. Populate this with an HLOOKUP from your original data (that still has Row2). Sort ascending on Column Value. Delete the labels and, if desired, convert Table to Range.

Edit Above addresses an earlier version of the OP. To address the current version Rows1 and 2 could be replaced with the result of a formula such as =A1&"|"&A2 (based on the contents of A1 and A2 before replacement) with copying across to suit, then splitting these apart again in the Table by text manipulation with searching for |.

edited May 23 '17 at 12:32

Community

1
1

answered Nov 03 '15 at 20:18

pnuts

58,317
11
87
139

Very helpful - but we're missing a step. Sometimes there is the same "po#" with different "date" values, so doing the HLOOKUP will not necessarily matchup the "date" with the correct "po#". – Andres Marcos Nov 03 '15 at 22:51
Yes I should have been clearer from the start. Thanks for trying to help anyway pnuts! I've been struggling with this for a while now. – Andres Marcos Nov 04 '15 at 20:09
1

Thanks pnuts! That did it. – Andres Marcos Nov 04 '15 at 21:15
This was very helpful - I want to see how to do it with VBA. I've posted a separate question with the same problem but asking for a solution with VBA. Pnuts if you know how to do it would be very helpful! Question:http://stackoverflow.com/questions/33532185/change-data-in-vertical-table-to-individual-rows-using-vba – Andres Marcos Nov 04 '15 at 21:19

Change data in vertical table to individual rows

1 Answers1

Linked