Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Columns For Change, Insert Blank Rows, and Sum Different Co
Hi, I have data that I export from Proclarity to Excel monthly. After I do
this, I'd like to run a macro that compares data in column A & B to data in A & B for the next row. If data in EITHER A or B are different, I'd like to Insert 2 new rows and insert the sum for that group on the first newly inserted row for columns D,E,F, and G. Both Column A & B contain numbers AND text. Here is an example of the data separated by '/': Column A/Column B/Column C/Column D......etc 8999 Path & Lab/ 1234XN425 / 2224 Text Name / $1225.36..... 8999 Path & Lab/ 1234XN425 / 2224 Text Name / $9365.91..... ( Column A different below: Insert 2 new rows and sum columns D-G on first row.) 8901 Mental Health/ 1234XN425 / 2224 Text Name/ $1234.00.... So, column A OR B data different, insert 2 new rows and sum D-G on 1st new row. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Columns For Change, Insert Blank Rows, and Sum Different Co
Suppose data looks like this:
8999 Path & Lab/ 1234XN425 / 2224 Text Name / $100 / $100 / $100 8999 Path & Lab/ 1234XN425 / 2224 Text Name / $200 / $200 / $200 You say "Column A different below: Insert 2 new rows and sum columns D- G on first row.)" What is the sum, $900? Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Columns For Change, Insert Blank Rows, and Sum Differen
No, sorry. I need to sum each of the 4 columns D,E,F,G individually. I see
that I worded it poorly. "merjet" wrote: Suppose data looks like this: 8999 Path & Lab/ 1234XN425 / 2224 Text Name / $100 / $100 / $100 8999 Path & Lab/ 1234XN425 / 2224 Text Name / $200 / $200 / $200 You say "Column A different below: Insert 2 new rows and sum columns D- G on first row.)" What is the sum, $900? Merjet |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Columns For Change, Insert Blank Rows, and Sum Different Co
You may find that your life in excel becomes easier if you use its built in
functions. Add headers to your data Data|subtotals (twice) Once for column B, then once more for column A. If you don't like the double subtotals, you could concatenate the data in columns A and B with something like: Insert a new column A (shifting everything to the right one column) Add headers to A1 Put this in A2 =b2&"---"&c2 and drag down. Then do the data|subtotals on column A. It won't be exactly what you specified, but it's simple and it's built in. Bagman wrote: Hi, I have data that I export from Proclarity to Excel monthly. After I do this, I'd like to run a macro that compares data in column A & B to data in A & B for the next row. If data in EITHER A or B are different, I'd like to Insert 2 new rows and insert the sum for that group on the first newly inserted row for columns D,E,F, and G. Both Column A & B contain numbers AND text. Here is an example of the data separated by '/': Column A/Column B/Column C/Column D......etc 8999 Path & Lab/ 1234XN425 / 2224 Text Name / $1225.36..... 8999 Path & Lab/ 1234XN425 / 2224 Text Name / $9365.91..... ( Column A different below: Insert 2 new rows and sum columns D-G on first row.) 8901 Mental Health/ 1234XN425 / 2224 Text Name/ $1234.00.... So, column A OR B data different, insert 2 new rows and sum D-G on 1st new row. Thanks -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Columns For Change, Insert Blank Rows, and Sum Differen
Let's see if I get it. Suppose data in rows 1-3 looks like this:
8999 Path & Lab/ 1234XN425 / 2224 Text Name / $100 / $100 / $100 / $100 8999 Path & Lab/ 1234XN425 / 2224 Text Name / $200 / $200 / $200 / $200 8901 Mental Health/ 1234XN425 / 2224 Text Name/ $1234.00.... You want to insert empty rows at 3 & 4, pushing the 3rd line of data to row 5. Where do you want $400 and $800 ? Cells A3 and B3? Merjet |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Columns For Change, Insert Blank Rows, and Sum Differen
I want to insert the empty rows where you say, but then sum the columns
D,E,F,G. So, $300 would appear in the newly inserted row in cells D3,E3,F3,G3. Then Loop and do the same all the way down. "merjet" wrote: Let's see if I get it. Suppose data in rows 1-3 looks like this: 8999 Path & Lab/ 1234XN425 / 2224 Text Name / $100 / $100 / $100 / $100 8999 Path & Lab/ 1234XN425 / 2224 Text Name / $200 / $200 / $200 / $200 8901 Mental Health/ 1234XN425 / 2224 Text Name/ $1234.00.... You want to insert empty rows at 3 & 4, pushing the 3rd line of data to row 5. Where do you want $400 and $800 ? Cells A3 and B3? Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to Rows and then Insert Blank Rows | Excel Discussion (Misc queries) | |||
How do i insert blank rows between data that is thousands of rows | Excel Discussion (Misc queries) | |||
How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) | |||
Insert a blank Column Every 9 Columns | Excel Programming | |||
Insert Blank Column every 9 Columns | Excel Programming |