Forum Discussion
Solved
How to count multiple values in a cell
Hello,
This is the first time I post!
I need to do the following. I have a dataset and one of the fields is made up of cells that can have multiple values. For example the below. We have cells that contain zero, one or multiple names and I would need to count those names like in a pivot table. These names are separated by an "enter". In the example below I put the desired output.
| 2019 | 2018 | |
| Class 1 | John Mary | Mary Joseph Matthew |
| Class 2 | Theresa Anthony | Matthew Margaret |
| Class 3 | John Theresa | Theresa Margaret |
Desired output:
| Number of Classes | ||
| Person | 2019 | 2018 |
| John | 2 | 0 |
| Theresa | 2 | 1 |
Thanks in advance!
Ugarte335 I used Powerquery to massage your data as you can see in the attached file. From the green table it'll be easy to create your pivottable report. You can also feed the result of the query into the pivottable directly without using the intermediate green table of course.
Ugarte335 I used Powerquery to massage your data as you can see in the attached file. From the green table it'll be easy to create your pivottable report. You can also feed the result of the query into the pivottable directly without using the intermediate green table of course.
Thank you, this was much easier than I expected!
Just for the collection, here is a bit another version of query, resulting table is in brown.
