I have a worksheet where the Data is arranged in rows. There is a an identifier in Bold that has a unique Id number after that there is a overall total for that particular Identifier in minutes. The next rows will be the number of occurances that make up the total. This will change each day sometimes there might be only 2 occurances sometimes there may be 100. After the last occurance for an identifier the next identifier is listed in bold in the same format as the first. This can go on for as many as 20 different identifiers.
I need all the identifiers listed in bold arranged into colums. Below each identifier I need to know the date of each occurance for that identfier as well as the duration. At the bottom of the column I need to put the total duration of the colum and the total number of occurances.
I would like to paste the raw data from the worksheet into an excel spreadsheet. Hit a macro button and have it arrange the data into 15 or 20 colums and total and count the number of occurances.
One of the major problems is that each day the occurances are different so it has to know when a row begins a new identifier. The identifiers always have the same name and it is in bold font. The occurances always begin on the next row after the Identifier in bold and end when the next identifier appears.
Hi It's not quite clear to me exactly how your data is arranged
and what the final format should be.
Could you post examples of the "before" and "after"?What you want should be possible. I've done a quick test on the following data
<b>Heading 1</b> item 1 item 2 item 3 item 4 item 5 item 6 item 7 item 8 <b>Heading 2</b> item 9 item 10 item 11 item 12 item 13 item 14 item 15 item 16 item 17 <b>Heading 3</b> item 18 item 19 item 20 <b>Heading 4</b> item 21 item 22 item 23 item 24 item 25 item 26 item 27 <b>Heading 5</b> item 28 item 29 item 30 item 31 item 32 item 33 item 34 item 35 item 36("Headers" are bolded) and turned it into this format
Heading 1 item 1 item 2 item 3 item 4 item 5 item 6 item 7 item 8 Heading 2 item 9 item 10 item 11 item 12 item 13 item 14 item 15 item 16 item 17 Heading 3 item 18 item 19 item 20 Heading 4 item 21 item 22 item 23 item 24 item 25 item 26 item 27 Heading 5 item 28 item 29 item 30 item 31 item 32 item 33 item 34 item 35 item 36using this code
Sub FindBold() Lastrow = Range("A" & Rows.Count).End(xlUp).Row RCount1 = 2 For i = 1 To Lastrow If Cells(i, 1).Font.Bold = True Then Header = Cells(i, 1) CCount = 2 RCount = RCount + 1 Else Cells(i, 1).Copy Sheets(2).Cells(RCount, 1) = Header Sheets(2).Cells(RCount, CCount).PasteSpecial CCount = CCount + 1 End If Next End Sub(The bolding didn't come out here
but it is there in my test data)If this is close to what you want it won't be
too much more effort to get the
summaries you need. But, again,
a sampling of your data would be
crucial.