Transpose columns contain in to row

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
Hello,

I have long data in columns A, B, C, D, spacing in between set of 14 in 14 I need to transpose all till end...

Column B data 14 in 14 to column F:S
Column C data 14 in 14 to column V:AI
Column D data 14 in 14 to column AL:AY

Example Data:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZ
1JRABCP1P2P3P4P5P6P7P8P9P10P11P12P13P14P1P2P3P4P5P6P7P8P9P10P11P12P13P14P1P2P3P4P5P6P7P8P9P10P11P12P13P14
2P11X211X11211121111XX1XXXXXXXXXXX2222212221222X
3P21X21X11X11X1111XXX1XX1XX1XXXX112122222X212221
4P3X12111XX21121X1X1XXX11XXX1X1X1X2212X122X22222
5P41X21XXX11X11X111XX111XX1XX1XXX1X2222221221X22
6P51X2111X21X2111X11XXX1XX1XXXX1XX2122122122X2X2
7P62X1
8P71X2
9P81X2
10P91X2
11P102X1
12P111X2
13P121X2
14P131X2
15P141XX
16
17P11X2
18P2X11
19P31X2
20P41X2
21P5X12
22P61X2
23P71X2
24P8X1X
25P91X2
26P101X1
27P111X2
28P121X2
29P13X12
30P14X11
31
32P11X2
33P21X2
34P31X1
35P4X12
36P5X1X
37P62X1
38P71X2
39P81X2
40P921X
41P101X2
42P11X12
43P121X2
44P13X12
45P141X2
46
47P11XX
48P2X12
49P3X12
50P4X12
51P51X2
52P61X2
53P7X12
54P81X1
55P91X2
56P10X12
57P111X1
58P121XX
59P131X2
60P14X12
61
62P11X2
63P21X1
64P31X2
65P4X12
66P52X1
67P61X2
68P7X12
69P82X1
70P91X2
71P101X2
72P111XX
73P12X12
74P131XX
75P141X2
76
Sheet6


Is it possible to transpose all at once?

Thank you all
Excel 2000
Regards,
Moti
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Try this:-
Not quite all at once, but quite quickly !!!

Code: 
[COLOR="Navy"]Sub[/COLOR] MG09Jul18
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
 col = 0
[COLOR="Navy"]For[/COLOR] ac = 0 To 2
    c = 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Rng.Offset(, ac).Areas
        c = c + 1
        R.Copy
        Cells(c, "F").Offset(, col).PasteSpecial Transpose:=True
    [COLOR="Navy"]Next[/COLOR] R
col = col + 16
[COLOR="Navy"]Next[/COLOR] ac
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
MickG said:
Try this:-
Not quite all at once, but quite quickly !!!

Code: 
[COLOR=navy]Sub[/COLOR] MG09Jul18
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, R [COLOR=navy]As[/COLOR] Range, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] col [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
 col = 0
[COLOR=navy]For[/COLOR] ac = 0 To 2
    c = 1
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] R [COLOR=navy]In[/COLOR] Rng.Offset(, ac).Areas
        c = c + 1
        R.Copy
        Cells(c, "F").Offset(, col).PasteSpecial Transpose:=True
    [COLOR=navy]Next[/COLOR] R
col = col + 16
[COLOR=navy]Next[/COLOR] ac
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...
Speechless MickG, Amazing!! All done in a seconds SOLVED!! :)

Thank you I appreciate your support and time you spend to solve all the complicate tasks

Have a good day

Kind Regards
Moti
 
Last edited:
Upvote 0
Can you use something like this? I worked with a much smaller sample of your data. Here is the formula: [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$D$1,ROW($B$2:$B$16)-ROW($B$2)+1),ROWS($D$2:D2)))," ") Below is what my results looked like. You can copy the formula accross, but you will need to change criteria and cells for the rows part of the formula. So I just did the data for A only.
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]P1[/TD]
[TD="width: 64"]P2[/TD]
[TD="width: 64"]P3[/TD]
[/TR]
[TR]
[TD]P1[/TD]
[TD="class: xl65"]1[/TD]
[TD][/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD="class: xl65"]1[/TD]
[TD][/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD="class: xl65"]1[/TD]
[TD][/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]8[/TD]
[/TR]
[TR]
[TD]P1[/TD]
[TD="class: xl65"]2[/TD]
[TD][/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD="class: xl65"]3[/TD]
[TD][/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD="class: xl65"]2[/TD]
[TD][/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD]P1[/TD]
[TD="class: xl65"]3[/TD]
[TD][/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD="class: xl65"]4[/TD]
[TD][/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD="class: xl65"]6[/TD]
[TD][/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD]P1[/TD]
[TD="class: xl65"]10[/TD]
[TD][/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD="class: xl65"]7[/TD]
[TD][/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD="class: xl65"]8[/TD]
[TD][/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Copying Rows Based on Column Value
Replies
1
Views
150
jolivanes
jolivanes
D
  • Solved
Identifying Groups of Data in a Column, Transposing Groups to a Row
Replies
3
Views
139
KRice
K
C
  • Question Question
Transpose multiple columns to a single column
Replies
1
Views
111
Fluff
Fluff
T
  • Solved
Get second last row?
Replies
4
Views
209
TheWennerWoman
T
N
  • Solved
Help with setting up a VBA
2 3
Replies
20
Views
634
Alex Blakenburg
A

Forum statistics

Threads
1,224,537
Messages
6,179,408
Members
452,912
Latest member
alicemil

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