Thursday, November 6, 2014

yogi_Recognizing Multiple Values Within A Single Cell In A pivot Table Type SetUp

                 Google Spreadsheet   Post  #1823
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Nov-06-2014
post by  Mike_McDaniel:
Recognizing multiple values within a single cell
I am using Google Sheets as an event tracking tool. Each row represents a specific item and tracks its progress. There are a few cells in each row that houses multiple data. I would like to be able to reference this data separately while still keeping it in a single cell. Any advice would be welcome. I have included an example of a row below:

IDTypePriorityProductReleaseAdditional RFARelease 2Date ReportedRef NumberNeed by dateAgency
Example Product
4.14a, 4.14a-14.15a, 4.16a7/7/2014419209/1/2014New York, Wyoming, California

In the Release, Release 2 and Agency columns I have multiple entries.

I have a sample sheet ready to share. Should I post it here or share it with specific posters?

Answers to your questions:

I am trying to create a summary pivot table that identifies and separates multiple entries in one cell. The second sheet contains the pivot table I am trying to create. Keep in mind the sample data is only a fraction of the data contained in the original sheet.

In my sheet the first column represents a specific record number. That number has relates to a specific problem. In some cases I have specific customers reporting against the same number. I have to track each agency. When we have a resolution we send it to the customer for validation. We track whther the resolution "Passed", Failed" is currently being "Validating" or if no response was received "Not received".

I'd like the Pivot Table to identify each agency and list the TMRs it reported. Then the pivot table would identify each validation status. Ideally, the pivot table would need to look at the each cell in the Agency column and separate multiple entries if they exist accordingly. For example:

TMR     Agency                                                   Valid Status
1234     Agency 1, Agency 2, Agency 3                  Validating

Results in a pivot table that looks like this:

Agency                            Valid Status
                              Passed    Failed     Not Rec    Validating
Agency 1
     1234                                                                     1               
Agency 2
     1234                                                                     1
Agency 3
     1234                                                                     1

I certainly welcome any help you can give me.