Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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." wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change data from two rows into 2 columns | Excel Discussion (Misc queries) | |||
How to insert rows based on change in data | Excel Worksheet Functions | |||
macro to Insert rows at every change in a column | Excel Programming | |||
change data in rows to be viewed in columns | Excel Discussion (Misc queries) | |||
Macro & Command Key to change data from Colums to rows | Excel Programming |