Data Trasposition

[Maurizio]

Cartel1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	HowcanIgetthistrasposition?
2
3	Before:
4	DOCUMENTO	CODICE	KG	LIRE	TOTALE
5	1	12	2	10000	20000
6	1	5	10	12000	24000
7	1	10	5	11000	55000
8	2	2	6	5500	33000
9	2	3	9	1000	9000
10	After:
11	DOCUMENTO	COD	KG	LIRE	TOTALE	COD	KG	LIRE	TOTALE	COD	KG	LIRE	TOTALE
12	1	12	2	10000	20000	5	10	12000	24000	10	5	11000	55000
Foglio1

 

[Maurizio]

Sorry, so edited:

Cartel1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	HowcanIgetthistrasposition?
2
3	Before:
4	DOCUMENTO	CODICE	KG	LIRE	TOTALE
5	1	12	2	10000	20000
6	1	5	10	12000	24000
7	1	10	5	11000	55000
8	2	2	6	5500	33000
9	2	3	9	1000	9000
10	After:
11	DOCUMENTO	COD	KG	LIRE	TOTALE	COD	KG	LIRE	TOTALE	COD	KG	LIRE	TOTALE
12	1	12	2	10000	20000	5	10	12000	24000	10	5	11000	55000
13	2	2	6	5500	33000	3	9	1000	9000
14
Foglio1

 

[maxflia10]

Maurizio,

I don't think it's a matter of transposing. Just cut and paste or reference the cell and drag to the right. But then I may be wrong!!
This message was edited by maxflia10 on 2002-10-23 17:54

 

[Yogi Anand]

Hi maurizio.rota:

I believe this can be done in many different ways, I used promarily the OFFSET function to transpose the data to your specification. The main formula, I used in cell B12 is ...

=IF(INT((COLUMN()-1)/5)>=COUNTIF($A$5:$A$9,$A12),"",OFFSET($A$5,MATCH($A12,$A$5:$A$9,0)-1+INT(COLUMN()/5)-(MOD(COLUMN(),5)=0)+0,IF(MOD(COLUMN(),5)=0,5,MOD(COLUMN(),5))-1))

This formula can then be copied to cells B12:O13

I am sure the formula can be shortened, and indeed there might be better ways of doing this ... however it does work.

y021023h1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
4	DOCUMENTO	CODICE	KG	LIRE	TOTALE
5	1	12	2	10000	20000			sourcedata
6	1	5	10	12000	24000			inputvalues
7	1	10	5	11000	55000			formulasformanipulatingsourcedata
8	2	2	6	5500	33000
9	2	3	9	1000	9000
10
11	DOCUMENTO	CODICE	KG	LIRE	TOTALE	DOCUMENTO	CODICE	KG	LIRE	TOTALE	DOCUMENTO	CODICE	KG	LIRE	TOTALE
12	1	12	2	10000	20000	1	5	10	12000	24000	1	10	5	11000	55000
13	2	2	6	5500	33000	2	3	9	1000	9000
Sheet12c

</SPAN>

Please post back whether it works for you ... otherwise explain a little further and let us take it from there.

Regards!

Yogi Anand

 

[Maurizio]

I have this code, but I don't want always heading:

Sub Rapporto()
Dim Dati As Range, NumRec As Integer, Nr As Integer
Foglio2.UsedRange.ClearContents
With Foglio1.Range("IniDb").CurrentRegion
.Sort Range("IniDb").Cells(2, 1), Header:=xlGuess
NumRec = .Rows.Count - 1
Set Dati = Range("IniDB").Offset(1, 1).Resize(NumRec, .Columns.Count - 1)
End With
Dim i As Integer, j As Integer, k As Integer, _
IniRep As Range, Intestaz As Range, Nc As Integer
i = 1: j = 1: k = 2
Set IniRep = Range("IniRep")
With Range("IniDb")
Set Intestaz = Range(.Offset(0, 1), .End(xlToRight))
End With
Nc = Intestaz.Columns.Count
While i <= NumRec
Range("IniDb").Copy IniRep(j, 1)
IniRep(j + 1, 1) = Dati(i, 0)
Intestaz.Copy IniRep(j, 2)
Dati.Rows(i).Copy IniRep(j + 1, 2)
While Dati(i + 1, 0) = Dati(i, 0)
i = i + 1: k = k + Nc
Intestaz.Copy IniRep(j, k)
Dati.Rows(i).Copy IniRep(j + 1, k)
Wend
i = i + 1: j = j + 2: k = 2
Wend
Foglio2.Activate
End Sub