Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA macro for Vlookup,pivot and separate files

Posted on 2016-07-14
17
Medium Priority
?
100 Views
Last Modified: 2016-07-20
Dear experts i have some tedious task of creating multiple files below is what I am looking for some automation

Column A to K is the Input file in Raw Data tab
Step1: Column L have to get updated by picking all the information before"." of Column J
Step2:  Column M have to get updated by vlookup from the mapping file however if I add more rows in Mapping file it have to get updated
Step3: Column N have to get updated by picking all the information after"." of Column J
Step4:  Column O have to get updated by vlookup from the mapping file however if I add more rows in Mapping file it have to get updated
Step5:  Column P have to get updated by vlookup from the mapping file however if I add more rows in Mapping file it have to get updated

Once the Raw data sheet is ready it have to create a Pivot as shown in the example

and create separate files based on the sales unit name


Thanks a ton in advance!!!
file.xlsx
0
Comment
Question by:Nirvana
  • 8
  • 7
  • 2
17 Comments
 
LVL 34

Expert Comment

by:Rob Henson
ID: 41710902
If separate sheets will suffice rather than separate files, this is fairly simple to achieve without VBA.

1) Convert your data to a Table - select any cell within the raw data and press Ctrl + T to create Table. This should show the data area to be converted and should just need OK.  When new entries are added to the table the formulas in L to P will copy down automatically.

2) For your lookups change the formula to look at whole columns on lookup sheet, any additions are then already included.

3) In your Pivot select Sales Unit as a Report Filter/Page Value, top left pane at the bottom of the Fields List navigator pane. When the Pivot has the Page Filter set go to the Pivot Table Tools Ribbon and select Options tab. At the left hand end there is an Options button with a dropdown. Don't click the Options button as this just opens the Table Options window. Select the dropdown and choose "Show Report Filter Pages...". This opens a Window and will show a list of item for which you have Report Pages, it should be just Sales Unit. Click OK and a new sheet will be created with a Report for each Sales Unit. If you then want each of these as a separate file, use the Right Click menu on the tab to Move or Copy.

Thanks
Rob H
2
 

Author Comment

by:Nirvana
ID: 41711496
Hi Rob, thank you for quick reply. while the information is sensitive and cannot be shared with others I was looking for an individual files and wouldn't be a macro be helpful for cutshort all other steps as well.
thank you very much again
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 41711759
Steps 1 & 2 would be one off tasks and then add new data as required.

A macro could be used to refresh the pivot and create the individual files.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41716016
Please find the attached workbook with the code and click the button on Raw Data Sheet to run the code to see if this is what you were trying to achieve.
file.xlsm
0
 

Author Comment

by:Nirvana
ID: 41716369
Hi Neeraj,

Thank you very much. works perfect. just a couple of more changes

1. In pivot how can I uncheck subtotal for each acronym
file.jpg2. Based on the sales unit a separate file have to be created with pivot with Sales unit name as filename(Column M)

Thanks again
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41716386
Okay check the attached.
The code will save the files for each Sales Unit at the same location where the macro workbook is saved.
file.xlsm
0
 

Author Comment

by:Nirvana
ID: 41717008
Thank you again. sorry not making it very clear. Would need rawdata for individual Sales Unit along with pivot.

attached is the sample outputfile
GK00G0K-_Africa.xlsx
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41717055
Hmm. Please see the attached.
file.xlsm
0
 

Author Comment

by:Nirvana
ID: 41717117
its working brilliant what i am looking for. one last question when i do a vlookup for a formula it is giving #N/A error how do I paste special as values after the formula is copied till last row example if sales unit is only number it is not picking the vlookup and I am trying to create files based on "Sales Unit Name"

thank you
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41717226
Please find the attached to see if this is what you were talking about and trying to do.
file.xlsm
0
 

Author Comment

by:Nirvana
ID: 41717490
if i have to change filenames to sales unit names how can I change and i am get the pivot in the final file but raw data i get only heading
0
 
LVL 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41717516
Please refer to the attached.
file-v6.xlsm
0
 

Author Comment

by:Nirvana
ID: 41718252
Sir I am getting a debug error at line

fileName = WorksheetFunction.Index(sws.Range("M:M"), WorksheetFunction.Match(str(i), sws.Range("L:L"), 0))

and getting all pivots in one file
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41718968
It shouldn't happen. All the Sales Units must be available in col. L if the value exits in the page filter.
When you debug the code, hover your mouse over str(i) inside the Match function, what value populates then? And check if the sheet for that Sales Unit is created with Pivot Table.

It is hard to debug the code without having the workbook. If possible please upload the workbook with data in question.
0
 

Author Closing Comment

by:Nirvana
ID: 41720262
you have been providing brilliant solutions. thank you so much. really appreciate. thanks again
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41720967
You're welcome Kiran! Glad to help.
1
 

Author Comment

by:Nirvana
ID: 41721037
Thanks a lot. I could which only i could provide you more points.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question