Data Shuttle when they are more than one line for the Unique ID

Annaleah Morrow

I have a source file with a mess of data. I pretty much have it cleaned up so that for each of my phase dates I have a raw data file that I can export and attach into my clean sheet. The problem is that once it hits the first row with the matching opportunity ID# (unique field) it stops matching. I need it to go to the next one. I'm fine if it only gives me the first instance in each column, but out of the 10 options it only gave me the first two columns (first instance). Ideas on what I can do within Data Shuttle? Mesh is not an option at this time.

Below is the source file. I would be totally happy if it took the first occurrence for Plan Begin/End, A/C Begin, End, etc. It brought in Plan Begin/End only.

Annaleah Morrow

I figured out how to get the target sheet to pull up the values to be across the whole row with this value in Plan Phase Start:

=IF(CONTAINS("Plan", Phase@row), [Start Date]@row, INDEX([Start Date]:[Start Date], MATCH([OPP #]@row, COLLECT([OPP #]:[OPP #], Phase:Phase, CONTAINS("Plan", @cell)), 0)))

jmyzk_cloudsmart_jp

Hi @Annaleah Morrow

I devised the solution of creating a unique identifier for each stage of an opportunity on both the source and target sheets.

As I tested, you can use a formula to create the unique identifier both at the source and target, as long as the formula at the target sheet is a cell formula.

Sample Source Google Sheet

https://docs.google.com/spreadsheets/d/183CaieCGvmMrWbUmk3Vwx3y7m6EZ8cHYGznSAJWelD4/edit?usp=sharing

Sample Target Sheet

Smartsheet

As I tested in the Data Shuttle field mapping, I needed to include the unique identifier column. (Ie, if I left it as "Not mapped", the mapping did not work)

This test showed that I needed to set the unique identifier on the sheet, not a column formula, as I needed to include the field in the mapping. If I use the column formula, the mapping sets the column as "Not mapped," which seems logical because we cannot update the value if there is a column formula.

Annaleah Morrow

I think we are getting closer. I have ID & Stage on the source file and the target sheet; however, the target sheet has that column blank - because it isn't a calculated field, and I'll need it to find the line on the target sheet based on the OPP# which is the truly unique connection between the two. I think I can get it to populate once if I add lines; however, any time new rows are added to the source file it won't match up and I will keep having to manually move that data up. Is this making sense?

Annaleah Morrow

And @jmyzk_cloudsmart_jp thank you for your help! You're amazing!

Annaleah Morrow

I figured out how to get the target sheet to pull up the values to be across the whole row with this value in Plan Phase Start:

=IF(CONTAINS("Plan", Phase@row), [Start Date]@row, INDEX([Start Date]:[Start Date], MATCH([OPP #]@row, COLLECT([OPP #]:[OPP #], Phase:Phase, CONTAINS("Plan", @cell)), 0)))

jmyzk_cloudsmart_jp

Grad that you figured it out! 😁