Data Shuttle when they are more than one line for the Unique ID
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.
Best Answer
-
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)))
Answers
-
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
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.
-
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?
-
And @jmyzk_cloudsmart_jp thank you for your help! You're amazing!
-
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)))
-
Grad that you figured it out! 😁
SmartsheetCategories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Trending Posts
- I have a Dynamic View that is filtered using the "Restrict view by sheet filter" so the view only shows a certain users assigned projects. Each project has one or more resources. The "resources" colum…
- Hey! This automation was working fine - but now that I've ensured that as i look to leave the company we have better owners, i can't select this sheet. They're in 2 workspaces, which wasn't a problem …
- Under the User Subscription Model, can internal collaborators designated as viewers (not members or guests), enter data into a dynamic view? Our group is still on the Legacy Collaborator Model and use…
