Auto Group data

wannabe12

New Member
Joined
Nov 28, 2012
Messages
13
Hi there,

I have this Dataset -


[TABLE="width: 597"]
<TBODY>[TR]
[TD]170</SPAN>
[/TD]
[TD="colspan: 4"]Batch No: 170 Period: 12/13/01/01</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]170</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]170</SPAN>
[/TD]
[TD]26/04/2012</SPAN>
[/TD]
[TD]400</SPAN>
[/TD]
[TD]Narrative of Jnl </SPAN>
[/TD]
[TD]BC01</SPAN>
[/TD]
[TD]D001</SPAN>
[/TD]
[TD]3MM12 </SPAN>
[/TD]
[TD]-250</SPAN>
[/TD]
[/TR]
[TR]
[TD]170</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]---------------</SPAN>
[/TD]
[/TR]
[TR]
[TD]170</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]-250</SPAN>
[/TD]
[/TR]
[TR]
[TD]170</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]179</SPAN>
[/TD]
[TD="colspan: 4"]Batch No: 179 Period: 12/13/01/01</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]179</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]179</SPAN>
[/TD]
[TD]18/04/2012</SPAN>
[/TD]
[TD]400</SPAN>
[/TD]
[TD]Narrative of Jnl </SPAN>
[/TD]
[TD]BC01</SPAN>
[/TD]
[TD]D001</SPAN>
[/TD]
[TD]2IRIMPACT </SPAN>
[/TD]
[TD]-50</SPAN>
[/TD]
[/TR]
[TR]
[TD]179</SPAN>
[/TD]
[TD]18/04/2012</SPAN>
[/TD]
[TD]400</SPAN>
[/TD]
[TD]Narrative of Jnl </SPAN>
[/TD]
[TD]BC01</SPAN>
[/TD]
[TD]D001</SPAN>
[/TD]
[TD]2RETWARM1 </SPAN>
[/TD]
[TD]-12</SPAN>
[/TD]
[/TR]
[TR]
[TD]179</SPAN>
[/TD]
[TD]18/04/2012</SPAN>
[/TD]
[TD]400</SPAN>
[/TD]
[TD]Narrative of Jnl </SPAN>
[/TD]
[TD]BC01</SPAN>
[/TD]
[TD]D001</SPAN>
[/TD]
[TD]2IRUNSOL </SPAN>
[/TD]
[TD]-15</SPAN>
[/TD]
[/TR]
[TR]
[TD]179</SPAN>
[/TD]
[TD]18/04/2012</SPAN>
[/TD]
[TD]400</SPAN>
[/TD]
[TD]Narrative of Jnl </SPAN>
[/TD]
[TD]BC01</SPAN>
[/TD]
[TD]D001</SPAN>
[/TD]
[TD]2IRIMPACT </SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[/TR]
[TR]
[TD]179</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]---------------</SPAN>
[/TD]
[/TR]
[TR]
[TD]179</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]-77</SPAN>
[/TD]
[/TR]
[TR]
[TD]179</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Basically I need a Macro thats groups the data, at each Change in column A. However in simple steps I would like the Macro to do this -
For the first row of "170" to go to column H, and search for -------, the Number below this copy to the first row of 170.
So if the row of the first line of 170 was Row5 and the column with all the ---- in is column H, the macro would copy the "-250" to H5,
on the same line as the title Batch.
Next the Macro will move to the second line of 170, and group this line all the way to the last line of 170.
Next the macro will move to the next batch of 179. Again it will go to column H search for ---, copy the figure one cell below this
which is -77, copy this to the same line as "Batch No:179....", but in column H. So if this row is row11, -77 would
be copied to H11.
Then Group the next line of 179 to the last line of 179, and then move to the next batch.
For info - Im at beginner level on Macros, and anyones who does this I can donate some money to a Charity of your choice or
to Mr Excel.

Kind Regards,:cool:
Amish
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Amish,

Try this code. Either in a code module or in the sheet module.
It needs your sheet to be Active when run.
Not sure what you have in the way of headers so edit the red 3 to suit your first line of data !!!!!!!!

Rich (BB code):
Sub Auto_Group()
Dim s As Integer
Dim c As Integer
Dim bnum As Integer
s = 3  '****ie start at row 3 ** edit if start is different row!!
bnum = Cells(s, "A")
Do Until bnum = 0
c = s
Do Until Not Cells(c, "A") = bnum
If Cells(c, "H") Like "*-----*" Then Cells(s, "H") = Cells(c + 1, "H")
c = c + 1
Loop
Range(Cells(s + 1, 1), Cells(c - 1, 1)).Rows.Group
Range(Cells(s + 1, 1), Cells(c - 1, 1)).Rows.EntireRow.Hidden = True
s = c
bnum = Cells(s, "A")
Loop
End Sub

Hope that helps.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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