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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!