+ Reply to Thread
Results 1 to 10 of 10

Need list of unique values for sumiff'ed records

  1. #1
    Registered User
    Join Date
    02-11-2011
    Location
    Annapolis, MD
    MS-Off Ver
    Excel 2010
    Posts
    11

    Need list of unique values for sumiff'ed records

    Please see attached sample table if the below description isn't clear.

    I have a list of records that are time block entries, (e.g. 8:00 a.m. to 9:30 a.m. = 1.5 hrs) assigned to a specific task number from 1 thru 7. So out of perhaps 50 entries I might have 7 task 1 entries, 18 task 2 entries, 4 task 3 entries, etc. The final column is the 4 digit invoice number that record is billed on. So the columns are:

    col. B = time start (entry) (e.g. 8:00)
    col. C = time end (entry) (e.g. 9:30)
    col. D = hrs (calculated) (e.g. 1.5 hrs)
    col. E = task # (entry, e.g. 7)
    col. F = invoice # (entry, e.g. 4315)

    My goal is to be able to do a sumif calculation for each task #1-10 totaling column 3 (total hrs) for each task. That's the easy part. But I also need to list for each task the individual invoice #s on which that task has been billed. End result being "16.5 hrs. billed for task 7 on invoices 4315, 4326 and 4410). All entries for a given task may have the same invoice #, or 4 records may have 4 different invoice # entries. So bottom line is that each task needs a collected list generated indicating the unique invoice entries (col. F) associated with any record for that task #(col. E).
    Any ideas?
    To make it even trickier, I would love for that list to appear in a single cell as a comma separated list (e.g. 4315,4326,4410) as opposed to a series of cells each containing one invoice #. This would be much easier to plan for and manage.
    Thanks for any guidance.
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Need list of unique values for sumiff'ed records

    Try adding this UDF to your VB Editor (ALT+F11, Insert|Module)

    Please Login or Register  to view this content.
    Then in K5 enter formula:

    =SUBSTITUTE(TRIM(aconcat(IF($E$2:$E$51=I5,$F$2:$F$51,"")," "))," ",", ")

    confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Need list of unique values for sumiff'ed records

    Hi

    I think the code below will do what you want.
    Also attached your workbook with the code added

    Please Login or Register  to view this content.
    Attached Files Attached Files
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  4. #4
    Registered User
    Join Date
    02-11-2011
    Location
    Annapolis, MD
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need list of unique values for sumiff'ed records

    Roger,
    That is great code, and it works perfectly in my sample sheet, so thank you very, very much. I thought I would be smart enough to apply whatever solution someone came up with to our real time tracking spreadsheet (attached), but once I read through your lines I began questioning my ability to accurately transpose it to our somewhat more complex sheet. The questions stem from not really being able to discern what were references to cells and what wasn't (I'm obviously not a programmer). Will the code you supplied work with the attached work book? The data entry is on the first sheet ALL DATA ENTRY with invoice #s in column M, task #s in column K, with the summary on the SUMMARY sheet. I've created the destination column (column L) for the invoice #s to be collected to on the sheet SUMMARY.
    Doable?
    With thanks for any further help (and great gratitude for the help so far),
    Scott
    Attached Files Attached Files

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Need list of unique values for sumiff'ed records

    With my suggestion you don't need to know the cells to reference.. The UDF in the VBEditor is acts like a regular function...

    After you paste the code in the editor, then you use the ACONCAT() function like any other Excel function...

    Syntax: ACONCAT(range,separator)

    I used it nested within and IF and SUBSTITUTE and TRIM in order to get it down to comma separators with no commas at the end....

    Try it...

  6. #6
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Need list of unique values for sumiff'ed records

    Hi Scott

    Only just spotted your request.
    I think the attached file with the modified code as below does what you want.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-11-2011
    Location
    Annapolis, MD
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need list of unique values for sumiff'ed records

    Roger,
    Once again thank you. This is perfect. I especially like the automatic column width adjustment to accommodate the string of invoice numbers as it expands. Nice touch.
    One question (demonstrating my macro-ignorance here): Can the macro be set to run any time a value is changed in the "invoiced on" column on the sheet "all data entry"? The other workbook macros (or are they "just" code?) I have set up to run automatically I think, so there is no need to specifically run them. They just happen.
    You've been so helpful in providing this code to us, could you also educate me just a tiny bit on how I determine or set a macro to run at specific times, or always, or on a certain event?
    Hope I'm not pushing my luck here.
    With great appreciation for your help with this,
    Scott

  8. #8
    Registered User
    Join Date
    02-11-2011
    Location
    Annapolis, MD
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need list of unique values for sumiff'ed records

    Roger,
    Once again thank you. This is perfect. I especially like the automatic column width adjustment to accommodate the string of invoice numbers as it expands. Nice touch.
    One question (demonstrating my macro-ignorance here): Can the macro be set to run any time a value is changed in column M "Billed on MYS invoice #" on workheet "all data entry"? The other workbook macros (or are they "just" code?) I have set up to run automatically I think, so there is no need to specifically run them. They just happen.
    You've been so helpful in providing this code to us, could you also educate me just a tiny bit on how I determine or set a macro to run at specific times, or always, or on a certain event?
    Hope I'm not pushing my luck here.
    With great appreciation for your help,
    Scott

  9. #9
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Need list of unique values for sumiff'ed records

    Hi Scott

    It is just a question of adding another Case statement to the event code that you have on the data entry sheet.

    Event code (in this case the change of a value in a cell) is triggered by the fact that an alteration has taken place in a specified range.
    You are using the range of Target.column so it is just a simple addition to your code as shown below

    Please Login or Register  to view this content.
    If a value changes in column M, then my macro will be called and run.

  10. #10
    Registered User
    Join Date
    02-11-2011
    Location
    Annapolis, MD
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need list of unique values for sumiff'ed records

    Perfect!!
    Much gratitude!
    Scott

    p.s. And I understand events now. So thanks for the primer.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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