Combine multiple rows into one and then seprate specific columns

A

ahad_bwp

New Member
Joined
Sep 25, 2012
Messages
4
[TABLE="class: outer_border, width: 924, align: center"]
<tbody>[TR]
[TD]Sr.#
[/TD]
[TD]Account ID/
[/TD]
[TD] Name & Address
[/TD]
[TD]Book#/
[/TD]
[TD] Meter #
[/TD]
[TD] Bill Due
[/TD]
[TD]Arrears/
[/TD]
[TD]Security
[/TD]
[TD]Payment/
[/TD]
[TD]Total Due
[/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD]Old Acct ID
[/TD]
[TD][/TD]
[TD] Page#
[/TD]
[TD][/TD]
[TD] Date/Current BillL
[/TD]
[TD]Month
[/TD]
[TD]Held
[/TD]
[TD]Date
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]4934891512
[/TD]
[TD]MR KASHEV KUMAR S/O JASHEN
[/TD]
[TD]000
[/TD]
[TD]GN26189255
[/TD]
[TD] 07-May-12
[/TD]
[TD]5,423.54
[/TD]
[TD]1,500
[/TD]
[TD][/TD]
[TD]5,740.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1630925239
[/TD]
[TD]LAL GARU NANIK NAGAR
[/TD]
[TD]40
[/TD]
[TD][/TD]
[TD] 288.52
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]MOHALLAH EID GAH SADIQ ABAD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1849331160
[/TD]
[TD]MR. MEVA KHAN S/O MR. PIR
[/TD]
[TD]000
[/TD]
[TD]GN25289446
[/TD]
[TD] 07-May-12
[/TD]
[TD]11,748.16
[/TD]
[TD]1,500
[/TD]
[TD][/TD]
[TD]12,240.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1639022206
[/TD]
[TD]BUX MALIK MOH SHAHAN SHAH
[/TD]
[TD]42
[/TD]
[TD][/TD]
[TD] 441.70
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GUDDU
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]7862497716
[/TD]
[TD]QAIM DIN THAHIM ABAD SADIQ
[/TD]
[TD]000
[/TD]
[TD]GN26434736
[/TD]
[TD] 07-May-12
[/TD]
[TD]8,543.27
[/TD]
[TD]1,500
[/TD]
[TD][/TD]
[TD]9,040.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0163092444
[/TD]
[TD]ABAD
[/TD]
[TD]28
[/TD]
[TD][/TD]
[TD] 441.70
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]3289404781
[/TD]
[TD]INDER KUMAR NR OLD SABZI
[/TD]
[TD]000
[/TD]
[TD]GN26434799
[/TD]
[TD] 07-May-12
[/TD]
[TD]8,883.66
[/TD]
[TD]1,500
[/TD]
[TD][/TD]
[TD]9,380.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0163092532
[/TD]
[TD]MANDI KASHMORE
[/TD]
[TD]27
[/TD]
[TD][/TD]
[TD] 441.70
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]9822739005
[/TD]
[TD]SHAHID MOH MEERANI GUDDU
[/TD]
[TD]000
[/TD]
[TD]MR03996592
[/TD]
[TD] 07-May-12
[/TD]
[TD]12,777.35
[/TD]
[TD]1,500
[/TD]
[TD][/TD]
[TD]13,270.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0163092533
[/TD]
[TD]SADIQ ABAD
[/TD]
[TD]30
[/TD]
[TD][/TD]
[TD] 441.70
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
HI All
I have Data like this above (sample Data) and i want to convert in As below (Resulting Data).. Kindly see if anyone have a solution in form of Excel VBA Macro / or something else..that will be great help..

Thanks

