Data extraction and formatting using macro

**4gurus** · 01-22-2014, 07:34 AM

Hi Team,

I am not good at macros and need your help desperately. I tried learning but really couldn't succeed in it as the subject is vast and need lot of time.

Here is what I am looking for what macro should do:

To start off macro should pick selected columns such as owners and their projects, start date, price Final price, priority and status columns and paste into new sheet

At the same time only pick selected owners(Dave, Brian, Ken, Russell, Ben)data along with their high low, and medium projects then format the price and the final price column (that is to first convert them to numbers, then change it to the currency format and set the decimal place to 0) and then sort owners (AtoZ) along with Price and final price columns (largest to smallest) into a new workbook and to a new sheet in the same workbook with a name to the sheet called Extracted data

Please note owners may change and new members might get included in the list ( I am not sure if something dynamically can be done about this in the macro)

I have included sample data and how the output should look like....Please help

Thank you,

4gurus

**jaslake** · 01-22-2014, 12:06 PM

Hi 4gurus

Your profile shows Excel 2007 & 2010 yet you posted .xls Files. What Versions of Excel must the Code accommodate?

**jaslake** · 01-22-2014, 01:37 PM

Hi 4gurus

The Code in the attached has been tested in Excel 2003 and 2007 and appears to do as you require. I'm not certain of the Sort Sequence as I'm not able to follow your description.

CTRL + z will fire the Code and Output will be written to the same Folder where the Sample File lives..

**4gurus** · 01-23-2014, 09:28 AM

Hi Jaslake,

The macro works brilliantly however missing few criteria

1. It should pick only selected columns and those are projects, start date, price Final price, priority and status totally 6 columns instead of 8. Sorry I didn't get that right in the output file I shared.

2. For the selected owners it should only pick high, low, and medium in priority column and not NA

Sorry about the file xls format you can definitely have the macro in 2007 format.

Thanks again Jaslake you are a star

4gurus

**jaslake** · 01-23-2014, 07:00 PM

Hi 4gurus

Replace the Module1 Code with this...still not certain of your Sort Requirements

Please Login or Register  to view this content.

**4gurus** · 01-27-2014, 04:51 PM

Hi Jaslake

Thanks for the editing the macro; after testing I realized that there will be addition to columns in the future so wondering if you can actually make a form where I can pick and choose the columns one would like to have in output file just like you did for the owners which I can pick and choose.

Coming to the formatting part I was actually referring to the custom sort; first for the owners and then for Price and Final price

Thanks again Jaslake

**jaslake** · 01-27-2014, 05:11 PM

Hi 4gurus

Why don't you SHOW me the Final Product sorted so I don't have to guess. Leave the Owners Column in the Final Sorted Product, I'll delete it later.

I'll need to look at this

make a form where I can pick and choose the columns one would like to have in output file

**4gurus** · 01-28-2014, 04:49 AM

Hi Jaslake,

I am extremely sorry for not being clear on what Macro should do, but now I have clear understanding of what macro should do..

Macro Conditions:

First macro should give user form to choose columns, then user form to choose Category(IT&Telecom), then user form for Region, then Priority(Low high medium), and then user form for Owners.

Please note that these column's could be anywhere in excel sheet for instance category and Region column could be in column AA and Aj respectively in real-time environment and same with other columns

Formatting condition

First sort owners A to Z then Sort Price (Largest to smallest) by going to sort by color > custom sort > add level> then price usd.

Note: this Price column could be anywhere in excel sheet.

Hope I am clear this time. If not I will try to come up with better examples...Sorry again

**jaslake** · 01-28-2014, 11:06 AM

Hi 4gurus

What version(s) of Excel must the Code accommodate?

Are Column Headers ACTUALLY in the First Row in your "real-time environment" Workbook?

Does the Workbook reflect the ACTUAL Structure of your "real-time environment" Workbook (knowing that the Column Headers may not be in the same order)?

Are these the ACTUAL Column Names...
Category
Region
Priority
Owners
Price USD
Final Price USD

