+ Reply to Thread
Results 1 to 13 of 13

VBA Dependent Unique Distinct Lists

  1. #1
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    VBA Dependent Unique Distinct Lists

    I wonder whether someone could help me please.

    I'm using the attached file to extract data from a 'Summary' 'Source' sheet, create a unique distinct list of values from a specified column before pasting the data to multiple 'Destination sheets upon certain text string values being found.

    DIR
    • If the text string "DIR" is found in column E on the 'Source' sheet,
    • The value from column D is copied, creating a unique distinct list so the same value is not repeated, and
    • Pastes this into column B on the "Direct Activities" 'Destination' sheet.

    Enhancements
    • If the text string "Enhancements" is found in column E on the 'Source' sheet,
    • The value from this column is copied, creating a unique distinct list so the same value is not repeated, and
    • Pastes this into column B on the "Enhancements" 'Destination' sheet.

    Projects
    • If a text string is found in column E on the 'Source' sheet, which does not contain "DIR", "Enhancements", "IND" or "OVH",
    • The value from this column is copied, creating a unique distinct list so the same value is not repeated, and
    • Pastes this into column B on the "Projects" 'Destination' sheet.

    In addition to all of the above, when the text values are copied, the associated 'Man hour' figure from column I on the 'Source' sheet is summed, and placed under the relevant monthly column in each of the 'Destination' sheet.

    I'd now like to change this to include multiple inter dependent 'Unique Distinct Value' lists, so the following will occur, but I've been struggling in finding a way to do this.:

    DIR
    • If the text string "DIR" is found in column E on the 'Source' sheet,
    • Copy the values from both columns D and B, create a unique distinct list for both, so the same value is not repeated, and
    • Paste the value from column D into column B and the value from column B, to column C on the "Direct Activities" 'Destination sheet

    Enhancements
    • If the text string "Enhancements" is found in column E on the 'Source' sheet,
    • Copy the values from column E and column B, create a unique distinct list for both, so the same value is not repeated, and
    • Paste the value from column E into column B and the value from column B, to column C on the "Enhancements" 'Destination sheet

    Projects
    • If a text string is found in column E on the 'Source' sheet, which does not contain "DIR", "Enhancements", "IND" or "OVH",
    • Copy the values from columns D, B and F, create a unique distinct list for all three, so the same value is not repeated, and
    • Paste the value from column D into column B, the value from column B to column D and the value from column F to column C on the "Projects" 'Destination sheet

    I would also like to maintain the current functionality which sums the total 'Man hours' figure.

    I appreciate that there is a lot to understand here, but I just wondered whether someone may be able to offer some guidance on how I may achieve this, and I'm hoping the attached file will help to illustrate the current and desired functionality.
    • The 'Source' sheet is called "All Data".
    • The sheets whose name do not include the word "Output" are the sheets which use the existing code.
    • Those sheets which do contain the word "output" show how I would expect the data to be shown with the change in code.
    • To run the macro click the button on the "Macros" page.

    Many thanks and kind regards
    Attached Files Attached Files
    Last edited by hobbiton73; 09-08-2013 at 08:48 AM.

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Dependent Unique Distinct Lists

    Hi hobbiton73

    Tried running your existing Code to see what the Code does so as to modify it...some Worksheets are missing...Code won't work.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Dependent Unique Distinct Lists

    Hi @jaslake, firstly, thank you for taking the time to reply to my post, secondly, my sincere apologies for posting the file which didn't work. I've now re-attached a working file.

    Once again, my apologies.

    Many thanks and kind regards
    Attached Files Attached Files
    Last edited by hobbiton73; 09-08-2013 at 08:49 AM.

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Dependent Unique Distinct Lists

    Hi hobbiton73

    In what Version(s) of Excel does this Code need to run? Your Profile shows Excel 2003 but you attached an Excel 2007 (.xlsm) File.

  5. #5
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Dependent Unique Distinct Lists

    Hi @jaslake, thank you very much for coming back to me with this.

    I've recently migrated to Excel 2013, but I've not had chance to update my profile.

    So it would be good if this would work as a .xlsm file please.

    Many thanks and kind regards

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Dependent Unique Distinct Lists

    Hi hobbiton73

    Interesting challenge...try the Code in the attached "Sub Extract_Detail()"...it appears to do as you require.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Dependent Unique Distinct Lists

    Hi @jaslake, thank you so very much for taking the time to put this together, it's incredible.

    Unfortunately, when I run this though, I receive the following error: Run-time error '91' object variable or With variable not set, and debug highlights this line as the issue:
    Please Login or Register  to view this content.
    I can confirm that I have made no changes but only saved the file.

    Many thanks and kind regards

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Dependent Unique Distinct Lists

    Hi hobbiton73

    Are you getting the error on your Sample File or another File?

    The error you describe would indicate the Code could not find a match. Check the Format of the Dates in Column H of AllData and in the Header Row of the OutPut Sheets. The Date Formats must be consistent. In the Sample File they are Custom Formatted as mmm-yy.

  9. #9
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Dependent Unique Distinct Lists

    Hi @jaslake, thank you very much for coming back to me with this so quickly.

    I'm receiving the error on the file you provided. All I've done is download the file, saved it, then tried to run it, so I' not sure where the issue resides. The error however does occur in the 'Projects' script, so I'm not sure if that helps.

    Many thanks and kind regards
    Last edited by hobbiton73; 09-10-2013 at 10:33 AM.

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Dependent Unique Distinct Lists

    Hi hobbiton73

    I've run and rerun the Code several times in both Excel 2007 and Excel 2010 without error. The File you have should be the same File as I have. However, I'm going to ask you to upload it so I can run the Code in your File.

  11. #11
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Dependent Unique Distinct Lists

    Hi @jaslake, thank you very much for your continued help with this.

    Please find the file attached.

    Kind Regards
    Attached Files Attached Files

  12. #12
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Dependent Unique Distinct Lists

    Hi hobbiton73

    The File runs without error on my platform...so, I don't know...

  13. #13
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Dependent Unique Distinct Lists

    Hi @jaslake, thank you for this. As you say, it's a little strange.

    I'll have to look at this.

    Many thanks and kind regards

+ 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. Validation Lists - Multiple dependent lists with unique values
    By Lewigi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2013, 06:42 AM
  2. [SOLVED] Multiple dependent lists with unique results
    By Jeepster325 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-05-2013, 01:14 PM
  3. Replies: 7
    Last Post: 03-30-2012, 06:38 PM
  4. unique distinct counts.
    By albert28 in forum Excel General
    Replies: 2
    Last Post: 03-11-2010, 06:58 AM
  5. distinct lists
    By dlythgoe in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-12-2009, 02:01 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