Transpose columns contain in to row

[motilulla]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ
1	JR	A	B	C		P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14			P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
2	P1	1	X	2		1	1	X	1	1	2	1	1	1	2	1	1	1	1			X	X	1	X	X	X	X	X	X	X	X	X	X	X			2	2	2	2	2	1	2	2	2	1	2	2	2	X
3	P2	1	X	2		1	X	1	1	X	1	1	X	1	1	1	1	X	X			X	1	X	X	1	X	X	1	X	X	X	X	1	1			2	1	2	2	2	2	2	X	2	1	2	2	2	1
4	P3	X	1	2		1	1	1	X	X	2	1	1	2	1	X	1	X	1			X	X	X	1	1	X	X	X	1	X	1	X	1	X			2	2	1	2	X	1	2	2	X	2	2	2	2	2
5	P4	1	X	2		1	X	X	X	1	1	X	1	1	X	1	1	1	X			X	1	1	1	X	X	1	X	X	1	X	X	X	1			X	2	2	2	2	2	2	1	2	2	1	X	2	2
6	P5	1	X	2		1	1	1	X	2	1	X	2	1	1	1	X	1	1			X	X	X	1	X	X	1	X	X	X	X	1	X	X			2	1	2	2	1	2	2	1	2	2	X	2	X	2
7	P6	2	X	1
8	P7	1	X	2
9	P8	1	X	2
10	P9	1	X	2
11	P10	2	X	1
12	P11	1	X	2
13	P12	1	X	2
14	P13	1	X	2
15	P14	1	X	X
16
17	P1	1	X	2
18	P2	X	1	1
19	P3	1	X	2
20	P4	1	X	2
21	P5	X	1	2
22	P6	1	X	2
23	P7	1	X	2
24	P8	X	1	X
25	P9	1	X	2
26	P10	1	X	1
27	P11	1	X	2
28	P12	1	X	2
29	P13	X	1	2
30	P14	X	1	1
31
32	P1	1	X	2
33	P2	1	X	2
34	P3	1	X	1
35	P4	X	1	2
36	P5	X	1	X
37	P6	2	X	1
38	P7	1	X	2
39	P8	1	X	2
40	P9	2	1	X
41	P10	1	X	2
42	P11	X	1	2
43	P12	1	X	2
44	P13	X	1	2
45	P14	1	X	2
46
47	P1	1	X	X
48	P2	X	1	2
49	P3	X	1	2
50	P4	X	1	2
51	P5	1	X	2
52	P6	1	X	2
53	P7	X	1	2
54	P8	1	X	1
55	P9	1	X	2
56	P10	X	1	2
57	P11	1	X	1
58	P12	1	X	X
59	P13	1	X	2
60	P14	X	1	2
61
62	P1	1	X	2
63	P2	1	X	1
64	P3	1	X	2
65	P4	X	1	2
66	P5	2	X	1
67	P6	1	X	2
68	P7	X	1	2
69	P8	2	X	1
70	P9	1	X	2
71	P10	1	X	2
72	P11	1	X	X
73	P12	X	1	2
74	P13	1	X	X
75	P14	1	X	2
76
Sheet6

Is it possible to transpose all at once?

Thank you all
Excel 2000
Regards,
Moti

 

[MickG]

Try this:-
Not quite all at once, but quite quickly !!!

[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

 

[motilulla]

Try this:-
Not quite all at once, but quite quickly !!!

[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

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

 

[MickG]

You're most welcome
Regrds Mick

 

[Mike Szczesny]

Can you use something like this? I worked with a much smaller sample of your data. Here is the formula:

=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. A P1 P2 P3 P1 1 1 1 1 P2 1 2 3 2 P3 1 3 4 6 10 7 8 P1 2 P2 3 P3 2 P1 3 P2 4 P3 6 P1 10 P2 7 P3 8 <colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody> </tbody>

<tbody>
</tbody>