Regarding this...are these the Columns the User wishes to SEE in the Final Report?

give user form to choose columns

If so, it's my understanding the User will ALWAYS SEE Category, Region, Priority, Owners, Price USD and Final Price USD in addition to the Columns the User chooses.

Please clarify...

**jaslake** · 01-28-2014, 11:11 AM

Hi 4gurus

...Sorry...duplicate post...

**4gurus** · 01-28-2014, 02:55 PM

Hi Jaslake

Thanks for being patient with me. I have answered to your questions below

What version(s) of Excel must the Code accommodate? Excel 2007

Are Column Headers ACTUALLY in the First Row in your "real-time environment" Workbook? Yes

Does the Workbook reflect the ACTUAL Structure of your "real-time environment" Workbook (knowing that the Column Headers may not be in the same order)? Almost but there are many more columns that I have not included and I might edit few names in the macro if required in the future and if anything changes in the actual structure

Are these the ACTUAL Column Names... Yes they are however under Priority columns which now reads as (high low medium) can change to (Identified, Inprocess, submitted)

Category
Region
Priority
Owners
Price USD
Final Price USD

Regarding this...are these the Columns the User wishes to SEE in the Final Report? Yes, these are some of the required columns again there could be others which I might need which macro should allow to choose from the user form.

give user form to choose columns
If so, it's my understanding the User will ALWAYS SEE Category, Region, Priority, Owners, Price USD and Final Price USD in addition to the Columns the User chooses. Yes

Please clarify...

**jaslake** · 01-28-2014, 04:36 PM

Hi 4gurus

What are the REQUIRED Columns?

ONLY these...

Category
Region
Priority
Owners
Price USD
Final Price USD

**jaslake** · 01-28-2014, 05:22 PM

Hi 4gurus

This is important...if I write the Code for Excel 2007 it won't run in Pre Excel 2007...verify this

What version(s) of Excel must the Code accommodate? Excel 2007

**4gurus** · 01-28-2014, 05:29 PM

Hi Jaslake,

Well along with the above list you mentioned may be old price (formatted to currency).

Note: Columns will be added in the future and its header will be in the 1st row, so user form should give the option to pick and choose column names

For example: In future a header by name "Latam Spend" gets added in Column AC which has currency value the macro should identify this column header and list it in the user form to select this column

Basically I am expecting the macro to allow me to pick the desired columns I wish to see in the output file and then filtering data based on Category, Region, Priority, Owners for which I need the user form as a filtering mechanism.

**4gurus** · 01-28-2014, 05:32 PM

Hi Jaslake,

I will be using this Code in Excel 2007 and above(Excel 2010 or Excel 2013). I hope it will be compatible.
Sorry again for sending sample files in xls format...

**jaslake** · 01-28-2014, 05:39 PM

Hi 4gurus

This will be handled by the Code

In future a header by name "Latam Spend" gets added in Column AC which has currency value the macro should identify this column header and list it in the user form to select this column

Is this a REQUIRED Column or an Optional Column...you tell me...the User will be able to select if so desired

Well along with the above list you mentioned may be old price (formatted to currency).

**jaslake** · 01-28-2014, 05:42 PM

Hi 4gurus

Just a thought...there best not be ANY Column Headers that are blank...

**4gurus** · 01-29-2014, 10:03 AM

Hi Jaslake,

Old price is not mandatory one but yes it should be present in the user form for the users to select it.

I don't think there will be any blank columns

Thanks!

4gurus

**jaslake** · 01-29-2014, 05:40 PM

Hi 4gurus

I don't know...you tell me...

**4gurus** · 01-29-2014, 07:13 PM

Hi Jaslake,

Looks great and works like a charm but few things has to be addressed and tweaked

Firstly, In columns list(userform) I observed that the last column was not captured in the list so when I entered a dummy column beside "Upload" header it showed "Upload" in the column list.

Secondly, In category when I select any of the items the user form list should update itself in region, priority, and owners list

