Macro to insert line and sums

**room7** · 11-09-2011, 08:06 PM

Im new to macros so need a little help
Task: Need a macro to
1)insert a line inbetween the change in name
2)insert a (-sum) of the amounts for that person
3)insert the person's name

My Data:
[Date ] [Name & Exp ] [amount]
[1/1/11] [John Doe Fedex] [5.00]
[1/5/11] [John Doe Stapes] [60.00]
[1/6/11] [Adam Smith Rental] [250.00]
[1/8/11] [Adam Smith Hotel] [299.00]

Ideal Outcome:
[Date ] [Name & Exp ] [amount]
[1/1/11] [John Doe Fedex] [5.00]
[1/5/11] [John Doe Stapes] [60.00]
[John Doe] [-65.00]
[1/6/11] [Adam Smith Rental] [250.00]
[1/8/11] [Adam Smith Hotel] [299.00]
[Adam Smith] [-549.00]

**MarvinP** · 11-09-2011, 09:09 PM

Hi room7,

See the attached with a Pivot Table to see if it will do what you need. No formulas, No VBA or Macros

**room7** · 11-10-2011, 02:48 PM

Originally Posted by MarvinP

Hi room7,

See the attached with a Pivot Table to see if it will do what you need. No formulas, No VBA or Macros

This doesnt work, the 2nd colum is the name and explanation combined
so : Jim Jones Office Max or Sally Turner Taco Bell

**room7** · 11-15-2011, 07:27 PM

Please help. A macro would save me about 10 hours a week. I just cant get it to work right

I have attached an example
Sheet 1 is the raw data
Sheet 2 is what i get after hours of work (The real list is 20k-30k lines)

What I do:
1) Go through and mannually instert line between each new name
- move down list, shift+space, ctr+(+)
2) Add sums move from bottom up
- alt+h,u,s, enter
-ctr+up, up
-repeat over and over and over
3) Find and replace 'Sum' with '-Sum'

**MarvinP** · 11-15-2011, 08:11 PM

OK Room7,

Find the attached where you can go through what I think you want using Grouping and some other Excel Tricks.

1. You need to parse out the name to another column. I hope all names have spaces inbetween first and last.
2. Group the data and use a subtotal
3. Multiply all formulas in col D by neg 1.

Not Easy Peasy but you should be able to do thousands of rows in a few steps.

**room7** · 11-15-2011, 08:45 PM

Originally Posted by MarvinP

OK Room7,

Find the attached where you can go through what I think you want using Grouping and some other Excel Tricks.

1. You need to parse out the name to another column. I hope all names have spaces inbetween first and last.
2. Group the data and use a subtotal
3. Multiply all formulas in col D by neg 1.

Not Easy Peasy but you should be able to do thousands of rows in a few steps.

Works great...cuts my time down from 5hrs/week to 20mins
Thank you so much