Solved

VBA macro for Vlookup,pivot and separate files

Posted on 2016-07-14
17
92 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
17 Comments
 
LVL 33

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 33

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

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 31

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 31

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 31

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 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 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 31

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 31

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

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

624 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