With COUNTM I can determine multiple selections are made, how can I extract the specifc selections?

Paul Newcome

Ok. What exactly is the end goal for this data? Why do you need it broken down like this?

Ann Marie Shields

@Paul Newcome we created a map of the US. On that map we want to show that New York has Project D and Project G. What we found is that because the states are multiple select it does not translate into showing that. We created the map in BI that pulls the data from Smartsheet.

Each project has their own sheet. We created a report that pulls in all the rows from each individual project sheet. We have attempted to set this up like a database.

Thank you!

Paul Newcome

Instead of doing a row for each project and then listing out the states, what if you did a row for each state and listed out the projects?

Ann Marie Shields

@Paul Newcome so would each project be their own column? So something like?

Project A Project B Project C Project D

Alabama X

Florida X X X

Georgia X X

Paul Newcome

I meant what you already have but flipped around.

State.............Projects

Alabama.........Project A

Florida............Project A, Project B, Project C

Georgia..........Project A, Project B

Ann Marie Shields

@Paul Newcome I am open to trying that. When all is said and done I just need to be able to show all projects for a particular state. Right now, my map only shows projects where there is only 1 state linked to it. Because it cannot read the other states in the cell because they are multiple select.

Paul Newcome

Ok. Then what you could do is create your State column and list out each of the states. Then in the Projects column you could use something along the lines of...

=JOIN(DISTINCT(COLLECT({Current Project Column}, {Current State Multi-Select Column}, CONTAINS(State@row, @cell))), ", ")

Since each state is only listed once and the corresponding projects are listed out as a single string, you should be able to get what you need out of this.

Ann Marie Shields

@Paul Newcome when I try to add in the formula you provide I am getting a blank cell.

This is what my sheet looks like. I pulled in the first 3 columns from another sheet. I added the "State list" like you mentioned and of course the last is the formula column.

The first formula: =JOIN([Primary Column]1:[States/ Territories Reached]1, ",")

The second formula: =JOIN(COLLECT([Primary Column]2:[States/ Territories Reached]2, [Primary Column]2:[States/ Territories Reached]2, [Count of States]2 > 0), ",")

The third formula: =JOIN(DISTINCT(COLLECT([Primary Column]3:[States/ Territories Reached]3, [Primary Column]3:[States/ Territories Reached]3, [Count of States]3 > 0)), ",")

The fourth formula: =JOIN(DISTINCT(COLLECT([Primary Column]4,[States/ Territories Reached]4,CONTAINS([State list]@row,@cell))),",")

There are subcategories to the projects so Project A may have Project A1, A2, etc.

We want our map to be able to say Delaware has Project A and Project D.

I am certain I am missing a big piece to this.

Thank you for all your help!

Paul Newcome

Do not use row numbers in the JOIN formulas. You want to evaluate the entire Primary Column based on the entire States/Territories Reached column.