In Excel how to create multiple rows from a single data row

Question

Hi

I have an execl datasheet with data looking like this

id  desc        part no 1      Qty 1   part no 2   Qty 2   part no 3    Qty 3   
1   PartsName   382A012-3-0 3       382A023-3   3       382A012-25   3      

And need it to look like this

id  desc           part no          Qty
1    PartsName     382A012-3-0       3
1    PartsName     382A023-3/42-0    3
1    PartsName     382A012-25/86-0   3

This from a SQL Table so I could do it in SQL if that makes it easier

Anybody any suggestions as how to best to sort this?

Answer 1

Simply make a UNION in the SQL

SELECT id, desc, partNo, qty FROM parts 
UNION SELECT id, desc, partNo2 as partNo, qty2 as qty FROM parts 
UNION SELECT id, desc, partNo3 as partNo, qty3 as qty FROM parts 
ORDER BY id

Answer 2

If you don´t have the option of using SQL and need to use Excel. You can use the TRANSPOSE function. It is an array type function so you need to use the {}. If you haven´t used it before I recommend reading the help first. I however don´t think you can use transpose to get it exactly as you describe it. The id and desc column have to handled separately.

Answer 3

You need to add next formulas on a new sheet:

column A =MOD(ROW()+1,3) - it's like a skeleton :)

First row is headers

column B =IF($A2=0,OFFSET(Sheet1!$A$1,COUNTIF($A$2:$A2,0),COLUMN()-2),B1) - autofil col C with it

column D =OFFSET(Sheet1!$A$1,COUNTIF($A$2:$A2,0),COLUMN()+CHOOSE($A2+1,-2,0,2)) - autofil col E with it

one more - your datasheet is "sheet1"