Transpose equal segments of rows into piled columns

O

occitan

New Member
Joined
Nov 15, 2017
Messages
15
Hello everybody! :)

I woud kindly like to ask you a question on transposing segments of rows into piled columns. I looked for similar cases on the forum but I didn't find any :/

Many thanks to whoever would like to help me!

So, I have to create a macro which enables to transpose different (equal) segments of rows into columns in this particular fashion:


A1 A1 A1 A1 B1 B1 B1 B1 C1 C1 C1 C1 D1 D1 D1 D1 E1 E1 E1 E1 ... (first row)
A2 A2 A2 A2 B2 B2 B2 B2 C2 C2 C2 C2 D2 D2 D2 D2 E2 E2 E2 E2 ... (second row)
A3 A3 A3 A3 B3 B3 B3 B3 C3 C3 C3 C3 D3 D3 D3 D3 E3 E3 E3 E3 ... (third row)
...

Into:

A1 B1 C1 D1 E1 ...
A1 B1 C1 D1 E1 ...
A1 B1 C1 D1 E1 ...
A1 B1 C1 D1 E1 ...
A2 B2 C2 D2 E2 ...
A2 B2 C2 D2 E2 ...
A2 B2 C2 D2 E2 ...
A2 B2 C2 D2 E2 ...
A3 B3 C3 D3 E3 ...
A3 B3 C3 D3 E3 ...
A3 B3 C3 D3 E3 ...
A3 B3 C3 D3 E3 ...
... ... ... ... ... ...


I simplified this example, in order to make it clear. In this example, each row consists of 5 elements (A, B, C, D, E -cell values-) which are taken in a group of 4, and have to be transposed into piled columns. In my case, I have 14 elements in each row paired in groups of 133 (instead of 4 in this example).

I tried several attempts in writing macros but I didn't manage to obtain this :/ Also, as I said, I wasn't able to find a solution already existing in the forum.

Many thanks to whoever will be so kind to help me.

O
 
Sort by date Sort by votes
I'm afraid you've completely lost me, when you say
using the code kindly provided me by Fluff, but instead of 1862 inserting 7000, it is possible to derive the correct transposition for (only) the first ID (all the others are not displayed. The values for the second ID are displayed but with missing values).
Click to expand...
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Using Fluff's code correctly provides the right transposition, but only for the first ID (row). I must insert below the correct transposition for all the remaining 998 IDs..

I am currently trying some codes but I can't find the right ones!

I am trying with "Offset(n)" and different combinations but it doesn't work :/

O


occitan said:
Dear Fluff, thank you very much for your kind help. The macro is not correct yet, but almost close to it!

Also many thanks to Mick.

Thank you very much for the kind help you are providing me, guys.

I apologize to Mick, if I was a little bit blurried. I just wanted to say that there is no gap between the values in each row (the "|" I insterted above was solely aimed at detecting the different paired groups).

Also, I have a total of 999 rows (999 IDs), each row having 14 groups (the segments), each group having 133 observations (so a total of 14x13=1862 values per row), so the total matrix in the main spreadsheet is 999x1862.

The final matrix should be a 132867x14 matrix (where 133x999=132867)

using the code kindly provided me by Fluff, but instead of 1862 inserting 7000, it is possible to derive the correct transposition for (only) the first ID (all the others are not displayed. The values for the second ID are displayed but with missing values).

I think that just a few arrangements to the Fluff's code are necessary to obtain the correct macro. I have been trying some codes but still don't get it :/

I apologize for bothering you with this, just it is driving me mad!
Click to expand...
 
Upvote 0
No Fluff, your code was 100% correct! Many many thanks for that :)

Just, I need a few more arrangements in order to obtain the transposition for all the other (remaining) 998 rows.

As I said the spreadsheet has 999 rows, each rows having 14x133=1862 column values, which have to be transposed in the fashion I exposed. Fashion which your last code you kindly provided me displayed 100% correctly, but just for the first row (indeed, it provided a 133x14 matrix). What I would need is a final (133x999) x 14 matrix
 
Upvote 0
If you start with

Excel 2013 32 bit
ABC
151a11a21a3
162a12a22a3
173a13a23a3
MA (2)

My code will give

Excel 2013 32 bit
A
201a1
211a2
221a3
232a1
242a2
252a3
263a1
273a2
283a3
MA (2)

This is just for the first set of elements. Is this not what you want?
 
Upvote 0
Hi Fluff!

Yes, precisely :)

Specifically, I have to obtain a final panel data, composed of 999 IDs displaying 133 observations over time (133 years) for 14 variables, that is a total of 999x133= 132867 rows (i=999, t=133) over 14 variables.

The table you posted above is correct. Imagine 15,16,17 being three different rows (IDs), and columns A,B,C displaying the evolution of, let's say GDP, over a period of three years (so 1a1=GDP of ID1 in year 1, 3a3=GDP of ID3 in year 3).

The second table correctly transposes table 1 in a panel data setting, so that you have

1a1 (ID=1, year=1)
1a2 (ID=1, year=2)
1a2 (ID=1, year=3)
2a1 (ID=2, year=1)
2a2 (ID=2, year=2)
2a3 (ID=2, year=3)
3a1 (ID=3, year=1)
3a2 (ID=3, year=2)
3a3 (ID=4, year=3)