example: Let's say I choose Capital in category list first the user form region list should update and show only APAC as it is only present in APAC and Priority list will update to just high and medum, and the owner list will have just Yuan's name

Similary If I were to click Latam in Region list "first" it should update Category, Priority and Owners list (userform)
Same goes with Owners if I select them first or anything in Priority list.

Thirdly, Let's say I don't select anything in any of the list that is Column, Category, Region, Priority Owners
and click "GO" it should take raw data and have it in the output file.

Hope I am clear this time

and once again wonderful macro work Jaslake.

Thanks!

4gurus

**jaslake** · 01-30-2014, 09:25 AM

Hi 4gurus

Perhaps another can write the Code to allow the User to select any Field at random and update the respective Lists.

What you're suggesting could possibly be done with Dependant ComboBoxes (ComboBoxes are Single Select...not Multi Select). I have neither the desire nor the talent to pursue this approach.

If you wish to take the approach whereby the User selects in a predefined order let me know...I'll work with it.

**4gurus** · 01-30-2014, 10:02 AM

Hi Jaslake,

Sorry for asking a bit too much. I think I should be able to work with it.

I need one last favor from your, that is to add "select all" buttion in the user form option for "Owners list" so that we can select all the names at one go when required.

Thanks!

4gurus

**jaslake** · 01-30-2014, 10:41 AM

Hi 4gurus

This has been fixed

Firstly, In columns list(userform) I observed that the last column was not captured in the list so when I entered a dummy column beside "Upload" header it showed "Upload" in the column list.

A Toggle Button has been added to the UserForm for this

add "select all" buttion in the user form option for "Owners list"

**4gurus** · 01-30-2014, 03:23 PM

Hi Jaslake,

I am sorry to come back because of the names changes that happened to the headers because of which macro threw up debug error

I tried changing it in Macro but could not fix it. You will see the changes I have made.

Names changes are Owner=Project Owner; Region=FOR Deck; Category = CST; Priority = Status

Regarding formatting it is Annualized Spend (USD) and Annualized Save (USD) which has to be in currency format and custom sorted high to low after Project Owneris customer sorted

Please help!!!!!! Jaslake

Thanks!

4gurus

**jaslake** · 01-30-2014, 03:58 PM

Hi 4gurus

Give me a list of all the Column Names you wish to be formatted as Currency (or does it not matter except for the two you mentioned).

**4gurus** · 01-30-2014, 04:53 PM

Hi Jaslake

I have the pasted the column names below which should be formatted to currency. So when ever I select any of these columns below in the column list (userform) and select other required options as required the output file should have the columns I selected (any of the below ones) formatted to currency.

Please note: Most of the columns below don't have numbers in the samplecopy v5 I shared with you; but they are the ones to be formatted when I select them in

Current Year Spend (USD) Current Year Save (USD)

Annualized BCS Spend (USD) Annualized BCS Save (USD) Current Year BCS Spend (USD) Current Year BCS Save (USD)
Annualized HERO Spend (USD) Annualized HERO Save (USD) Current Year HERO Spend (USD) Current Year HERO Save (USD)
Annualized HMT Spend (USD) Annualized HMT Save (USD) Current Year HMT Spend (USD) Current Year HMT Save (USD)
Annualized PS Spend (USD) Annualized PS Save (USD) Current Year PS Spend (USD) Current Year PS Save (USD)
Annualized Forp Spend (USD) Annualized Forp Save (USD) Current Year Forp Spend (USD) Current Year Forp Save (USD)
Annualized AMERICAS Spend (USD) Annualized AMERICAS Save (USD) Current Year AMERICAS Spend (USD) Current Year AMERICAS Save (USD)
Annualized EMEA Spend (USD) Annualized EMEA Save (USD) Current Year EMEA Spend (USD) Current Year EMEA Save (USD)
Annualized APAC Spend (USD) Annualized APAC Save (USD) Current Year APAC Spend (USD) Current Year APAC Save (USD)

