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

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.

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

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.

Hi hobbiton73

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

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.

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

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.

Hi hobbiton73

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