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

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

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

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

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

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