Thanks!

4gurus

**jaslake** · 01-30-2014, 05:23 PM

Hi 4gurus

So...tell me about the Sort...I have never been clear.

Based on what you've written I see the Sort being this:

First Sort............. Project Owner in Alphabetic Order (A - Z)
Second Sort......... Annualized Spend (USD) (High to Low)
Third Sort............ Annualized Save (USD) (High to Low)

Is this correct?

**4gurus** · 01-30-2014, 05:29 PM

Hi Jaslake,

Yes that is correct

**jaslake** · 01-30-2014, 05:31 PM

Hi 4gurus

I'll have something for you in a short while.

**jaslake** · 01-30-2014, 05:39 PM

Hi 4gurus

Try the Code in the attached...

**4gurus** · 01-30-2014, 07:48 PM

Hi Jaslake,

This one worked beautifully, but I had to change the column name For Deck to MOR Deck in the actual file as per the request which threw up debug error and the formatting to currency failed for USD columns; I think because of this change I am trying to make.

Could you please change the name of this column to "MOR Deck" and send it back to me. I think we are very very close to finishing this.

In fact I could not change the column name (For Deck) in the excel sheet it said The user has restricted value that cannot be entered

Thanks!

4gurus

**jaslake** · 01-30-2014, 08:03 PM

Hi 4gurus

I ran this through several times...seems to work. Let me know.

**4gurus** · 01-31-2014, 04:53 AM

Hi Jaslake,

This time almost everything worked except formatting.

All I did was copied the numbers and pasted in to the macro file from a data source number file after which the formatting didn't work

Some of the columns in data source number file match with the Macro file but all are not there
like Current year spend and Current year Save and few others in the end.

I have attached all the file you would need.

Let me know what could be wrong.

Thanks!

4gurus

**jaslake** · 01-31-2014, 08:44 AM

Hi 4gurus

You did what?

**4gurus** · 01-31-2014, 09:31 AM

Hi Jaslake,

From the "Data Source Number" file which I have shared with you; I copied Annualized Spend (USD) and Annualized Save (USD) columns' data and pasted into the macro file (samplecopy v5.2) and then ran the macro it worked fine except the Formatting that is changing the columns with USD to currency format and then sorting them.

Similary I have different excel files from which I pick data(just numbers) for columns like Current year spend; current year save and columns like:

Annualized BCS Spend (USD) Annualized BCS Save (USD) Current Year BCS Spend (USD) and many more so when I did that and pasted it into the macro file and ran the macro the formatting failed.

Really could not understand what wrong I could have done.

Thanks!

4gurus

**4gurus** · 01-31-2014, 10:07 AM

Hi Jaslake,

I realized what is wrong when I am copying the those numbers from the column I think they are in the text format which is is why there is a small triangle displayed beside those numbers giving option to convert to numbers.

Please help me in converting those Columns with (USD) to numbers first and then to currency in the macro; I think that should fix the problem.

I think sorting Annualized spend (USD) and Annualized save (USD) will happen automatically.

Thanks!

4gurus

**jaslake** · 01-31-2014, 10:10 AM

Hi 4gurus
This is exactly what's happening...I'm looking for a workaround

I realized what is wrong when I am copying the those numbers from the column I think they are in the text format which is is why there is a small triangle displayed beside those numbers giving option to convert to numbers.

**jaslake** · 01-31-2014, 10:52 AM

Hi 4gurus

See if the attached works for you...

**4gurus** · 01-31-2014, 01:56 PM

Hi Jaslake,

Thanks a ton. You have been extremely patient with my various requests. You have been the Macro Hero for me, just can't imagine this task being accomplished without you.

May God Bless You Jaslake.

**jaslake** · 01-31-2014, 02:13 PM

You're welcome...glad I could help...thanks for the Blessings...need 'em.

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

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Re: Data extraction and formatting using macro

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro for data extraction?

[SOLVED] Listbox selection as a parameter in data extraction macro.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions