Data Shuttle - Duplicate Rows with Key Identifier

Options

Can Data Shuttle gather info from multiple excel rows and add the that info to one row in Smartsheet, like the image below?

Best Answer

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓
    Options

    HI @Dave Werwinski

    From what I know on data shuttle, it cant do this directly, but in my mind their may be a two part work around.

    I would suggest that if you ran a data shuttle to a helper sheet that received all copied rows in, you could then replicate the required result in the third sheet as illustrated below using formula. Although would just be a string of text not multi select, i.e.

    Column 178 and 179 would be on sheet 2 receiving the Data Shuttle, Columns 181 and 182 would be on sheet 3 delivering the required output. Formula for above is -

    =JOIN(COLLECT([Column179]:[Column179], [Column178]:[Column178], [Column181]@row), ", ")

    In order to populate any new change number into the third sheet you could run an automation from the 2nd sheet whenever a new change number was received that wasn't in the third sheet to copy over the row, this would also accommodate if a new country was added after the fact to a old change number the system and formula would continue to update older entries.

    Not quite the answer you were expecting but hope it may be of some help.

    Thanks

    Paul

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓
    Options

    HI @Dave Werwinski

    From what I know on data shuttle, it cant do this directly, but in my mind their may be a two part work around.

    I would suggest that if you ran a data shuttle to a helper sheet that received all copied rows in, you could then replicate the required result in the third sheet as illustrated below using formula. Although would just be a string of text not multi select, i.e.

    Column 178 and 179 would be on sheet 2 receiving the Data Shuttle, Columns 181 and 182 would be on sheet 3 delivering the required output. Formula for above is -

    =JOIN(COLLECT([Column179]:[Column179], [Column178]:[Column178], [Column181]@row), ", ")

    In order to populate any new change number into the third sheet you could run an automation from the 2nd sheet whenever a new change number was received that wasn't in the third sheet to copy over the row, this would also accommodate if a new country was added after the fact to a old change number the system and formula would continue to update older entries.

    Not quite the answer you were expecting but hope it may be of some help.

    Thanks

    Paul

  • Dave Werwinski
    Options

    That helps, thank you Paul!