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.

[TABLE="width: 299"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]g[/TD]
[TD]l[/TD]
[TD]m[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]PT-55002[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]135[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]PT-55002[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]135[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]AIT-98932[/TD]
[TD="align: right"]-200[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]FT-98140[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]LT-36001[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]FQIT-36003[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7000[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]LT-37201[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]LT-39001[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]LT-41005[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]LT-38002[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]AIT-37705[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]AIT-39510[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 960"]
<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]g1[/TD]
[TD="width: 64"]l1[/TD]
[TD="width: 64"]m1[/TD]
[TD="width: 64"]g2[/TD]
[TD="width: 64"]l2 [/TD]
[TD="width: 64"]m2[/TD]
[TD="width: 64"]g3 [/TD]
[TD="width: 64"]l3[/TD]
[TD="width: 64"]m3[/TD]
[TD="width: 64"]g4[/TD]
[TD="width: 64"]l4[/TD]
[TD="width: 64"]m4[/TD]
[TD="width: 64"]g5 [/TD]
[TD="width: 64"]l5[/TD]
[TD="width: 64"]m5[/TD]
[/TR]
[TR]
[TD]PT-55002[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]135[/TD]
[TD]PT-55002[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]135[/TD]
[TD]AIT-98932[/TD]
[TD="align: right"]-200[/TD]
[TD="align: right"]150[/TD]
[TD]FT-98140[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LT-36001[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD]FQIT-36003[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7000[/TD]
[TD]LT-37201[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]110[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LT-39001[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]110[/TD]
[TD]LT-41005[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD]LT-38002[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD]AIT-37705[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD]AIT-39510[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]

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:-

[TABLE="width: 230"]
<colgroup><col width="62" style="width: 46pt;" span="5"> <tbody>[TR]
[TD="class: xl64, width: 62, bgcolor: #CCFFFF"] [/TD]
[TD="class: xl64, width: 62, bgcolor: #CCFFFF"]A[/TD]
[TD="class: xl64, width: 62, bgcolor: #CCFFFF"]B[/TD]
[TD="class: xl64, width: 62, bgcolor: #CCFFFF"]C[/TD]
[TD="class: xl64, width: 62, bgcolor: #CCFFFF"]D[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #CCFFFF, align: right"]1[/TD]
[TD="class: xl65, width: 62, bgcolor: white"] [/TD]
[TD="class: xl65, width: 62, bgcolor: white"]g[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]l[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]m[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #CCFFFF, align: right"]2[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]PT-55002[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]0[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]135[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #CCFFFF, align: right"]3[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]2[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]PT-55002[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]0[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]135[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #CCFFFF, align: right"]4[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]3[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]AIT-98932[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]-200[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]150[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #CCFFFF, align: right"]5[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]4[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]FT-98140[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]0[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]50[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #CCFFFF, align: right"]6[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]LT-36001[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]0[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]100[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #CCFFFF, align: right"]7[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]2[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]FQIT-36003[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]0[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]7000[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #CCFFFF, align: right"]8[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]3[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]LT-37201[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]0[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]110[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #CCFFFF, align: right"]9[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]LT-39001[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]0[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]110[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #CCFFFF, align: right"]10[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]2[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]LT-41005[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]0[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]100[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #CCFFFF, align: right"]11[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]3[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]LT-38002[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]0[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]100[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #CCFFFF, align: right"]12[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]4[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]AIT-37705[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]0[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]20[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #CCFFFF, align: right"]13[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]5[/TD]
[TD="class: xl65, width: 62, bgcolor: white"]AIT-
39510[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]0[/TD]
[TD="class: xl66, width: 62, bgcolor: white"]100
[/TD]
[/TR]
</tbody>[/TABLE]