split multiple items to isolated columns in a new grid

147smartsheet

Hi,

In a main grid called 'Grid A', one column contains multiple items selected from a drop down list. For a display purpose, I would like to have these items shown in individual columns (one item sits in a column) in a new grid, called 'Grid B'.

Would someone please share idea to sort it out?

Thank you.😊

Bassam Khalil

Hi @147smartsheet,

could you please add a screenshot for Grid A & Grid B to help me to create this formula for you after you remove any sensitive Data.

147smartsheet

Hi @Bassam Khalil ,

Thank you for replying.

As the photos shown, the main grid is 'Minor Project Tracker Live', colum 'High Risks (Next 2 wk)' has a drop down list of values. For example, a project QFBP0050.0 has 3 values in this column.

In a new grid 'Sub report - test', I would like to list these 3 values to individual columns 'High Risk Activity 1', 'High Risk Activity 2'& 'High Risk Activity 3' in the corresponding line.

Bassam Khalil

Hi @147smartsheet,

please try the following:

1- in "Sub report - test" sheet create helper column call it "High Risks (Next 2 wk)" and add the following formula on it and convert it to column format formula to collect the multiple risks from the "Minor Project Tracker Live"sheet:

=IFERROR(INDEX(COLLECT({High Risks (Next 2 wk)}, {Project ID}, [Project ID]@row), 1), "")

2- use the following formula for [High Risk Activity 1] in the "Sub report - test" sheet" to subtract the first Value and convert it to column format formula:

=IFERROR(LEFT([High Risks (Next 2 wk)]@row, FIND(CHAR(10), [High Risks (Next 2 wk)]@row) - 1), "")

3- use the following formula for [High Risk Activity 2] in the "Sub report - test" sheet" to subtract the first Value and convert it to column format formula:

=IFERROR(MID([High Risks (Next 2 wk)]@row, FIND(CHAR(10), [High Risks (Next 2 wk)]@row) + 1,FIND(CHAR(10),
[High Risks (Next 2 wk)]@row, FIND(CHAR(10), [High Risks (Next 2 wk)]@row) + 1) - FIND(CHAR(10), 
[High Risks (Next 2 wk)]@row) - 1), "")

4- use the following formula for [High Risk Activity 3] in the "Sub report - test" sheet" to subtract the first Value and convert it to column format formula:

=IFERROR(MID([High Risks (Next 2 wk)]@row, FIND(CHAR(10), [High Risks (Next 2 wk)]@row, FIND(CHAR(10),
 [High Risks (Next 2 wk)]@row) + 1) + 1, LEN([High Risks (Next 2 wk)]@row)), "")

and here is a screenshot shows the result when i test the soluation: