# Data Trasposition

#### Maurizio

##### Well-known Member
Cartel1
ABCDEFGHIJKLM
1HowcanIgetthistrasposition?
2
3Before:
4DOCUMENTOCODICEKGLIRETOTALE
511221000020000
615101200024000
711051100055000
8226550033000
923910009000
10After:
11DOCUMENTOCODKGLIRETOTALECODKGLIRETOTALECODKGLIRETOTALE
121122100002000051012000240001051100055000
Foglio1

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Maurizio

##### Well-known Member
Sorry, so edited:
Cartel1
ABCDEFGHIJKLM
1HowcanIgetthistrasposition?
2
3Before:
4DOCUMENTOCODICEKGLIRETOTALE
511221000020000
615101200024000
711051100055000
8226550033000
923910009000
10After:
11DOCUMENTOCODKGLIRETOTALECODKGLIRETOTALECODKGLIRETOTALE
121122100002000051012000240001051100055000
132265500330003910009000
14
Foglio1

#### maxflia10

##### Well-known Member
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
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
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

Replies
7
Views
286
Replies
4
Views
525
Replies
3
Views
174
Replies
8
Views
233
Replies
10
Views
267

1,181,241
Messages
5,928,843
Members
436,632
Latest member
DrCSdeLange

### 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?

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