+ Reply to Thread
Results 1 to 2 of 2

Macro to add rows at a change in data

  1. #1
    Mr. T.
    Guest

    Macro to add rows at a change in data

    Sample data :

    COLORADO 014C 2391942 3528600 141
    172 724899 1067108 40
    1797 546812 820255 34
    1998 416032 626383 28
    2216 806535 1176044 44
    237 1872461 2710125 104
    2477 168605 253895 10
    2830 1016305 1527912 64
    3262 396654 557467 20
    3524 1261749 1913467 79
    3525 1704009 2442987 99
    3609 614421 914285 33
    3641 1204536 1760984 73
    3704 1914318 2761913 109
    3748 913416 1333302 54
    3934 1355085 2068155 85
    3938 857785 1273634 57
    COLORADO TOTAL 18165564 26736516 1074
    36331128 53473032 2148

    Other info:

    1. I used the subtotal command to get the last line of data. Note that the
    row above it is the subtotal already in the spreadsheet.

    2. In column a, there is a number that reflects the state in column B

    3. Columns C- F have data that gets subtotaled.


    How do I create a macro that:

    1. inserts a row above and below where each subtotal occurs. Keep in mind it
    would be a change in Column A (would prefer B).

    2. After inserting the rows , I then need to delete the rows that has the
    State Total already in the spreadsheet


    3. Or better yet: lookup value in column a, preplace blank in column b with
    dat from lookup table. At change in column b, insert row. Needs to be done in
    this order, as if this is done before lookup/replace, I will have a break
    from "state" and state total"

    Thanks..

  2. #2
    Glen Mettler
    Guest

    Re: Macro to add rows at a change in data

    I am not sure what you are trying to do with the row insert. If you want
    totals for all the states, I would set up a sheet (call it State Sum) with
    all the states listed in column A and the totals in columns B,C, & D.
    Then set up 50 sheets and name them approprately with the data in A, B, C
    Then, instead of having you total at the bottm, put your total in row 1 for
    each column. That way, it doesn't matter if the rows are different for each
    state. The totals are always in row 1.

    Sheet State Sum
    A B C D
    State Tot1 Tot2 Tot3
    AL
    AK
    AZ
    AR
    CA
    CO =CO!A1 =CO!B1 =CO!C1
    CT

    Thus, whenever a total changes in any state, the values in the State Sum
    sheet will automatically be updated.

    Hope this helps

    Glen



    "Mr. T." <[email protected]> wrote in message
    news:[email protected]...
    > Sample data :
    >
    > COLORADO 014C 2391942 3528600 141
    > 172 724899 1067108 40
    > 1797 546812 820255 34
    > 1998 416032 626383 28
    > 2216 806535 1176044 44
    > 237 1872461 2710125 104
    > 2477 168605 253895 10
    > 2830 1016305 1527912 64
    > 3262 396654 557467 20
    > 3524 1261749 1913467 79
    > 3525 1704009 2442987 99
    > 3609 614421 914285 33
    > 3641 1204536 1760984 73
    > 3704 1914318 2761913 109
    > 3748 913416 1333302 54
    > 3934 1355085 2068155 85
    > 3938 857785 1273634 57
    > COLORADO TOTAL 18165564 26736516 1074
    > 36331128 53473032 2148
    >
    > Other info:
    >
    > 1. I used the subtotal command to get the last line of data. Note that the
    > row above it is the subtotal already in the spreadsheet.
    >
    > 2. In column a, there is a number that reflects the state in column B
    >
    > 3. Columns C- F have data that gets subtotaled.
    >
    >
    > How do I create a macro that:
    >
    > 1. inserts a row above and below where each subtotal occurs. Keep in mind
    > it
    > would be a change in Column A (would prefer B).
    >
    > 2. After inserting the rows , I then need to delete the rows that has the
    > State Total already in the spreadsheet
    >
    >
    > 3. Or better yet: lookup value in column a, preplace blank in column b
    > with
    > dat from lookup table. At change in column b, insert row. Needs to be done
    > in
    > this order, as if this is done before lookup/replace, I will have a break
    > from "state" and state total"
    >
    > Thanks..
    >





+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1