Combine two rows based on a unique identifier?

Kyle Cannella

So my smartsheet randomly receives two entries with the same unique identifier with different rows filled out. For example, my smartsheet has the columns "machine", "material", and "program". entry 1 has the identifier xyz and fills out the "machine" and "material" columns. entry 2 has the identifier xyz and fills out the "program" column. I'd like to combine both of these rows, with the identifier xyz, into a single row so that the row has data for all three of these columns.

Is this possible? I saw a previous answer that used the join and collect functions (How to combine multiple rows of data into one row based off of one cell value. — Smartsheet Community) but I don't think that would work for my case since it would receive new identifiers every day.

Dale Murphy

@Kyle Cannella From where do these rows originate? There are some ideas, but most will depend on how the data arrives in the sheet.

For example, if Entry 1 is first, automation could send an update request to whoever is responsible for the program value and ask for it to be filled out. Submitting an Update Request would put that new data onto the existing row.

dm

Kyle Cannella

https://community.smartsheet.com/discussion/comment/332231#Comment_332231

The goal here is to have 2 separate forms. Trying to achieve a work around, because the update request doesn't allow for a dynamic form (i.e. selecting 1 dropdown value shows or hides other dropdowns).

The way I have it set up right now is having 2 sheets, a master sheet and a review sheet. We receive data from 1 form that inputs data into the master sheet. An automation sends that filled out row to the review sheet. A second form is filled out via the review sheet with the same unique identifier that the row from the master sheet has. Now, we have 2 rows with the same identifier but each are missing data that the other contains. From here, I'd like to combine these two rows based on if they share the unique identifier, and this row will be exported back to the master sheet.

Dale Murphy

@Kyle Cannella I would recommend using two sheets with forms to front the process, and using the master sheet to collect data from each. (so three sheets in all)

But I am not clear on how you manage to have the same unique id in two different sheets, and how those are part of the form submission.

dm

Kyle Cannella

Essentially, our process is 4 steps but only 2 of these steps require dynamic forms (the first two).

What I want to do is have the first form be created with the user coming up with a unique ID (such as first initial last initial date and report number in that day, so for me it'd be kc67221 for my first report).

The process would go like this (simplified since its a mess). As mentioned previously, User 1 would would fill out machine and materials. They also create a unique ID for this report. From here, it'd send an alert email to User 2 requesting a update, but instead of the normal update request, it'd send a link to the review sheet form and show the data that the previous user submitted into the master sheet. So the email would show the link to the review sheet form in the text portion of the email then it'd show the machine, the materials, and the unique ID that User 1 used in their submitted report. User 2 would fill out the rows in the review sheet form and type User 1's chosen unique ID for this report. Since there are now two rows with the same unique ID, these rows would be combined.

I have everything working except the formula to combine the two rows based on the unique ID. Hope this helps.

Kyle Cannella

Managed to get it to work, thanks for the help though! If someone wants an explanation, I can provide one.

Ctrlaltdelete.tom

Yes please share. I believe I may be able to use this myself

Christina W

Definitely share your solution, please! We are trying to work this exact process right now!

sbdoubleaa

I encountered a very similar issue, and I am still looking for a process to enable this using one sheet. That being said, I do have a bit of a workaround I can share:

Create two smart sheets

Sheet 1 is the initiator sheet with a basic form, it collects the shared unique identifier and a handful of other basic project info.

Sheet 2 is the main database sheet where all collected project data will live. Build a robust form to collect all other data you need from the user.

When the form from Sheet1 is submitted, automation kicks out an email to the contact who submitted the form (they are required to put their email in Form 1). In that email is a customized URL link to the Sheet2 master Form, but with a pre-populated Unique ID field. This form field is hidden when the Form from Sheet2 opens so they cant change it accidentally. When they submit Form2, I use an Index/Match as column formulas in Sheet2 for all the Sheet1 fields that need to be pulled over, relying on the Index/Match logic to grab the info thats relevent to the UniqueID.

Here is my formula for the column, it is built in a column in Sheet1

"http://Sheet2 Form URL" + "?Project%20Number=" + ProjectNum@row

This adds the project number for the row in question to the end of the URL. Then, in the automated email, it includes a cell reference to that custom URL for that row.

Here is my Index/Match formula

=INDEX({RangeOfFieldToPullOver}, MATCH(ProjectNum@row, {RangeOfProjNumToLookAt}, 0))

The key is to NOT copy the data from Sheet1 to Sheet2 using automation, otherwise you end up with duplicate lines for the same Unique ID. Building the custom URL allows you to include the right info for the new line in Sheet 2. Then use Cross-Sheet formula attributes to pull the info from Sheet1 into Sheet2, relying on the Unique ID shared in both. Just remember to set your formulas to be column formulas after building the initial formula.

Hopefully this helps!