Sort data by color thro Pivot table

Hi All,

Hope everyone is safe & doing well.
I have a excel sheet as a Progress tracker for multiple products manufactured at different sites and i need to track the activity related to gathering of data for a Risk assessment exercise.

so basically, for any product you have the information in columns as below:
- Product
- Product configuration (volume in ml or mg)
- Site (at which the product is produced)
- Pack format (e.g.- bottle, vials etc.)
- Pack details (specs of the pack format)
- RPT no (Database no. which needs to be looked thro for the below mentioned details):
- DPQ (sort of questionnaire)
- Specs (just indicating whether the specs are available in full (green), if data is in WIP then it is amber colored, if not available or pending, then it is Red
- Suppliers (1,2,3 indicating different suppliers - all cells against these suppliers would turn green, amber, red, depending upon the data they are required to furnish for risk assessment - details are mentioned in the excel sheet)

So, basically am looking to create a summary of the data (may be a pivot table); where I should be able to see the data sorted by color for respective products (with ther respective sites). There might be cases where for a specific site there could be multiple products.
Also, I am looking to get the count of the progress for columns H thro L , meaning, DPQ, Specs & Suppliers (either could be completed (which means green color) is in WIP(which means Amber color) or Pending(which means Red color))

Hope I was able to explain myself in a proper manner.

I have attached sample excel sheet with some example data.

Please help/support.

Thanks,
Mahesh

Hello Excel experts/ Moderators,

Does this post have to be moved to Excel Charts & Pivot table? If so, kindly help and move the post to the appropriate forum.

Thanks,
Mahesh

Hi Croweater,

The idea is good, but i am unable to follow the count theory. When i press the sort by color button across each columns; the count changes and sometimes weirdly with numbers, 1, 44, 999.

I am shortly posting a mock-up solution in the thread continuation; to get better clarity.

Thanks anyways for your time & efforts.

Mahesh

Hi all Experts,

I have now attached my file with some minor changes and also added a Summary tab (which is a mock-up of the solution I am looking for).

Please go through and suggest if the solution is possible as I have mentioned in the Summary tab.

Thanks,
Mahesh

My apologies...please find the updated file attached.

Originally Posted by MaheshK5277

Hi Croweater,

The idea is good, but i am unable to follow the count theory. When i press the sort by color button across each columns; the count changes and sometimes weirdly with numbers, 1, 44, 999.

I am shortly posting a mock-up solution in the thread continuation; to get better clarity.

Thanks anyways for your time & efforts.

Mahesh

What count theory? There is no count.

The numbers you see is the colour number of each cell (row) in that column! That is what it sorts on. Forget that column, I thought I hid it anyway.

And of course it changes...that's what 'sorting' does!

Dear,

Can anyone help/support please. Sharing the attachment once again; if missed by you all, where i have described the mock-up solution which I am looking for?

Thanks in advance