Convert a table [Name]x[Technologie] into a table [Name]x[Level]

stefandoo

Hello,

I'm having trouble converting a table like this one:

into a table like this one (values within both examples are illustrative and aren't meant to match):

I need to build a database with only 4 columns (level 1 to 4) inside which I must store every technology that the person described in [First and last name]@row knows at the described level.

I've tried combinations of JOIN(COLLECT()), JOIN(INDEX(COLLECT())) and such and no success until now.

If you know a workaround using excel directly to clean the data let me know please.

Thank you in advance for your help!

Stefan

Courtney S.

I don't know how to do this in Smartsheet, but I know a way to do it in Excel. It's maybe not the most efficient but it's something someone else showed me and I just kept using the same method. It looks like the Column1 doesn't matter for the final table, so I deleted that column and just kept Column2.

First use Power Query. Select the whole table. Add the table to Power Query. In current Excel versions, that is the Data ribbon, and the From Table/Range option.

Then select all the columns listing people, and use Transform > Unpivot Columns.

Then use File > Close & Load to get a new table with a row for each unique person+tech+level answer. I renamed the columns. You must re-sort the table to sort by name and then by level.

Then I use a couple formulas to concatenate the tech answers for each name+level combination, and mark if it's the final row of that combination.

The formula for H2 is =IF(CONCAT(F2:G2)=CONCAT(F1:G1),CONCAT(H1,";",E2),E2)

The formula for I2 is =IF(CONCAT(F2:G2)<>CONCAT(F3:G3),"Yes","no")

Filter the table to only the rows where Final row = Yes. Copy only 3 columns to a new table: Name, Level and Tech concat. Select the whole table and load the table into Power Query. This time, select only the Level column and then go to Transform > Pivot Column. Change the default settings so that the Values Column is the Tech concat column, and the "Aggregate Value Function" answer is "Don't Aggregate." Once you use the File > Close & Load option again, you'll have a table like your final goal.

Scott Orsey

Hi, This is not an easy task. The first table isn't particularly friendly within SS. The second is how I would want the data structured in SS. It would be helpful to understand the use case a little better.

For me, these types of activities happen when I'm trying to upload data into SS in the second format from an output file from some other system that looks like the first format. If this is the case for you, I would do most of the data processing within Excel, then upload the finished product. If it's less than 500 rows, you can just paste into SS. If it's more than 500 rows, you'll need to import the Excel and then move the data to your destination sheet.

I'd need to play with this a bit to get it to work in either Excel or SS. My first instinct, though, is to transpose the data (paste special) in Excel so that the names are going down the left column and start from there.

I hope this helps.

Be well

Courtney S.

I don't know how to do this in Smartsheet, but I know a way to do it in Excel. It's maybe not the most efficient but it's something someone else showed me and I just kept using the same method. It looks like the Column1 doesn't matter for the final table, so I deleted that column and just kept Column2.

First use Power Query. Select the whole table. Add the table to Power Query. In current Excel versions, that is the Data ribbon, and the From Table/Range option.

Then select all the columns listing people, and use Transform > Unpivot Columns.

Then use File > Close & Load to get a new table with a row for each unique person+tech+level answer. I renamed the columns. You must re-sort the table to sort by name and then by level.

Then I use a couple formulas to concatenate the tech answers for each name+level combination, and mark if it's the final row of that combination.

The formula for H2 is =IF(CONCAT(F2:G2)=CONCAT(F1:G1),CONCAT(H1,";",E2),E2)

The formula for I2 is =IF(CONCAT(F2:G2)<>CONCAT(F3:G3),"Yes","no")

Filter the table to only the rows where Final row = Yes. Copy only 3 columns to a new table: Name, Level and Tech concat. Select the whole table and load the table into Power Query. This time, select only the Level column and then go to Transform > Pivot Column. Change the default settings so that the Values Column is the Tech concat column, and the "Aggregate Value Function" answer is "Don't Aggregate." Once you use the File > Close & Load option again, you'll have a table like your final goal.