Excel question - sorting data from one worksheet into multiple worksheets

Factual Questions
1

Is there a way to do a slicer or a sort to take a spreadsheet that contains sales data for multiple accounts and copy each account’s data onto a separate tab?

Thanks!

StG

2

I know the VLOOKUP function works across different tabs in a spreadsheet, so maybe you could set up all your tabs ahead of time, and use the VLOOKUP function to only grab the data pertinent to that tab?

3

I’m not getting that to work. I can VLOOKUP on the account number, but it only pulls the first hit on the master sheet. Maybe there’s a different way you’re talking about?

StG

4

Create a PivotTable on a new worksheet (tab), copy that worksheet for each sales account, then filter by the desired sales account on each worksheet.

5

Troutman - Yep. that would work. Not as efficient has the (non-existent) function I had in my mind, but definitely doable.

StG

6

I have a similar situation, in which accounts are split by a value in Column A, and I output a workbook for each unique value, using VBA.

I could give you some code tomorrow if you’re interested, but it basically sorts by column A, then looks through the rows for matching values, and copies those rows into a new spreadsheet. Doing it with tabs instead would be easier, in most ways.

7

This is one of those things that Google Sheets might be better at. It’s super simple:



=filter(range, account_id=12345)

Example: Accounts In Multiple Tabs - Google Sheets

Excel is better for some things (visualizations, for example), but its data parsing functions are relatively primitive compared to Google Sheet’s. You can import your Excel sheet and see if Google works better… it’s free.

8

I don’t know if the suggestion will work, but did you have multiple worksheets selected rather than just the master sheet?

9

I did something similar not too long ago and ended up writing a macro in VBA to do it. I can find the code if you’re interested. This one took a list of records and sorted them to worksheets based on what State they were for (Georgia, Iowa, etc).