Automate Report -- Macro Question

I have data in excel like this:

1
aaa
bbb
ccc
ccc
ccc
2
aaa
aaa
ccc
3
aaa
bbb
bbb
....

that I need to repeatedly make into a specific landscape format like this:

1 aaa bbb ccc
..............ccc
..............ccc
2 aaa ccc
...aaa
3 aaa bbb
........bbb
---------(PERIODS ONLY FOR ALLIGNMENT PURPOSES)---------------------

Any ideas how to start this. I use VB frequently, but the multiple rows (like in series 1 of several "ccc") are confusing me. Any tips would be appreciated!! Thanks,

Wino

 

>> Any ideas how to start this

Are those the only options per number? Either "aaa" or "bbb" or "ccc" (regardless of how many)?

Rgds,
Andy

 

Actually, there would be 4 options rather than 3. Each row contains several cells worth of data. And the key identifier is in that first cell, column A-- that's where one of the 4 options is.

And then I would copy the set number of cells every time with the identifier and the data, as opposed to just copying one cell-- such as columns A through G in that row. Does that make sense?

Thanks for your help!

Wino

 

>> Does that make sense?

I'm afraid not (to me, at any rate).

You say "each row contains several cells worth of data" yet the original example looks like a single column.

Maybe you could zip your file & upload it?

Rgds,
Andy

 

Sorry about the confusion -- here is one tab with an example of the original data, and one tab with the required output. Thanks again for looking into this!

wino

 

Even with seeing it I can't figure all the possible parameters, however I knocked up a sub to reconfigure 1st sheet data as per 2nd sheet (NB - I don't do proper code).

>> Any ideas how to start this

Well ya gotta start somewhere.
I inserted a duplicate of col A to the left of col A then stripped out the text cells, as a marker for the sub. Also formulas in D1:F1 to calc # of rows per "aaa" or "bbb" or "ccc" per number.

Select A2 & run the sub, it'll convert the B3:G6 data and end on A7, from where you can run it again (CTRL+Y). I've not looped this to run for all entries in (new) col A as I may be miles down the wrong road already.

You'll see that to top'n'tail you'd need to delete B:G then run down col A deleting any wholly blank rows.

I only ask that you don't tell me if you've fixed this already, either by code, or manually having given up on code.

HTH,
Andy

 

wino,

In case you check back sometime, one section (at least) of the code can be pruned a fair bit. The 6-line section

'x = ActiveCell.Row + 1
'Selection.End(xlDown).Select
'y = ActiveCell.Row
'Selection.End(xlUp).Offset(1, 1).Select
'NumRows = y - x
'Selection.Resize(NumRows, 1).Select

can be replaced with

Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeBlanks).Offset(0, 1).Select

NB - this is all one line ; watch for text wrap.

Rgds,
Andy