Transform and tabulate rows of data in specified format

[naveenraja16]

I am wanted to simplify the task of transforming 1000's of data in rows into a table with specified column headings. I do manual copy paste of data which is tedious.

	g	l	m
1	PT-55002	0	135
2	PT-55002	0	135
3	AIT-98932	-200	150
4	FT-98140	0	50
1	LT-36001	0	100
2	FQIT-36003	0	7000
3	LT-37201	0	110
1	LT-39001	0	110
2	LT-41005	0	100
3	LT-38002	0	100
4	AIT-37705	0	20
5	AIT-39510	0	100

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

g1	l1	m1	g2	l2	m2	g3	l3	m3	g4	l4	m4	g5	l5	m5
PT-55002	0	135	PT-55002	0	135	AIT-98932	-200	150	FT-98140	0	50
LT-36001	0	100	FQIT-36003	0	7000	LT-37201	0	110
LT-39001	0	110	LT-41005	0	100	LT-38002	0	100	AIT-37705	0	20	AIT-39510	0	100

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

with reference to the sample data available in the attached image , The data available in the bounded table on the left has to be tabulated under the headings g1 to m5 respectively. The bounded data with a thick border represents one set . That has to tabulated in the first row. Similarly the second set has to be populated in second row. The sets can be differentiated by referring the numbering of rows . every set begins with number 1 .

Can this forum help me in doing this task through a vba code.

 

[MickG]

Try this code for Results on sheet2 Starting "A1"

[COLOR="Navy"]Sub[/COLOR] MG15Jul38
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] St [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Range("A1").CurrentRegion.Resize(, 4)
ReDim nray(1 To UBound(Ray, 1), 1 To 15)
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]If[/COLOR] Ray(n, 1) = 1 [COLOR="Navy"]Then[/COLOR]
        c = c + 1: Col = 0
    [COLOR="Navy"]End[/COLOR] If
     [COLOR="Navy"]For[/COLOR] Ac = 2 To 4
       Col = Col + 1
       nray(1, Col) = Ray(1, Ac) & Ray(n, 1)
       nray(c, Col) = Ray(n, Ac)
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 15)
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
 [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Code Based on data as below in Active sheet:-

	A	B	C	D
1		g	l	m
2	1	PT-55002	0	135
3	2	PT-55002	0	135
4	3	AIT-98932	-200	150
5	4	FT-98140	0	50
6	1	LT-36001	0	100
7	2	FQIT-36003	0	7000
8	3	LT-37201	0	110
9	1	LT-39001	0	110
10	2	LT-41005	0	100
11	3	LT-38002	0	100
12	4	AIT-37705	0	20
13	5	AIT- 39510	0	100

<colgroup><col width="62" style="width: 46pt;" span="5"> <tbody> </tbody>