Very tricky pivoting/formula question
Dear community,
I hope somebody can help me with what I believe is a pretty tricky problem.
I have a table that contains line items with many columns, but only 3 of them I am interested in for this use case.
It looks like this:
Column 1 Column 2 Column 3 (multiselection)
Process 1 FW1 E1, E3, E65
Process 2 FW2 E1, E4, E67
Process 3 FW2 E1, E3, E67, E80
....
The sheet has about 400 lines.
I now basically want to pivot the table to give me information based on the "E's" (Column 3). So I would like to know for all E's what is the corresponding information from column 2.
My target is to find out for all instances of "E"...so E1E80, what the corresponding instances of FWs. Either in a table listing them, in a pivot chart, a dashboard, not matter what.
Any ideas on how to achieve this?
Thanks and all the best,
Daniel
Answers

On a separate sheet you can reference the entire columns then use the index(collect()) formula to drag out each instance you are looking for. Then you can use formulas on that sheet to get your pivot information.
If you want to chart/get row data and don't' need any calculations you can just use a report with some filters and maybe a helper column on the original sheet to get the information.

I am thinking a table that has all of the "E" values listed then cross sheet references in a COUNTIFS with CONTAINS.
E Value.................Count
E1
E2
E3
E4
E5
etc. etc.
Then in the Count column a formula such as
=COUNTIFS({Main Sheet Column 3}, CONTAINS([E Value]@row, @cell))
Displaying it on a dashboard in a chart widget would show all "E" values and their counts including the ones with zeros. If you wanted something a little more dynamic that only showed a count of 1 or greater and left the zero counts off of the dashboard chart, you would adjust the formula to an IF statement that says "if the count is greater than zero, display the count, otherwise leave blank" like so...
=IF(COUNTIFS({Main Sheet Column 3}, CONTAINS([E Value]@row, @cell)) > 0, COUNTIFS({Main Sheet Column 3}, CONTAINS([E Value]@row, @cell)))

Wow....thank you guys very much. I will look into the suggestions and update you on the outcome.
Help Article Resources
Categories
Check out the Formula Handbook template!