How to populate duplicate numbers into sheet?

santiago.rendon

Hello,

I have one main sheet that tracks all incoming DRR #'s and every row has a couple of names in it.

I want to build another sheet that tracks names based on the DRR # but want it to populate in different rows, like below. So two people are on 0001 while in the main sheet that would only be one row

jmyzk_cloudsmart_jp

Hi @santiago.rendon

The following method expands a row with multiple names under a single DRR # into separate rows for each name using only formulas in Smartsheet.

1. Count the number of names in each row.
   In the source sheet, convert the list of names (separated by a delimiter like #) into a multi-line format using SUBSTITUTE, then count how many names are listed using COUNTM.
2. Calculate the cumulative name count.
   Use COLLECT and SUM to create a running total of the COUNTM values. This cumulative count will serve as a reference index for assigning names to new rows in the expanded sheet.
3. Create a second sheet with enough rows.
   In the expanded sheet, generate row numbers starting from 1 up to a number large enough to accommodate the total possible number of names across all rows in the main sheet.
4. Assign the correct DRR # and corresponding names.
   Based on the row number and cumulative values, use INDEX and COLLECT to assign the correct DRR # and retrieve the list of names from the main sheet.
5. Extract each name individually.
   Use string functions like MID, FIND, and SUBSTITUTE to extract one name at a time from the full name string, according to the current row's position among the matching DRR # entries.

1st sheet formulas (1-3)

[Mulitple Dropdown] =SUBSTITUTE(Names@row, delimiter#, CHAR(10))
[CountM] =COUNTM([Mulitple Dropdown]@row)
[Cumulative] =SUM(COLLECT(CountM:CountM, Row:Row, <=Row@row))

https://app.smartsheet.com/b/publish?EQBCT=7991fc76b3a74bccb88db70e954d8b16

2nd sheet formulas (4-5)

[Row] : Row numbers from 1 to a large number like 100.
[DRR #] =IFERROR(INDEX(COLLECT({DRR #}, {Cumulative}, Row@row <= @cell), 1), "") *
[Name] =IFERROR(MID([old_text]# + Names@row + [old_text]#, FIND([new_text]#, SUBSTITUTE([old_text]# + Names@row + [old_text]#, [old_text]#, [new_text]#, Index@row)) + 1, FIND([new_text]#, SUBSTITUTE([old_text]# + Names@row + [old_text]#, [old_text]#, [new_text]#, Index@row + 1)) - FIND([new_text]#, SUBSTITUTE([old_text]# + Names@row + [old_text]#, [old_text]#, [new_text]#, Index@row)) - 1), "")
[Names] =JOIN(COLLECT({Names}, {DRR #}, [DRR #]@row))
[Index] =IF(ISTEXT([DRR #]@row), MATCH(Row@row, COLLECT(Row:Row, [DRR #]:[DRR #], [DRR #]@row)))

(*)

1. Look at the current row number in the expanded sheet.
2. Compare it to the cumulative counts from the main sheet. These totals show how many names exist up to each original row.
3. Find the first cumulative total that is greater than or equal to the current row number. That tells us which original DRR # this row belongs to.
4. Return that DRR # so you know which group the current name should fall under.

https://app.smartsheet.com/b/publish?EQBCT=b9754fec2d3b4eb1b1fb1f61c5c1b553

Georgie

Hi @santiago.rendon,

You can use formulas in helper columns and multiple automations to copy rows to your destination sheet, copying them the same number of times as there are names in each row (e.g. if DRR0001 has three names assigned to it in the main sheet, the row would be copied three times). However, it’s not currently possible to extract particular names or values from a multi-select column, so there’s no way to automatically have one name copied for each row.

To clarify, you can have the rows copied the same number of times as there are people assigned to the row, but each row that’s copied to your other sheet will contain all names that are in the original row on the main sheet. 

I’d recommend adding your vote to this existing product idea: Add function to retrieve values from cell for a multi-select column.

If you’d like to copy all the rows with all names listed in each row, take a look at this related thread for my explanation on how to do so: Copy rows to another sheet multiple times. 

In your case, you wouldn’t need a “Workdays” column and can incorporate your count into your checkbox column formulas - so you can skip step 1.

Step 2 will be to add 3 helper checkbox columns in your main sheet: “Copy rows x1”, “Copy rows x2”, and “Copy rows x3”, and you would use the following formulas in these columns:

• Copy rows x1: =IF(COUNTM(Name@row) >= 1, 1, 0)
• Copy rows x2: =IF(COUNTM(Name@row) >= 2, 1, 0)
• Copy rows x3: =IF(COUNTM(Name@row) >= 3, 1, 0): 

If you’re likely to have more than 3 names per row, you’ll need to create another checkbox column per extra possible name and add the formula, incrementing the first number in the formula.

Step 3 in the thread I’ve linked will be the same. Again, if you’re likely to have more than 3 names per row, you’ll need to create another automation per extra possible name. 

Hope that helps!

Georgie

santiago.rendon

@Georgie

That's unfortunate that it won't really work out the way we would like it to but I do appreciate your help.

I think the easiest way to do it now would be having columns with everyone's name and index/match with the DRR#, and not duplicate the DRR #'s

Paul Newcome

It sounds like this thread may be helpful:

https://community.smartsheet.com/discussion/119380/split-multiple-selections-in-single-form-into-individual-rows-core-product/p1

santiago.rendon

@Paul Newcome Thank you! I read through it and I really would not like to use the copy row workflow for this to work, my main sheet that this would be copying from is around 30 columns deep and I think would get confusing very fast if I copied all of it to a helper sheet, I'm also on a bit of a time crunch but might try it out once I'm not as busy.

Basically from those 30 columns, I just want to take DRR #, and then 3 columns that have peoples names

Paul Newcome

You would be able to hide all of those extra columns on the target sheet to keep the sheet from being too cluttered.

Another approach that would allow you to specifically copy the data from those few columns without having to worry about the extra columns would be through the API or the premium add-on Bridge.

jmyzk_cloudsmart_jp

Hi @santiago.rendon

The following method expands a row with multiple names under a single DRR # into separate rows for each name using only formulas in Smartsheet.

1. Count the number of names in each row.
   In the source sheet, convert the list of names (separated by a delimiter like #) into a multi-line format using SUBSTITUTE, then count how many names are listed using COUNTM.
2. Calculate the cumulative name count.
   Use COLLECT and SUM to create a running total of the COUNTM values. This cumulative count will serve as a reference index for assigning names to new rows in the expanded sheet.
3. Create a second sheet with enough rows.
   In the expanded sheet, generate row numbers starting from 1 up to a number large enough to accommodate the total possible number of names across all rows in the main sheet.
4. Assign the correct DRR # and corresponding names.
   Based on the row number and cumulative values, use INDEX and COLLECT to assign the correct DRR # and retrieve the list of names from the main sheet.
5. Extract each name individually.
   Use string functions like MID, FIND, and SUBSTITUTE to extract one name at a time from the full name string, according to the current row's position among the matching DRR # entries.

1st sheet formulas (1-3)

[Mulitple Dropdown] =SUBSTITUTE(Names@row, delimiter#, CHAR(10))
[CountM] =COUNTM([Mulitple Dropdown]@row)
[Cumulative] =SUM(COLLECT(CountM:CountM, Row:Row, <=Row@row))

https://app.smartsheet.com/b/publish?EQBCT=7991fc76b3a74bccb88db70e954d8b16

2nd sheet formulas (4-5)

[Row] : Row numbers from 1 to a large number like 100.
[DRR #] =IFERROR(INDEX(COLLECT({DRR #}, {Cumulative}, Row@row <= @cell), 1), "") *
[Name] =IFERROR(MID([old_text]# + Names@row + [old_text]#, FIND([new_text]#, SUBSTITUTE([old_text]# + Names@row + [old_text]#, [old_text]#, [new_text]#, Index@row)) + 1, FIND([new_text]#, SUBSTITUTE([old_text]# + Names@row + [old_text]#, [old_text]#, [new_text]#, Index@row + 1)) - FIND([new_text]#, SUBSTITUTE([old_text]# + Names@row + [old_text]#, [old_text]#, [new_text]#, Index@row)) - 1), "")
[Names] =JOIN(COLLECT({Names}, {DRR #}, [DRR #]@row))
[Index] =IF(ISTEXT([DRR #]@row), MATCH(Row@row, COLLECT(Row:Row, [DRR #]:[DRR #], [DRR #]@row)))

(*)

1. Look at the current row number in the expanded sheet.
2. Compare it to the cumulative counts from the main sheet. These totals show how many names exist up to each original row.
3. Find the first cumulative total that is greater than or equal to the current row number. That tells us which original DRR # this row belongs to.
4. Return that DRR # so you know which group the current name should fall under.

https://app.smartsheet.com/b/publish?EQBCT=b9754fec2d3b4eb1b1fb1f61c5c1b553

santiago.rendon

@jmyzk_cloudsmart_jp Thank you! In my main sheet, the names are listed in multiple columns for different stages of our projects. Would I just have to use "=SUBSTITUTE(Names@row, delimiter#, CHAR(10))" in every column with a name in it?

santiago.rendon

@jmyzk_cloudsmart_jp , this worked, i used a join formula to grab all the names in the different columns, and then continued on with your help!