Separate raw data to it's own worksheets within same workbook and summarize

Good morning all.  I'm looking for a way to automate a task I find myself repeatedly doing even though the first time it was asked a few months ago, it was a one-off thing.  NOT!  :-)

  1. I extract data from our systems via Excel.
  2. All raw data is dumped to a single worksheet in Excel as shown in the attached spreadsheet.
  3. I need to separate data based on company name column and move only that company data (all rows and all columns, even if date columns might be empty) to it's own worksheet within same workbook and that worksheet name being the company name for that worksheet by running a macro, or by clicking a button with said macro attached to it.
  4. I will start with a properly formatted "raw data" workbook with only the rawdata worksheet in it each time I do this and save it out as it's own "dated" file when I'm done

My issue is i'm not good with Macros and I don't think recording a macro would do me much good here because the amount of data in the raw data worksheet will in almost every case be different in row size but not column size.  Meaning I could have 20 rows for Company A, 5 rows for Company B, 18 rows for Company C, and so on -  this round and those numbers of rows be completely different next time.  The columns will always stay the same and contain same data points.

Also, each worksheet will need to summarize the following to be able to be replicated via a bar chart graph on a final and separate worksheet.

Company Name
Sum of Volume in $ for that company
CountA for Date Quoted Column
CountA for Date Sold Column
Percentage of Date Sold and Date Quoted for "pull through"

Any help would be appreciated.
Todd WSystems AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Why not use a PivotTable? You can double click on a total to create a sheet of the data that makes up that total
Todd WSystems AdministratorAuthor Commented:
Roy, I think I can work with that, yes.  However i'm missing something very obvious.  How are you getting the % calculation column (sum of field1)?  No matter what i set that values to (bottom right box) of pivot field options, I get erroneous data.
Roy CoxGroup Finance ManagerCommented:
It's a calculated field.

This explains in detail

Excel Pivot Table Calculated Field

  1. Basically, if you select a cell in the pivot table, and on the Excel Ribbon, under the PivotTable Tools tab, click the Options tab (Analyze tab in Excel 2013 onwards).
  2. In the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.

You will be able to see how I have set up the calculation

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Todd WSystems AdministratorAuthor Commented:
perfect.. thank you sir.. that'll do nicely what I need
Roy CoxGroup Finance ManagerCommented:
Pleased to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.