[TABLE="class: grid, width: 1183, align: left"]
<tbody>[TR]
[TD]Sr.#
[/TD]
[TD]Account ID
[/TD]
[TD]Old Acct ID
[/TD]
[TD]Name & Address
[/TD]
[TD]Book#
[/TD]
[TD]Page#
[/TD]
[TD]Meter #
[/TD]
[TD]Bill Due Date/
[/TD]
[TD]Current BillL
[/TD]
[TD]Arrears/ Month
[/TD]
[TD]Security Held
[/TD]
[TD]Payment/
[/TD]
[TD]Date
[/TD]
[TD]Total Due
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]4934891512
[/TD]
[TD]1630925239
[/TD]
[TD]MR KASHEV KUMAR S/O JASHEN LAL GARU NANIK NAGAR MOHALLAH EID GAH SADIQ ABAD
[/TD]
[TD]000
[/TD]
[TD]40
[/TD]
[TD]GN26189255
[/TD]
[TD]07-May-12
[/TD]
[TD]288.52
[/TD]
[TD]5423.54 12
[/TD]
[TD]1,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5,740.00
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1849331160
[/TD]
[TD]1639022206
[/TD]
[TD]MR. MEVA KHAN S/O MR. PIR BUX MALIK MOH SHAHAN SHAH GUDDU
[/TD]
[TD]000
[/TD]
[TD]42
[/TD]
[TD]GN25289446
[/TD]
[TD]07-May-12
[/TD]
[TD]441.7
[/TD]
[TD]11748.16 12
[/TD]
[TD]1,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD]12,240.00
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]7862497716
[/TD]
[TD]0163092444
[/TD]
[TD]QAIM DIN THAHIM ABAD SADIQ ABAD
[/TD]
[TD]000
[/TD]
[TD]28
[/TD]
[TD]GN26434736
[/TD]
[TD]07-May-12
[/TD]
[TD]441.7
[/TD]
[TD]8543.27 3
[/TD]
[TD]1,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD]9,040.00
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]3289404781
[/TD]
[TD]0163092532
[/TD]
[TD]INDER KUMAR NR OLD SABZI MANDI KASHMORE
[/TD]
[TD]000
[/TD]
[TD]27
[/TD]
[TD]GN26434799
[/TD]
[TD]07-May-12
[/TD]
[TD]441.7
[/TD]
[TD]8883.66 3
[/TD]
[TD]1,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD]9,380.00
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]9822739005
[/TD]
[TD]0163092533
[/TD]
[TD]SHAHID MOH MEERANI GUDDU SADIQ ABAD
[/TD]
[TD]000
[/TD]
[TD]30
[/TD]
[TD]MR03996592
[/TD]
[TD]07-May-12
[/TD]
[TD]441.7
[/TD]
[TD]12777.35 3
[/TD]
[TD]1,500
[/TD]
[TD][/TD]
[TD][/TD]
[TD]13,270.00
[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Try this :-
Results sheet(2)
Code: 
[COLOR=navy]Sub[/COLOR] MG25Sep26
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Ray1
[COLOR=navy]Dim[/COLOR] Ray2
[COLOR=navy]Dim[/COLOR] Ray3
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Rw [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
n = Rng.Offset(, -1).SpecialCells(xlCellTypeConstants).Count
Ray1 = Array(1, 2, 4, 5, 7, 8, 10, 11, 12, 14)
Ray2 = Array(3, 4, 6, 9, 10, 13)
Ray3 = Array(1, 2, 3, 5, 6, 8)
ReDim Ray(1 To Rng.Count, 1 To 16)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng.Offset(, -1)
    [COLOR=navy]If[/COLOR] Not Dn = vbNullString [COLOR=navy]Then[/COLOR]
        c = 1
        Rw = Rw + 1
    [COLOR=navy]Else[/COLOR]
        c = c + 1
    [COLOR=navy]End[/COLOR] If
    [COLOR=navy]If[/COLOR] c = 1 [COLOR=navy]Then[/COLOR]
        [COLOR=navy]For[/COLOR] Ac = 0 To UBound(Ray1)
            Ray(Rw, Ray1(Ac)) = Dn.Offset(, Ac)
        [COLOR=navy]Next[/COLOR] Ac
    [COLOR=navy]ElseIf[/COLOR] c = 2 [COLOR=navy]Then[/COLOR]
        [COLOR=navy]For[/COLOR] Ac = 0 To UBound(Ray2)
           [COLOR=navy]If[/COLOR] Ac = 0 Or Ac = 2 Or Ac = 3 Or Ac = 6 [COLOR=navy]Then[/COLOR]
              Ray(Rw, Ray2(Ac)) = Dn.Offset(, Ray3(Ac))
           [COLOR=navy]Else[/COLOR]
            Ray(Rw, Ray2(Ac)) = Ray(Rw, Ray2(Ac)) & Chr(10) & Dn.Offset(, Ray3(Ac))
           [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] Ac
    [COLOR=navy]Else[/COLOR]
        Ray(Rw, 4) = Ray(Rw, 4) & Chr(10) & Dn.Offset(, 2)
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
Sheets("Sheet2").Range("A1").Resize(n, 14) = Ray
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG said:
Try this :-
Results sheet(2)
Code: 
[COLOR=navy]Sub[/COLOR] MG25Sep26
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Ray1
[COLOR=navy]Dim[/COLOR] Ray2
[COLOR=navy]Dim[/COLOR] Ray3
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Rw [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
n = Rng.Offset(, -1).SpecialCells(xlCellTypeConstants).Count
Ray1 = Array(1, 2, 4, 5, 7, 8, 10, 11, 12, 14)
Ray2 = Array(3, 4, 6, 9, 10, 13)
Ray3 = Array(1, 2, 3, 5, 6, 8)
ReDim Ray(1 To Rng.Count, 1 To 16)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng.Offset(, -1)
    [COLOR=navy]If[/COLOR] Not Dn = vbNullString [COLOR=navy]Then[/COLOR]
        c = 1
        Rw = Rw + 1
    [COLOR=navy]Else[/COLOR]
        c = c + 1
    [COLOR=navy]End[/COLOR] If
    [COLOR=navy]If[/COLOR] c = 1 [COLOR=navy]Then[/COLOR]
        [COLOR=navy]For[/COLOR] Ac = 0 To UBound(Ray1)
            Ray(Rw, Ray1(Ac)) = Dn.Offset(, Ac)
        [COLOR=navy]Next[/COLOR] Ac
    [COLOR=navy]ElseIf[/COLOR] c = 2 [COLOR=navy]Then[/COLOR]
        [COLOR=navy]For[/COLOR] Ac = 0 To UBound(Ray2)
           [COLOR=navy]If[/COLOR] Ac = 0 Or Ac = 2 Or Ac = 3 Or Ac = 6 [COLOR=navy]Then[/COLOR]
              Ray(Rw, Ray2(Ac)) = Dn.Offset(, Ray3(Ac))
           [COLOR=navy]Else[/COLOR]
            Ray(Rw, Ray2(Ac)) = Ray(Rw, Ray2(Ac)) & Chr(10) & Dn.Offset(, Ray3(Ac))
           [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] Ac
    [COLOR=navy]Else[/COLOR]
        Ray(Rw, 4) = Ray(Rw, 4) & Chr(10) & Dn.Offset(, 2)
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
Sheets("Sheet2").Range("A1").Resize(n, 14) = Ray
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...



HI!

It says run time error line no 09
 
Upvote 0
Try this:-
Open new "WORKBOOK".
Paste you Basic Data from thread into sheet (1) "A1".
Copy the Vb code from the thread.
With sheet (1) Open, Right click the sheet "Tab" .
Select "View Code" from the drop down box.
VB window opens.
From the Vbwindow Toolbar select "Insert", then select "Module", New Vbwindow opens.
Paste the code into this window.
Close VB Window.
In sheet (1) Cliclk "Alt + F8", Macro dialog box opens.
Select the Macro name from the List.
On the right hand side of the Dialog Box, select Run.
Sheet(2) should now be showing the results.
If this works repeat for Actual Data.
Regrds Mick
 
Last edited:
Upvote 0
MickG said:
Try this:-
Open new "WORKBOOK".
Paste you Basic Data from thread into sheet (1) "A1".
Copy the Vb code from the thread.
With sheet (1) Open, Right click the sheet "Tab" .
Select "View Code" from the drop down box.
VB window opens.
From the Vbwindow Toolbar select "Insert", then select "Module", New Vbwindow opens.
Paste the code into this window.
Close VB Window.
In sheet (1) Cliclk "Alt + F8", Macro dialog box opens.
Select the Macro name from the List.
On the right hand side of the Dialog Box, select Run.
Sheet(2) should now be showing the results.
If this works repeat for Actual Data.
Regrds Mick
Click to expand...

Thanks it works just great and perfectly thanks
 
Upvote 0
You must log in or register to reply here.

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top