Large database separation

[Sfields]

I have a large comma delimited file that i want to setup into a multi-worksheet spreadsheet file. When I was handed the project......I set it up very quickly into a pivot table and sent it back to my boss. All the users would have to do is choose their location(s) from a pivot table filter drop down. Simple right.....Apparently not simple enough. He wants it to be a copy paste for the non-excel person into an input tab and a report for each location on separate tabs. OK I can setup a mapping or V-lookup formulas to pull the information, but (and here is my question) How do I pull in multiple records into a concise, well formatted report when the records for each tab's report are scattered throughout the report. In other words, the report may have a thousand rows and only 5 rows pertain to one facility. I have a unique identifier for each facility as a field in the file. Also, the size of the report will fluctuate from month to month. Is there a formula that will pull multiple records scattered throughout a large database and line them up in a nice neat concise report ??

 

[SpillerBD]

If you set up the Pivot Table to provide all the details needed ( Use Tabular Format, No Subtotals, No Grand Totals) you can duplicate your location field for the purpose of using the Page Filter.
Then, use the Pivot Table option, "Show Report Filter Pages..." to create a worksheet for each location.

As far as dealing with the file updates, use the Get and Transform feature linked to the file. Overwrite the file with the new data. and redo the Pivot Table Page Filter process. (You'll add the Helper field in the Get & Transform.)

Should each facility receive a workbook with their own data, not including everybody else's?
The only other thing you might consider is creating a cleaner Pivot Table Style.

 

[Sfields]

That's cool, I didn't know you could create a separate pivot table for each item in a filter with a click of a button.....That's awesome. It names some of the worksheets with the name in the filter, it named others with a number...... if that because some of the filter names were too large ? Also, if I set these up, and new data is introduced, could they be "refreshed" all at once.....In other words, if the person maintaining this report copies in the new data each month, could they simply refresh the main pivot table would all of the pivot tables refresh or would they have to go into each one and refresh them individually ?

 

[SpillerBD]

Yes, the filter name was either too long or contained invalid characters for a worksheet name.
All the Pivot Tables will run off the same cache, so when the cache is updated, all the pivot Tables should update. Refresh ALL. boom!

Since you are working with comma delimited file, I figured you were working with some export file.
You can have a single file that is updated, or if you do incremental updates you have the addition text files saved in the folder and use Power Query to read all those CSV files and combine into a single database. Load to Table or to Data Model.

 

[Sfields]

I'm putting this together for someone else to maintain and they sent me the delimited file already in column format. This person has very limited excel skills. I'm going to give them simple instructions.....Delete the existing data in the report, copy and paste the new data in the same place, go to the Pivot table tab and click refresh. I will set everything else up behind the scenes. What you showed me will save me a TON of time !!!! thank you very much.

 

[Sfields]

I have two issues. One, I have to stack the data into multiple rows to get all of the elements of the report that I want. Is there a way to either show only the final row or a way to pull fields with text (such as names) into "values" Two, when I change out the data after creating worksheets for every location, if a location has no data for that month, the sheet for that filter reverts to the next filter option with data. can I stop that from happening and have worksheets without data that month just show blank ?