Need a solution to move some unique IDs from one sheet to another

Meredith Rhodes

Hello!

I have a main intake sheet for my team to manage studies. Every time a new row is added, a unique identifier (for each unique study on this main intake sheet) is created.

We have at least 3 different services that are listed on the main intake sheet. Each service a study team is requesting is marked with a checkmark on the corresponding study / row.

If a new row is added to the main intake sheet marking ‘budget’ as a service, I would like the corresponding unique identifier to be sent to the budget sheet as a new row. It only needs to be the study identifier so my cross sheet formulas populate the remaining relevant information.

Because there may be more than one budget action for a project on the budget sheet, this identifier may be duplicated on this sheet, but it will never be sent from the main intake sheet twice (where it is truly a unique identifier).

I have tried to create an Automation - but I can't simply copy one cell - it has to be the whole row - and the columns are very different between sheets due to differing process.

I have attempted data shuttle as a solution - but have been getting errors that there are duplicate values in the identifier column. Ironically - this number was created to facilitate cross sheet references, so I'm hopeful I can make this work.

Can you think of another solution?

Thank you! Meredith

Paul Newcome

Are you able to provide some screenshots for context?

Meredith Rhodes

Well, it looks like something I did may have worked. I've been updating the Offload and Upload workflows in Data Shuttle - and it seemed to work this morning. Unfortunately, I don't know what I did that made the difference.

Should data shuttle be able to add a new row to a column that contains values that aren't unique? This will eventually happen on this particular sheet.

Paul H

@Meredith Rhodes

Data Shuttle should work fine, use merge

Then on the mappings page choose your unqiue ID field

Meredith Rhodes

Thanks Paul,

I have a conundrum. I started my auto-numbering with 0001. And data shuttle is converting that to '1' which is messing with my formulas.

Rookie mistake.

Is there an easy way to get data shuttle to recognize the entire string of characters? Or will it ultimately be easiest to change my auto-numbering scheme to just start from 1.

Thanks,

Meredith

Meredith Rhodes

^^^ Nevermind, I figured out my autonumbering issue. This happened (0001 turned to 1) when I Replaced all rows. But if I had one row on the sheet in the format I wanted it and then merged data - it worked.

Danielle Wilson

@Meredith Rhodes Glad that's working for you! DataMesh would probably be another option for yielding the same result (usually what I'd use for something like this) since it essentially does a merge or add of unique IDs between sheets & reports without the offload & upload step.