Sum After Segmented Number Series in Column
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Sum After Segmented Number Series in Column

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Paducah, KY
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello,

    I hope someone can help me or point me in the right direction. I have a file that has three blank rows between each different storeroom number. I need to add totals to the second (middle) blank cell after every segmented number series under QTY, PRICE, and TOTAL; however, I need to have the flexibility to be able to pick a starting cell in any column, not just column B, C, and D (this is just one file we would use this for).

    STORE
    ROOM.....QTY......PRICE......TOTAL
    005-29........1........33.00......33.00

    Need total>...1........33.00.....33.00

    005-2921......1........73.64.....73.64
    005-2921......1........73.64.....73.64
    005-2921......1........73.64.....73.64

    Need total>...3.......220.92....220.92

    020-1065......2.........4.06......8.12
    020-1065......6.........4.06.....24.36

    Need total>...8.........8.06.....32.48

    Is there a macro that would do this? I've searched, but did not find one. I appreciate any assistance, thanks.

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Select your first column. Hit Edit-Go to-Special. Choose Blanks, hit OK. Now hit Edit-Delete and choose Entire row. That deletes all your blank rows. Now, sort by your store room number. Hit Data-Subtotals. Tell it For each change in Store number to give you the SUM of whatever columns you want. Hit ok. At the upper-left of your screen, you'll see a 1-2-3 (just above the row numbers). Hit the 2.
    ~Anne Troy

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Paducah, KY
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thank you for your suggestion, but I was wondering if there was a VBA way to click a start cell in a column (column is variable), put the subtotal in the middle blank cell below each segment, and then return to the starting cell without changing the format of the sheet and without having to deal with the "total" and "grand total" lines created by the subtotal command. I hope this makes sense.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com