Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
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
-
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
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
