How to transpose the data vertically

[akhendre88]

Hello All,

Can we transpose the data vertically. I have some data in below format.

Emp Id	Access Id	Roles
A001	abcd	READ	WRITE	DELETE

<tbody>
</tbody>

So is there any way so that I align the data in 'Roles' column like

Emp Id	Access Id	Roles
A001	abcd	READ
		WRITE
		DELETE

<tbody>
</tbody>

I was trying this,

sht.Range("C" & i) = WorksheetFunction.Transpose(.Range("C" & i))sht.Range("C" & i) = WorksheetFunction.Transpose(.Range("C" & i))

but the output was only first value i.e. READ.

Please suggest.

Thank you

 

[Peter_SSs]

Excellent Point Peter. I have changed up the code as follows:

Cheers.
An alternative would have been just to keep referencing all relevant cells etc to that worksheet, as you had started to do.

    With s1
        
        .Range("A1:C1").Copy s2.Range("A1")
        r1 = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 2 To r1
            r2 = s2.Range("C" & Rows.Count).End(xlUp).Row
            .Range("A" & i & ":B" & i).Copy s2.Range("A" & r2 + 1)
            lc = [COLOR="#FF0000"][B][SIZE=5].[/SIZE][/B][/COLOR]Cells(i, [COLOR="#FF0000"][B][SIZE=5].[/SIZE][/B][/COLOR]Columns.Count).End(xlToLeft).Column
            .Range([COLOR="#FF0000"][SIZE=5][B].[/B][/SIZE][/COLOR]Cells(i, 3), [COLOR="#FF0000"][B][SIZE=5].[/SIZE][/B][/COLOR]Cells(i, lc)).Copy
            s2.Range("C" & r2 + 1).PasteSpecial xlPasteAll, , , True
            Application.CutCopyMode = False
        Next i
    End With

 

[akhendre88]

@alansidman & @Peter_SSs thank you very much for your helphttp://www.mrexcel.com/forum/members/peter_sss.html.

 

[Baydoun]

can i make the following transpose :
THE ORIGIN :

	A	B	C	D	E	F	G	H	I	J	K
					sizes
1	ITEM NO	DESCRIPTION	COLOR	U/PRICE	35	36	37	38	39	40	41
2	Q8-8940-2	WOMEN	SILVER	US$10.00		1	2	3	4	5	6
3		WOMEN	GOLD	US$10.00		7	8	9	10	11	12
4	QS-2307-32	MEN	WHITE	US$6.70		13	14	15	16	17	18
5		MEN	BLACK	US$6.70		19	20	21	22	23	24
6		MEN	GOLD	US$6.70		25	26	27	28	29	30
7	270#	KIDS	BLACK	US$10.30		31	32	33	34	35	36
8		KIDS	PINK	US$10.30		37	38	39	40	41	42
9	M0-1116-1039	UNISEX	BEIGE	US$5.80	43	44	45	46	47	48
10		UNISEX	PINK	US$5.80	49	50	51	52	53	54

<tbody>
</tbody>

TO BECOME LIKE THIS :

	M	N	O	P	Q	R
1	ITEM NO	DESCRIPTION	COLOR	U/PRICE	sizes
2	Q8-8940-2	WOMEN	SILVER	US$10.00	35
3					36	1
4					37	2
5					38	3
6					39	4
7					40	5
8					41	6
9	Q8-8940-2	WOMEN	GOLD	US$10.00	35
10					36	7
11					37	8
12					38	9
13					39	10
14					40	11
15					41	12
16	QS-2307-32	MEN	WHITE	US$6.70	35
17					36	13
18					37	14
19					38	15
20					39	16
21					40	17
22					41	18
23	QS-2307-32	MEN	BLACK	US$6.70	35
24					36	19
25					37	20
26					38	21
27					39	22
28					40	23
29					41	24
30	QS-2307-32	MEN	GOLD	US$6.70	35
31					36	25
32					37	26
33					38	27
34					39	28
35					40	29
36					41	30
37	270#	KIDS	BLACK	US$10.30	35
38					36	31
39					37	32
40					38	33
41					39	34
42					40	35
43					41	36
44	270#	KIDS	PINK	US$10.30	35
45					36	37
46					37	38
47					38	39
48					39	40
49					40	41
50					41	42
51	M0-1116-1039	UNISEX	BEIGE	US$5.80	35	43
52					36	44
53					37	45
54					38	46
55					39	47
56					40	48
57					41
58	M0-1116-1039	UNISEX	PINK	US$5.80	35	49
59					36	50
60					37	51
61					38	52
62					39	53
63					40	54
64					41

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Baydoun]

And is it possible to fill the blank cells with the same upper values ?

 

[Baydoun]

shall i open a new thread since i'm asking a new question ? or i shall only wait for the answer to my questions above ?
looking forward to your answers ,
thanks

 

[Peter_SSs]

shall i open a new thread since i'm asking a new question ?

You would do one or the other, not both. Since you have already asked the question here, this is where it stays. Please also read my Private Message about this issue.

looking forward to your answers ,
thanks

Your requirement is not clear. You have asked to how to fill the blank cells with what is above. In that case what values do you expect to see in cells R1, R2, R9 etc?
My guess is nothing, but that is not what your question implies & it is up to you to clarify exactly what you want.

 

[Baydoun]

thanks for your answer peter ,
then no need to fill in the blanks with upper cells ,
i shall need only the transpose ,
looking forward to your answer ,
thanks beforehand

 

[Peter_SSs]

Try this on a copy of your data.

Sub Rearrange_Data()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, NumSizes As Long
  Dim ItmNo As String
  
  a = Range("A1:K" & Range("B" & Rows.Count).End(xlUp).Row).Value
  NumSizes = UBound(a, 2) - 4
  k = 1 - NumSizes
  ReDim b(1 To UBound(a, 1) * NumSizes, 1 To 6)
  For i = 2 To UBound(a)
    If a(i, 1) <> "" Then ItmNo = a(i, 1)
    k = k + NumSizes
    b(k, 1) = ItmNo
    For j = 2 To 4
      b(k, j) = a(i, j)
    Next j
    For j = 0 To NumSizes - 1
      b(k + j, 5) = a(1, 5 + j)
      b(k + j, 6) = a(i, 5 + j)
    Next j
  Next i
  With Range("M1:R1")
    .Value = Array("ITEM NO", "DESCRIPTION", "COLOR", "U/PRICE", "sizes", "")
    .Offset(1).Resize(UBound(b)).Value = b
    .EntireColumn.AutoFit
  End With
End Sub

 

[Baydoun]

Thanks peter , works greatly