Macro to Look for Name in Column and calculate other columns based on same Value

bdunk

Active Member
Joined
Aug 1, 2002
Messages
290
I have a sheet with rows that may or may not have the same value in Column E. What I would like to have happen is to have a macro that will look in column E for like values. When it finds a different value insert a blank row below the last row with the value in Column E. In this new row total all values in Column D with like values in Column E. Total all values in Column M with like values in Column E. Total all values in Column N with like values in Column E. Copy the values from one row above this new row from columns A, B, C and I.

Thanks
Brian
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Not sure you need a macro.

1) Highlight the data
2) Select Data > Subtotal

At each change in - set to the header for column E
Use function - SUM
Add Subtotal - check off the column headers for D, M and N.

That should get you 90% of the way there.
 

bdunk

Active Member
Joined
Aug 1, 2002
Messages
290
Not sure you need a macro.

1) Highlight the data
2) Select Data > Subtotal

At each change in - set to the header for column E
Use function - SUM
Add Subtotal - check off the column headers for D, M and N.

That should get you 90% of the way there.

Thanks but there are just too many rows of data to try. Atleast if I could have something that would insert the blank row after the common value in Column E then total the columns specified above. That would get me going. Any thoughts?
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Too many rows of data? This can be done on 1000s of rows of data all in one go. Follow the instructions given, especially #1.... highlight ALL the data. The subtotals will be inserted by adding rows using the column you set as the "at each change in" column, in your case that is column E. So everytime column E changes values, a row will be inserted, that is exactly what you're after. Then the "Add Subtotal" columns you indicate will get the SUMs, also exactly what you want.

I'm underlining how Excel already knows how to do what you want, you just need to highlight all the data first so it know how far to go.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,570
Messages
5,625,579
Members
416,119
Latest member
JCLLE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top