UPDATE: Is there a way to ignore case in a unique identifier

Meredith Rhodes
Meredith Rhodes ✭✭✭
edited 03/22/24 in Formulas and Functions

UPDATE:

I figured out something about my query below - not all of the doi's on the source and target sheets have the same case (some lower case, some upper). I think this is my issue, which presents a new problem.

Is there a way tell smartsheet to ignore case? Or a formula to change case? I'm running into issues where my Unique Identifier is the result of a formula (which it can't be to be able to assign in data shuttle - I've had to copy the result into another column) - and now it's not matching the case.

Bah.

ASIDE: Is there a group of smartsheet folks who work with publication data that I can commiserate with / learn from?

Thank you!

Meredith

_____________________________________________________________________________________________

Hi all,

I'm trying to upload data to my sheet using data shuttle - which I have plenty of experience with. The column that I'm using as a unique identifier to merge data is a digital object identifier (doi).. I'm trying to merge publication dates and open access determinations into my sheet.

Some of these identifiers have a lot of characters in common - even though they are different numbers - like these:

I noticed that not all of the corresponding data for the above doi's uploaded to my sheet. I'm wondering if data shuttle looked at the first 18 or so characters and said, 'nope, those are the same numbers'.

I'd like to be able to use these numbers to import relevant data about the publication, but data from about 80 rows (of about 480) did not merge using these identifiers. I don't want to copy them manually every time I update my database.

Anyone have similar issues and maybe a workaround?

Thank you!

Meredith

Answers

  • Meredith Rhodes
    Meredith Rhodes ✭✭✭
    edited 03/22/24

    UPDATE: Looks like there are differences in capitalization in the unique identifier between the source sheet and the target sheet.

    Is there a way to tell smartsheet to ignore case?

    So many variables...

    Thank you!

    Meredith

  • OK - I think I figured my own problem out.

    I'll run make sure my identifiers are lower case with the =LOWER(@row) formula - and move this result over to my source sheet via data shuttle so it's not a formula there. Let me run this idea through the mill.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!