Solved

VBA macro for Vlookup,pivot and separate files

Posted on 2016-07-14
17
80 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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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
Comment Utility
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 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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
Comment Utility
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 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
Hmm. Please see the attached.
file.xlsm
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Nirvana
Comment Utility
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 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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
Comment Utility
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 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
Comment Utility
Please refer to the attached.
file-v6.xlsm
0
 

Author Comment

by:Nirvana
Comment Utility
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 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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
Comment Utility
you have been providing brilliant solutions. thank you so much. really appreciate. thanks again
0
 
LVL 28

Expert Comment

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

Author Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This is an Add-On procedure to be used in conjunction with the code provided in Reducing EE Email Clutter using Outlook (http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/A_3146-Outlook-Processing-EE-emails-on-Receive.…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now