Multi-Level Data extraction interface

Hi Jaslake

This time I have a request which is very similar to my previous request; however this time I have few more conditions to add.

Hope you will be my angel again.

What Macro Should do:

On pressing ctrl+z on keyboard

It should bring up the dialog box with a section for column names(where I can pick and choose columns to have in the sheet) and beside this column a series of different column names with its unique values again to pick and choose(basically show all column sections with its unique values)

Just like you did in the attached sheet but this time instead of selected columns; it will be for all the column headers with its unique values and under each of these sections an option to select all and deselect all.

So the idea is to filter based on the selected columns and its unique values to an output sheet
just like you did last time. Just that it should be dynamic in terms of column names and the data under it.

Factors to consider:
1. The header names will always be in first Row
2. All the column header names are variables, so all these column header names could change anytime and the values under these columns
3. The dialog box that will be created should be movable. In the current attached sheet when you press ctrl+z the user interface dialog box that comes up is immovable; wondering if you can address that
4. Format of all columns wondering if you can keep it "General" or which ever you think is the best as columns will include text, numbers, and dates.

Note: The file will grow and both vertically(columns names change and new one gets added) and horizontally

As usual relying on your great macro skills. Hope I am making sense to you..

Kind Regards,

4Gurus

Did you try messaging jaslake directly? He may not even look at this post.

Hi Jaslake,

Yes, something like that; I am not sure how feasible it is, but my objective is get this feature dynamic.

In the excel sheet attached I believe there are 79 column headers, but if I were to use this macro in a different excel sheet which has like 10 or 12 columns headers the macro should be able to give me the option to pick the columns we want in the output sheet and all the columns headers list with its unique values under the respective column header name to filter along with tick box to select all and de-select all under each of column header section. Hope you are able to visualize my need.

The whole idea is to make it dynamic; the interface, again I am not sure if this is going to be a herculean task for you.
If yes, then please recommend other workaround or ideas you might have to make this task dynamic.

Thanks!

4gurus

Hey no worries Jaslake, I have couple of small request which you can address in the attached excel sheet.

1. Like you have "Select All Owners" under Project Owner List can we have the same for Columns like "Select All Columns"
2. When we press ctrl+z the box that comes up can that be made movable, because if one wants to take a peek at another sheet it is just not possible to see what columns they need to select.

Thanks!

4gurus

HI Jaslake,

I noticed a new error in the macro output file especially in the annual spend and annual save columns, and I am not sure if it is happening with other USD columns

The error is when we pull the data for any of the Owners the annual spend and save number doubles up.

I have attached the documents for your reference.

The sad part is I didn't notice this error for so long and I had to redo all the data extraction manually.



Thanks!

4Gurus

Hi Jaslake,

I tested it right away and the new concern is that

when I select any of the columns along with cst, project owner, mor deck and status, the output file is simply not pulling any of the columns.

My other request is can you also add Select all button under columns list.

Hi Jaslake,

I think in the process of sharing the example of how annual the spend doubles up, I might have given you a different picture of how the output file would look.

In the attached document I have manually shown how the output file should be keeping below selections in mind

If I select All Column headers under column list, Select IT under CST, Select Americas Under MOR deck, High under status, and Ross Under Project Owner.

Hope I am clear this time.

Again thanks a ton for taking the pain to rewrite the macro really appreciate that.

Lastly, would like to see a button for select all owners.

Thanks!

4gurus

Hi Jaslake,

Yes, the columns are Project Owner, Status, MOR Deck, CST, Annualized Spend (USD) and Annualized Save (USD)

So the above columns should always be present along with the other columns chosen by the user.

Thanks!

4gurus

Hi Jaslake,

You are correct it will be first project owner, then spend and then save. I just brought Project ID into picture just to show how annual spend and save numbers were doubling up which you have fixed I believe and you shared that in your previous posting.

Now the two requirements are:
When we select any of the column header names from the columns list it should show up in the output file and then need
buttons under column list like; (Select all deselect all) is what I am looking forward to see.

I have attached the previous file you posted where the box is movable to different sheets and numbers don't double up for annual spend and save; I am sure that other column headers which have numbers under them won't double up too after you make the necessary changes.

Thanks!

4gurus

Hi Jaslake,

Thank you so much works beautifully; however, I will be testing it for few more of days and then mark this post as solved. As usual very grateful for your help and very much appreciate your patience

I just have another request related to the same file you posted but it is not complicated (which I believe).
The requirements are:

Using the same file you posted we just need column list; when we press ctrl+z the box should come up and have only columns list showing up and buttons like "select all", reset, and cancel should be present.

In this column list all the column headers should be shown under the column list and the interface box(ctrl+z) should be movable like you have already done in the last post

The other condition is that if any new columns gets added anywhere the first row of the sheet the user interface(ctrl+z box) should show it in column list so when selected it can be extracted to the output file.

In short every thing is similar to the previous file samplecopy v6.1.xlsm you posted but this time only column list is required and all the column headers under it.


Thanks!

4gurus

Alright Jaslake will do as you say...will be marking this post as solved in few days and will start a new thread

Thank you for sorting this out.

Thanks!
4gurus

Hi Jaslake,

I have started the new thread and below is the link to it; please help me with it. Will close this thread shortly.

http://www.excelforum.com/excel-prog...ml#post3695363

Thanks!

4gurus

Thanks a ton Jaslake. I am marking this thread as resolved.

Please help me with the below post which I have created as per your request.

http://www.excelforum.com/excel-prog...ml#post3695363

Thanks!

4guru

Hi Jaslake,

I am extremely sorry to bug you

Do you think anybody else who can help me with this request.

Thanks!

4gurus