1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Automate Report -- Macro Question

Discussion in 'Business Applications' started by galacticwino, Apr 12, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. galacticwino

    galacticwino Thread Starter

    Joined:
    Apr 5, 2004
    Messages:
    3
    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 :confused:
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> 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
     
  3. galacticwino

    galacticwino Thread Starter

    Joined:
    Apr 5, 2004
    Messages:
    3
    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
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> 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
     
  5. galacticwino

    galacticwino Thread Starter

    Joined:
    Apr 5, 2004
    Messages:
    3
    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
     

    Attached Files:

  6. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    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
     

    Attached Files:

  7. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    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
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/219630

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice