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

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

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

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

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

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