Data Trasposition

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
682
Office Version
  1. 2007
Platform
  1. Windows
Cartel1
ABCDEFGHIJKLM
1HowcanIgetthistrasposition?
2
3Before:
4DOCUMENTOCODICEKGLIRETOTALE
511221000020000
615101200024000
711051100055000
8226550033000
923910009000
10After:
11DOCUMENTOCODKGLIRETOTALECODKGLIRETOTALECODKGLIRETOTALE
121122100002000051012000240001051100055000
Foglio1
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
682
Office Version
  1. 2007
Platform
  1. Windows
Sorry, so edited:
Cartel1
ABCDEFGHIJKLM
1HowcanIgetthistrasposition?
2
3Before:
4DOCUMENTOCODICEKGLIRETOTALE
511221000020000
615101200024000
711051100055000
8226550033000
923910009000
10After:
11DOCUMENTOCODKGLIRETOTALECODKGLIRETOTALECODKGLIRETOTALE
121122100002000051012000240001051100055000
132265500330003910009000
14
Foglio1
 

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCDEFGHIJKLMNO
4DOCUMENTOCODICEKGLIRETOTALE
511221000020000sourcedata
615101200024000inputvalues
711051100055000formulasformanipulatingsourcedata
8226550033000
923910009000
10
11DOCUMENTOCODICEKGLIRETOTALEDOCUMENTOCODICEKGLIRETOTALEDOCUMENTOCODICEKGLIRETOTALE
12112210000200001510120002400011051100055000
1322655003300023910009000 
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

Well-known Member
Joined
Oct 15, 2002
Messages
682
Office Version
  1. 2007
Platform
  1. Windows
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
 

Forum statistics

Threads
1,144,056
Messages
5,722,261
Members
422,419
Latest member
Havok390

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top