Articles

I need a macro to convert data arranged in rows to columns

January 16, 2013 at 05:53:02
Specs: Windows 7

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.


See More: I need a macro to convert data arranged in rows to columns

Report •


#1
January 16, 2013 at 19:21:26

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  36

using 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.


Report •
Related Solutions


Ask Question