How to combine multiple rows of data into one row based off of one cell value.

BCK Team

Hello Smartsheet's community!

I am back with another question. I have a database that holds the tracking of employee trainings. The training structure is in tiers and after each completed tier they fill out a form. The raw data from this forms looks like this:

Depending on an employee's level they will have a differing amount of rows entered.

Example: L1 employees will have 2 rows filling in all of the green columns.

I am looking to create a spreadsheet that combines/concatenates all of this data into one row based off of the "Alias" value.

Example: "Test 1" employee only has 1 row filling all green columns with "trained".

End result to look like this:

The reason for needing this is I am trying to create a dashboard that shows each employees training completion and if/what training's they are missing.

As always appreciate the help!

Thanks :)

Toufong Vang

Here’s one approach. Use the functions JOIN() and COLLECT() in your new sheet.

You’ll need to COLLECT everything from the training column in the raw data worksheet (Sheet1), where the alias in the Alias column matches the alias found in the new sheet (Sheet2). And then JOIN the results.

•  JOIN( COLLECT( {Sheet1 COL1} , {Sheet1 Alias} ,  [Alias]@row ) )
• JOIN( COLLECT( {Sheet1 COL2} , {Sheet1 Alias} ,  [Alias]@row ) )
• JOIN( COLLECT( {Sheet1 COL3} , {Sheet1 Alias} ,  [Alias]@row ) )
• JOIN( COLLECT( {Sheet1 COL4} , {Sheet1 Alias} ,  [Alias]@row ) )
• JOIN( COLLECT( {Sheet1 COL5} , {Sheet1 Alias} ,  [Alias]@row ) )

In the new worksheet (Sheet2), create data references for each of the columns that you’ll need from the source sheet, Sheet1.

To do this, right-click anywhere in Sheet2 and select Manage References… from the pop-up. Then create the references you’ll need.

• Sheet1 Alias would be the entire “Alias” column.
• Sheet1 COL1 would be the next column. In your environment, COL1 would be the column labeled, “Level”.
• Sheet1 COL2 would be “Dish”.

And so on…

Once the references are created, enter the formula,

 = JOIN(COLLECT( {Sheet1 COL1} , {Sheet1 Alias} ,  [Alias]@row ))

 into the first column after the Alias. Then subsequently,

 = JOIN(COLLECT( {Sheet1 COL2} , {Sheet1 Alias} ,  [Alias]@row ))

 …and so on.

Cheers!

Reference Smartsheet functions here: https://help.smartsheet.com/functions

Paul Newcome

Is it possible to have Alias = "Test 1" and then have both L1 and L2 in the Level column for that same alias? If so, would you have it broken down that way in your End Result Sheet?

Toufong Vang

Here’s one approach. Use the functions JOIN() and COLLECT() in your new sheet.

You’ll need to COLLECT everything from the training column in the raw data worksheet (Sheet1), where the alias in the Alias column matches the alias found in the new sheet (Sheet2). And then JOIN the results.

•  JOIN( COLLECT( {Sheet1 COL1} , {Sheet1 Alias} ,  [Alias]@row ) )
• JOIN( COLLECT( {Sheet1 COL2} , {Sheet1 Alias} ,  [Alias]@row ) )
• JOIN( COLLECT( {Sheet1 COL3} , {Sheet1 Alias} ,  [Alias]@row ) )
• JOIN( COLLECT( {Sheet1 COL4} , {Sheet1 Alias} ,  [Alias]@row ) )
• JOIN( COLLECT( {Sheet1 COL5} , {Sheet1 Alias} ,  [Alias]@row ) )

In the new worksheet (Sheet2), create data references for each of the columns that you’ll need from the source sheet, Sheet1.

To do this, right-click anywhere in Sheet2 and select Manage References… from the pop-up. Then create the references you’ll need.

• Sheet1 Alias would be the entire “Alias” column.
• Sheet1 COL1 would be the next column. In your environment, COL1 would be the column labeled, “Level”.
• Sheet1 COL2 would be “Dish”.

And so on…

Once the references are created, enter the formula,

 = JOIN(COLLECT( {Sheet1 COL1} , {Sheet1 Alias} ,  [Alias]@row ))

 into the first column after the Alias. Then subsequently,

 = JOIN(COLLECT( {Sheet1 COL2} , {Sheet1 Alias} ,  [Alias]@row ))

 …and so on.

Cheers!

Reference Smartsheet functions here: https://help.smartsheet.com/functions

BCK Team

Toufong Vang

This is incredibly helpful thank you so much for the detail!!

GMichal

Is it possible to do this same type of combining data in one sheet (the same sheet) by matching the primary column names?

I have a situation where a sheet tracks the progress of prospects all of the way thru becoming a dealer thru an onboarding process. But, when a prospect looks promising, we send them an application where, with external automation, we bring in the application data fields into that same Smartsheet as a new row. That Smartsheet would then have a row of all of their prior prospect data that needs to be joined with their new application data.

The external automation capturing the data from the application does not have the capability of matching the new application data to the already existing data by the prospect name so hoping to do it in Smartsheets.

Paul Newcome

@GMichal Are you able to provide some sample screenshots?

GMichal

The Smartsheet that I am working with is quite large so I created a sample sheet to describe the situation. In a nutshell, I would like to figure out how best to combine/merge duplicate rows (identified by the primary column data) into one combined row (prefer in one of the existing rows. In the example, Prospect A, B, and C were entered into the sheet manually but, the extraction software solution that I am using can only create a new row of data; not fill-in the missing data of an existing row.

As such, I am looking for automation ideas or a formula in Smartsheets to take both Prospect A rows (row 1 and row 4) and combine them into one like is shown on row 6. Any ideas would be welcome.

Paul Newcome

I would suggest having that extraction solution populate a sheet separate from the sheet in your screenshot then using a formula with cross sheet references such as INDEX/MATCH to pull the data over.

GMichal

Paul, excellent idea and one that I have done in the past with success. Sometimes being too close to the problem blinds you to the possibilities. Thank you for this great advice.

Shreeg

Hello,

I am stuck with one of the same isssue.

We have 3 forms and submitted data saves in 1 master table. So what i want to do that when entry come from the same name lets consider from all the 3 form we got the entry. Then i want to combine all form name and that user name into one row.

I have no idea how to achieve this, Please help attached is the screenshot, See the last 3 entry from diff form but with the same user shree.