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

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 01/30/20

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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!