+ Reply to Thread
Results 1 to 15 of 15

Sumifs Labeling

  1. #1
    Registered User
    Join Date
    10-21-2016
    Location
    Denver Co
    MS-Off Ver
    2013
    Posts
    65

    Sumifs Labeling

    I am not sure if this can be done nor where to start, need help with taking the formula below an once it finds the things that tie to zero label them in groups
    So if it is 1 to many it will label that group with a "1" and then next maybe one to one and it labels it "2" . Basically i want to see the grouping of the things that are zero beside seeing it only as all 1 lump sum. Any ideas how to do this. The attachment has the items group by Colors. I just need it group by a Number or Letter or anything beside color.

    =IF(ROUND(SUMIFS([Amount],[OPERATION/NAME],[@[OPERATION/NAME]]),2)=0,"Zero")
    Attached Files Attached Files

  2. #2
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Sumifs Labeling

    There is no straight forward way to accomplish this.

    The only way is to generate all possible combinations of numbers in Column G and check their sum, if the sum hits 0 for any combination then the values are allotted a group number.

    Is there any indication in your data you can suggest that can limit the number of possible combinations. As without any indication the amount of possible combinations will increase tremendously with increase in amount of data and thus will increase amount of processing time.
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  3. #3
    Registered User
    Join Date
    10-21-2016
    Location
    Denver Co
    MS-Off Ver
    2013
    Posts
    65

    Re: Sumifs Labeling

    Would it help if i did not run the formula off the [Amount] and have it state to look at the [payment] sumif the value of the number 251.01 for example with the numbers in [Receipts] and then label them as a group. etc etc. Would that work?
    Attached Files Attached Files

  4. #4
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Sumifs Labeling

    No, you are not getting my point. Sumif function doesn't provide you the the numbers that match up to a particular sum. We will need to generate all possible combinations using VBA and check each combination if that equals 0. I was asking if there is any way we can limit the number of combinations looking at other columns. But I went through the data, there no such indication which can possibly suggest that these items will make a group. So the only way is to generate all combinations. Let me see if I can accomplish it.

  5. #5
    Registered User
    Join Date
    10-21-2016
    Location
    Denver Co
    MS-Off Ver
    2013
    Posts
    65

    Re: Sumifs Labeling

    Ok, now i understand. Let me know what you find. FYI the original data sheets is like 5k Lines,setup the exact same way.If you are setting up a vba could you include to extraction of all the group data to tab or a new worksheet. New worksheet would be preferred

  6. #6
    Registered User
    Join Date
    10-21-2016
    Location
    Denver Co
    MS-Off Ver
    2013
    Posts
    65

    Re: Sumifs Labeling

    Also as a FYI that you probably already know in the original data Column1 aka H. Is not there.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,896

    Re: Sumifs Labeling

    What are you trying to group on, and what would some sample answers look like?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Registered User
    Join Date
    10-21-2016
    Location
    Denver Co
    MS-Off Ver
    2013
    Posts
    65

    Re: Sumifs Labeling

    Grouping by Operation/Name or Column C. Because the title can change and per the attachment is example. Notice Tab Sheet 2 for the extraction of data that would normally be in a different Workbook as Sheet 1. Is this what you were referring to?

    =IF(ROUND(SUMIFS([Amount],[OPERATION/NAME],[@[OPERATION/NAME]]),2)=0,"Zero")
    Attached Files Attached Files

  9. #9
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Sumifs Labeling

    Hello

    Finally I think I made it, this is the code-

    Please Login or Register  to view this content.
    It is a quite complex code so I cannot guarantee just by looking at it that it would work fine. Probably you can test it properly and see if it seems to yield correct results.
    Speed was of course the main concern so I made sure to minimize processing time as much as I can by minimizing the traffic between the sheet and the macro.

    Do test it and let me know how it works.
    Example Data1.xlsm

    Regards
    Sourabh

  10. #10
    Registered User
    Join Date
    10-21-2016
    Location
    Denver Co
    MS-Off Ver
    2013
    Posts
    65

    Re: Sumifs Labeling

    Continue to test but so far it is working. Question. I notice column H needs to be present having the formula already ran. Is it possible to include that logic in the beginnging of the vba for that formula. I actually insert column H, Apply the formula to all lines with the formula below. If that is to much for the system to handle i can run this into two forms. Or can i run two vba. Can you help me with that as well. Attached is an example of the original Data.


    =IF(ROUND(SUMIFS([Amount],[OPERATION/NAME],[@[OPERATION/NAME]]),2)=0,"Zero")
    Attached Files Attached Files

  11. #11
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899
    Yes, that is easy.

    It's 2:30 am here. Will do that when I wake up

    Regards
    Sourabh

  12. #12
    Registered User
    Join Date
    10-21-2016
    Location
    Denver Co
    MS-Off Ver
    2013
    Posts
    65

    Re: Sumifs Labeling

    Totally understandable

  13. #13
    Registered User
    Join Date
    10-21-2016
    Location
    Denver Co
    MS-Off Ver
    2013
    Posts
    65

    Re: Sumifs Labeling

    Will this logic or process work better in Access

  14. #14
    Registered User
    Join Date
    10-21-2016
    Location
    Denver Co
    MS-Off Ver
    2013
    Posts
    65

    Re: Sumifs Labeling

    Need help adding the below formula in the beginning of this vba and Appling the formula to all lines. Then taking that Grouped data of Zero by [Operation /Name ] and creating a tab for each, labeling the tab by their [operation name] and then run the Full vba that i have attached to each tab that will have a running number that is not used twice in the file so i can have a summary tab of all Grouped number in its separate tab with data.

    =IF(ROUND(SUMIFS([Amount],[OPERATION/NAME],[@[OPERATION/NAME]]),2)=0,"Zero")

    I apologize this came thru twice. I couldnt find my orginal email.

  15. #15
    Registered User
    Join Date
    10-21-2016
    Location
    Denver Co
    MS-Off Ver
    2013
    Posts
    65

    Re: Sumifs Labeling

    Sourabh

    How are you? Need your help once more with the Example Data1.xlsm spreadsheet. Is there a way to adjust the Macro to Label per Column A. Meaning if the number in Column A is the same, sum all and label and the entire group per A and then go to the next and do the same. I will drop data into this spreadsheet in order by Column A. For this file all that is need is a label by column A that sum to zeros.I have attached and example of what it would look like. Also could have the collumen H be moved to AB of AC for the search as welll.
    Attached Files Attached Files

+ 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. Labeling A Graph
    By rez3 in forum Excel General
    Replies: 3
    Last Post: 01-14-2016, 12:23 PM
  2. [SOLVED] Labeling duplicates
    By excelsonic in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2012, 11:53 AM
  3. Hover labeling
    By stebs in forum Excel General
    Replies: 0
    Last Post: 11-22-2011, 03:49 AM
  4. labeling rows
    By Jordans121 in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 06-03-2010, 07:10 PM
  5. Labeling numbers
    By Jordans121 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-29-2010, 05:26 PM
  6. Pie Chart Labeling
    By Tantrum31 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-13-2008, 06:16 AM
  7. labeling worksheets
    By fifijr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-04-2006, 07:41 PM

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