Automate Report -- Macro Question

[galacticwino]

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

 

[XL Guru]

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

 

[galacticwino]

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

 

[XL Guru]

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

 

[galacticwino]

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

 

[XL Guru]

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

 

[XL Guru]

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