In my case, my spreadsheet is the following:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]ID[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]A2[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]A133[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]B133[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]P1[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]P133[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1a1[/TD]
[TD="align: center"]1a2[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]1a133[/TD]
[TD="align: center"]1b134[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]1b266[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]1p1729[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]1p1862[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2a1[/TD]
[TD="align: center"]2a2[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]2a133[/TD]
[TD="align: center"]2b134[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]2b266[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]2p1729[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]2p1862[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]3a1[/TD]
[TD="align: center"]3a2[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]3a133[/TD]
[TD="align: center"]3b134[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]3b266[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]3p1729[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]3p1862[/TD]
[/TR]
[TR]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]999[/TD]
[TD="align: center"]999a1[/TD]
[TD="align: center"]999a2[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]999a133[/TD]
[TD="align: center"]999b134[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]999b266[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]999p1729[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]999p1862[/TD]
[/TR]
</tbody>[/TABLE]



Which I have to transpose in the following format:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]row[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]...[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1a1[/TD]
[TD]1b134[/TD]
[TD]...[/TD]
[TD]1p1729[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1a2[/TD]
[TD]1b135[/TD]
[TD]...[/TD]
[TD]1p1730[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...
[/TD]
[/TR]
[TR]
[TD]133[/TD]
[TD]1a133[/TD]
[TD]1b266[/TD]
[TD]...[/TD]
[TD]1p1862[/TD]
[/TR]
[TR]
[TD]134[/TD]
[TD]2a1[/TD]
[TD]2b134[/TD]
[TD]...[/TD]
[TD]2p1729[/TD]
[/TR]
[TR]
[TD]135[/TD]
[TD]2a2[/TD]
[TD]2b135[/TD]
[TD]...[/TD]
[TD]2p1730[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]132735[/TD]
[TD]999a1[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]999p1729[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]999a2[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]999p1730[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]132867[/TD]
[TD]999a133[/TD]
[TD]999b266[/TD]
[TD]...[/TD]
[TD]999p1862[/TD]
[/TR]
</tbody>[/TABLE]


Before running the macro, I delete the label rows so that in A1 I start directly with 1a1 (so in my spreadhseet I ONLY have values, from 1a1 to 999p1862, and no labels).

Your former code correctly provides the transposition as in table 2 above with regard to ID1 (thus providing a 133x14 matrix), but all the other 998 IDs are not shown.

I tried myself to modify your code to derive the correct final table, but so far I didn't manage :/

If you could kindly help me with this I would be really, extremely grateful!

Many many thanks!

O.
 
Last edited:
Upvote 0
How about
Code: 
Sub Trans()

    Dim Cols As Long
    Dim Rws As Long
    Dim clmn As Long
    
    clmn = 2
    For Cols = 1 To 1862 Step 133
        For Rws = 1 To Range("A1").CurrentRegion.Rows.Count
            Sheets("sheet3").Cells(Rows.Count, clmn).End(xlUp).Offset(1).Resize(133).Value = Application.Transpose(Cells(Rws, Cols).Resize(, 133).Value)
        Next Rws
        clmn = clmn + 1
    Next Cols
    With Sheets("Sheet3")
        .Rows(1).Delete
        .Range("A1").Value = 1
        .Range("A1").AutoFill .Range("A1:A132867"), xlFillSeries
    End With

End Sub
 
Upvote 0
Dear Fluff,

thank you a lot for your code. However, I still got the (correct) transposition only for the first ID, all the other 998 are still not displayed :/
 
Upvote 0
Do you have any blank rows or columns in your original data?
 
Upvote 0
Ok, try this
Code: 
Sub Trans()

    Dim Cols As Long
    Dim Rws As Long
    Dim clmn As Long
    
    clmn = 2
    For Cols = 1 To 1862 Step 133
        For Rws = 1 To Range("A" & Rows.Count).End(xlUp).Row
            Sheets("sheet3").Cells(Rows.Count, clmn).End(xlUp).Offset(1).Resize(133).Value = Application.Transpose(Cells(Rws, Cols).Resize(, 133).Value)
        Next Rws
        clmn = clmn + 1
    Next Cols
    With Sheets("Sheet3")
        .Rows(1).Delete
        .Range("A1").Value = 1
        .Range("A1").AutoFill .Range("A1:A132867"), xlFillSeries
    End With

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Macro to print a table filling from a sheet then move to the next row on the sheet
Replies
3
Views
177
Peter_SSs
Peter_SSs
A
  • Question Question
Excel formula please
Replies
4
Views
112
JoeMo
J
S
  • Question Question
Function to repeatedly replace string one-at-a-time via a lookup table
Replies
4
Views
456
smallxyz
S
K
  • Question Question
Employee Attendance Tracker Template.
Replies
3
Views
223
6StringJazzer
6StringJazzer
P
  • Question Question
Simple-interest debt tracker, w/o terms/monthlies
Replies
0
Views
149
pitriDish
P

Forum statistics

Threads
1,224,606
Messages
6,179,862
Members
452,948
Latest member
UsmanAli786

Share this page

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
Back
Top