Reformatting data

[azedac]

Hi,

I have data in an array, like:

machine 1.1.2006 2.1.2006 3.1.2006
BTA-CUT-A 1 11 111
BTA-CUT-B 2 22 222
BTA-CUT-C 3 33 333
BTA-CUT-D 4 44 444

and I would like to do re-formation into rows:

BTA-CUT-A 1.1.2006 1
BTA-CUT-B 1.1.2006 2
BTA-CUT-C 1.1.2006 3
BTA-CUT-D 1.1.2006 4
BTA-CUT-A 2.1.2006 11
BTA-CUT-B 2.1.2006 22
BTA-CUT-C 2.1.2006 33
BTA-CUT-D 2.1.2006 44
BTA-CUT-A 3.1.2006 111
BTA-CUT-B 3.1.2006 222
BTA-CUT-C 3.1.2006 333
BTA-CUT-D 3.1.2006 444


Have somebody any solution or advice?
Thanks a lot
Azedac

 

[SydneyGeek]

Hi Azedac,

try this:

Book1
	A	B	C	D	E	F	G	H
1		1/01/2006	2/01/2006	3/01/2006
2	BTA-CUT-1	1	11	111		BTA-CUT-1	1/01/2006	1
3	BTA-CUT-2	2	22	222		BTA-CUT-1	2/01/2006	11
4	BTA-CUT-3	3	33	333		BTA-CUT-1	3/01/2006	111
5	BTA-CUT-4	4	44	444		BTA-CUT-2	1/01/2006	2
6						BTA-CUT-2	2/01/2006	22
7						BTA-CUT-2	3/01/2006	222
8						BTA-CUT-3	1/01/2006	3
9						BTA-CUT-3	2/01/2006	33
10						BTA-CUT-3	3/01/2006	333
11						BTA-CUT-4	1/01/2006	4
12						BTA-CUT-4	2/01/2006	44
13						BTA-CUT-4	3/01/2006	444
Sheet2

F2 is =OFFSET(A$2,(ROW()-2)/3,0)
G2 is =OFFSET($B$1,0,MOD(ROW()-2,3))
H2 is =VLOOKUP(F2,$A$2:$D$5,MOD(ROW()+1,3)+2)

Fill down

Denis