Very tricky pivoting/formula question

Daniel Kunkel

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 E1-E80, 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

L_123

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.

Paul Newcome

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)))

Daniel Kunkel

Wow....thank you guys very much. I will look into the suggestions and update you on the outcome.