copying a row twice and giving each row a unique identifyer

Hilbert Kok

Hi I have a created an audit form that can have 2 action items. To control each action item individually, I want to copy the row to a separate sheet with a unique identifyer. I'm able to copy the row 2 times based on a workflow condition. the copied rows are identical twins. Because both action information is in one row I need to remove the information of the first action to prevent the user to be confused. i have tried it with formulas but because I don't have a unique identifier I can't separate the data. does anybody have a tip for me?

Thx

HIlbert

Andrée Starå

Hi @Hilbert Kok

I hope you're well and safe!

Could you use the Auto-Numbering column?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jeff Reisman

If the moves are done at different times, In your second sheet (where the rows are being moved to,) you could use the system column "Created," which has the full date/time that the row was moved, as a unique identifier, or to create a unique identifier by stripping some of the data from the date/time value.

For example, if Created on the row is 09/30/21 6:04 AM, create a helper column called "CreatedText" to change it to text by adding a + sign and a pair of double quotes:

=Created@row +""

Then build a Unique Identifier text string based on criteria:

=MID(CreatedText@row, 7, 2) + LEFT(CreatedText@row, 2) + MID(CreatedText@row, 4, 2) + IF(MID(CreatedText@row, 11, 1) = ":", "0"+MID(CreatedText@row, 10, 1)+MID(CreatedText@row, 12, 2), MID(CreatedText@row, 10, 2)+MID(CreatedText@row, 13, 2)) + RIGHT(CreatedText@row, 2)

Which will result in "2109300604AM". Add some value from another field in the row (perhaps your Action) and you'll have a truly unique row.

=Action@row + MID(CreatedText@row, 7, 2) + LEFT(CreatedText@row, 2) + MID(CreatedText@row, 4, 2) + IF(MID(CreatedText@row, 11, 1) = ":", "0"+MID(CreatedText@row, 10, 1)+MID(CreatedText@row, 12, 2), MID(CreatedText@row, 10, 2)+MID(CreatedText@row, 13, 2)) + RIGHT(CreatedText@row, 2)

Hilbert Kok

Hi guys, thx for your response,

I have tried both but it won't work because the row is copied twice at the same time and as such it has the same date stamp.

This the result:

# Row- id created Jeff's formula

2 40 28-01-22 15:46 222801154646

2 41 28-01-22 15:46 222801154646

3 43 28-01-22 15:56 222801155656

3 44 28-01-22 15:56 222801155656

I thought of another idear. If i look at the content of the copied field I could compare the content with an if/then statement. Like if row 1 = row 2 then 1 else 2. The problem is that colomn formulas only allows @row and not @47 for example. Is there a way to use like @row + next row?

cheers

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317772#Comment_317772

2 40 28-01-22 15:46 222801154646

2 41 28-01-22 15:46 222801154646

3 43 28-01-22 15:56 222801155656

3 44 28-01-22 15:56 222801155656

What the values in BOLD ITALIC? Where are they coming from?

Andrée Starå

@Hilbert Kok

Happy to help!

Why doesn't the Auto-number solution work? It should because the number would be unique. (it should be added to the Destination sheet)

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Hilbert Kok

Hi guys, really appreciate your feedback.

Hi Jeff the number in bold is the Row ID .

Hi Andrée, the Row ID indeed creates a unique number, but How do I then, based on the unique number, which cell I need to use. I'll explain a bit more in detail.

step 1, Form is filled with for example 5 actions, with action description, owner and due date, This is then submitted into sheet

step 2, In sheet I have now one row with the 5 actions, which makes it difficult to manage for the user, so I have created a workflow that will copy the row to a new sheet 5 times.

Step 3. I know need to have each row (with the same information) to copy the action information into a central action field (in the row) so the user only has to look at one set of fields.

I can't get this to work, the Row ID add's one number for every row added, But I don't know which number belongs to which row, so I cant create a workflow or a formula.

I got it to work for max 2 lines using ISODD and ISEVEN to identify the uniqueness.

=IF(ISEVEN([Row ID]@row); [action 1]@row; IF(ISODD([Row ID]@row); [action 2]@row; ""))

Using this for more then 3 it won't work. 3 actions will have either 2 odd and 1 even or 2 even and 1 odd, etc.

So i'm stuck, I have implemented now max 2 actions but the user would like to see max 5 actions.

I hope this makes it a bit more clear.

Hilbert

Andrée Starå

@Hilbert Kok

What about adding an auto number as an ID on the first page?

Would that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!