Create multiple rows based on data from one cell

Ann Marie Shields

I need a way to count the number of states each project participates in. For example, for Project A I should be able to says West Va and Va. Smartsheet will not allow me to count the state column. So I would then like to be able to separate each project by row based on the state column. I found VBA code to use in Excel to create rows under the original row, but I would prefer not to export from Smartsheet to Excel to go back into Smartsheet to pull in for Power BI.

In total there will be two sheets. The second sheet will pull in from the first sheet.

David Joyeuse

Hi @Ann Marie Shields

If you want to count occurences in the states/territories reached column, use the COUNTM function. But for this to work, you have to make your State/Territories reached column a multi dropdown list.

Then simply use:

=COUNTM([States/ Territories Reached]@row)

On the next column.

Ann Marie Shields

Hi @David Joyeuse

I was thinking about doing that in order to know how many additional rows I would need for the next sheet.

I would still need to find a way to create the additional rows with the same information except for the different states.

So with the new column I would create this

Then based on the states count column I would need to create the additional rows with the same information in column A but with each specific state for column B. Like this. Is there a formula or something I can add that would allow me to do this for the second sheet?

Thank you for responding!!

David Joyeuse

@Ann Marie Shields

Currently, the best option I would have is to do it baackwards than what you are doing right now.

I would suggest on your second sheet to add a form with two columns:

• Project Name
• States/Territories Reached. (Single Dropdown list)

Each time you submit a form, the lines will add at the end of the sheet. Just sort the sheet by Project then States/Territories reached.

Then add 3 columns: (That you may hie at your convenience)

• Full States List. (Multi dropdown list should be working here)
• States Count
• Identical (which will be a flag type hidden helper column.

On Full States List use this formula:

=JOIN(DISTINCT([States/Territories Reached]:[States/Territories Reached],[Project Name]:[Project Name],[Project Name]@row),"; ")

This will collect distinct States for each project in case you got some noted twice.

On States Count:

=COUNTM([Full States List]@row)

In the Identical column:

=IF(COUNTIFS([Project Name]1:[Project Name]@row,[Project Name]@row,[Full States List]1:[Full States List]@row,[Full States List]@row)>1,1,0)

For each row, it will calculate if this line already exists above. If yes, it will raise a flag. You can hide this column.

Then, create a report out of this sheet and display lines where Identical =0 and limits it to cells: Project Name, Full States List and States Count.

Then in the future, each time you have a new project or an existing project hits a new state / territory, simple add it using the form, and everything else will sort itself out :)