+ Reply to Thread
Results 1 to 12 of 12

VBA Macro to create subtotals based around criteria - some of this criteria is duplicated

  1. #1
    Registered User
    Join Date
    06-26-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    6

    VBA Macro to create subtotals based around criteria - some of this criteria is duplicated

    Hi,

    I am new to this forum but hoping someone can still help. I have a set of data that I need to be able to create subtotals from. The issue is that some of the data is duplicated yet it is correct. An example is below


    Current data format
    item1 100
    item1 150
    item1 100
    item2 100
    item2 100
    item1 100
    item1 100

    Required Output
    item1 350
    item2 200
    item1 200


    My initial thought was some form of Do until loop that contains a Sum. For instance it would sum item1 numbers (350) until it gets to item2. At which point it would add it at the bottom of the sheet as a subtotal shown above in the Required Output. I would need to the macro to then do this for item2 and item1 again.

    My issue with just using a cell formula of a sumif is that adds all of item1 together and can't distinguish it.

    I hope someone can help with this,

    Many thanks for reviewing,

    Adam

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: VBA Macro to create subtotals based around criteria - some of this criteria is duplica

    This seems to work.
    Please Login or Register  to view this content.
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    06-26-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    6

    Re: VBA Macro to create subtotals based around criteria - some of this criteria is duplica

    Thank you I will try this out now.

  4. #4
    Registered User
    Join Date
    06-26-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    6

    Re: VBA Macro to create subtotals based around criteria - some of this criteria is duplica

    Hi David,

    Thanks for your help on the above I can follow the logic and it does work for the example listed above. However, the code is above is static to that example. I need to modify it if possible in the following way:

    1) The number of items may vary - if i'm not mistaken the code above assumes only 2 items.
    2) This is an issue I failed to highlight in the above example as I thought I would be able to overcome it if I had the skeleton code, alas I can not. Therefore this is how the data source may appear:

    Item1
    Part1
    Part2
    Part3
    Item1
    Part1
    Part2
    Item2
    Part1
    Part2
    Part3

    As you can see above Item1 may appear directly after another Item1 except they may have different configurations and therefore need to be subtotalled separately. Also the number of Parts in each item may vary.

    If you are able to assist further I would be most grateful,

    Thanks again for the assistance so far,

    Adam

  5. #5
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: VBA Macro to create subtotals based around criteria - some of this criteria is duplica

    And does Item have a value in the next column? Or just the Part#?

    If it has a value, do you want it to be included in the total of Item1-Part#?

  6. #6
    Registered User
    Join Date
    06-26-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    6

    Re: VBA Macro to create subtotals based around criteria - some of this criteria is duplica

    Hi David,

    Yes Item 1 will have a value in the column next to it that would need to be included in the subtotal with the Part no. value

    Thanks,

    Adam

  7. #7
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: VBA Macro to create subtotals based around criteria - some of this criteria is duplica

    Then I'm afraid that, without having a known list of Item, I don't know of a way to do it.

    If you can post a more realistic sample of data, we might can come up with something.

    if I'm not mistaken the code above assumes only 2 items.
    No it should handle any number of items. (But I guess that's moot now.)

  8. #8
    Registered User
    Join Date
    06-26-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    6

    Re: VBA Macro to create subtotals based around criteria - some of this criteria is duplica

    Hi,

    Thanks for the support David here is a sample of what I'm expecting:

    Machine Value
    Machine 1 £50.00
    Ex51 £100.00
    Ek52 £100.00
    Machine 1 £100.00
    Ex51 £40.00
    Ek52 £50.00
    Ek53 £200.00
    Machine 2 £200.00
    pl53 £100.00
    pl53 £100.00
    Machine 3 £300.00
    pl53 £300.00
    Machine 3 £100.00
    pl53 £100.00
    Machine 4 £300.00
    pl53 £300.00

    The Machine will be written as "Machine" and then a number i.e. Machine 3
    Last edited by adamg4988; 07-03-2014 at 11:34 AM. Reason: Accidental post

  9. #9
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: VBA Macro to create subtotals based around criteria - some of this criteria is duplica

    OK, give this a try.
    Please Login or Register  to view this content.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA Macro to create subtotals based around criteria - some of this criteria is duplica

    Try this
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    06-26-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    6

    Re: VBA Macro to create subtotals based around criteria - some of this criteria is duplica

    Hi David,

    Thank you this code is excellent and works perfectly for the purpose required. The only part that I hope is easy to fix is it rounds the numbers to a whole number when I need them to 2 decimal places, is this possible?

    Thanks again, very happy

    Edit: Managed to solve this by following you comment of changing the 'Long' to 'Double'

    As mentioned thanks again this code really is great!


    Adam
    Last edited by adamg4988; 07-04-2014 at 06:24 AM. Reason: Solved issue myself

  12. #12
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: VBA Macro to create subtotals based around criteria - some of this criteria is duplica

    Try changing the line as commented.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] delete duplicated rows based on a criteria in another column
    By melody10 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-08-2013, 06:53 AM
  2. [SOLVED] Extract duplicated values based on an extra criteria
    By Eduard in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2013, 05:50 AM
  3. [SOLVED] create drop down box to select multiple criteria and link macro to each criteria
    By anand_erin in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-03-2012, 06:35 PM
  4. Replies: 1
    Last Post: 07-01-2011, 12:37 PM
  5. MACROS: Sort by multiple criteria - Create tabs based off of criteria
    By svineyard in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2009, 10:48 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1