Data manipulation

[ryan8200]

Here is my data

		1	2	3	4	5	6	7	8	9
000	10	20	30	40	50	60	70	80	90	100
001	110	120	130	140	150	160	170	180	190	200
002	210	220	230	240	250	260	270	280	290	300

<tbody>
</tbody>

I would like to display the data in the following format . Is that possible to do it in Excel ?

0000	10
0001	20
0002	30
0003	40
0004	50
0005	60
0006	70
0007	80
0008	90
0009	100
0010	110
0011	120
0012	130
0013	140
0014	150
0015	160
0016	170
0017	180
0018	190
0019	200
0020	210
0021	220
0022	230
0023	240
0024	250
0025	260
0026	270
0027	280
0028	290
0029	300

<tbody>
</tbody>

 

[venkat1926]

data is like this. 0 is added to B1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1		0	1	2	3	4	5	6	7	8	9
2	000	10	20	30	40	50	60	70	80	90	100
3	001	110	120	130	140	150	160	170	180	190	200
4	002	210	220	230	240	250	260	270	280	290	300
Sheet1

try thils macro and see columns P and Q

Sub test()
    Dim r As Range, c As Range
    Dim dest As Range
    Range("P1:Q1").EntireColumn.Delete
    Set r = Range(Range("B2"), Range("K4"))
    For Each c In r
        Set dest = Cells(Rows.Count, "P").End(xlUp).Offset(1, 0)


        dest = "'" & Cells(c.Row, 1) & Cells(1, c.Column)


        ' MsgBox dest
        dest.Offset(0, 1) = c
    Next c


End Sub

FEEDBACK

 

[venkat1926]

DEFN OF r in macro may be modified to suit your file

 

[ryan8200]

data is like this. 0 is added to B1

Sheet1

*	A	B	C	D	E	F	G	H	I	J	K
1	*	0	1	2	3	4	5	6	7	8	9
2	000	10	20	30	40	50	60	70	80	90	100
3	001	110	120	130	140	150	160	170	180	190	200
4	002	210	220	230	240	250	260	270	280	290	300

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

try thils macro and see columns P and Q

Sub test()
    Dim r As Range, c As Range
    Dim dest As Range
    Range("P1:Q1").EntireColumn.Delete
    Set r = Range(Range("B2"), Range("K4"))
    For Each c In r
        Set dest = Cells(Rows.Count, "P").End(xlUp).Offset(1, 0)


        dest = "'" & Cells(c.Row, 1) & Cells(1, c.Column)


        ' MsgBox dest
        dest.Offset(0, 1) = c
    Next c


End Sub

FEEDBACK

Thanks for the code. Can share with me how to master in VBA and Macro ?

 

[venkat1926]

use vba help pages, groupslike this newsgroup(see replies for all type of queries not nercessrily oly yours) .of course you get a book

Excel Books by Category get a book for your versio of excel . go on doing from small experiments to more complex