Question on assigning a qualitative value based on quantitative info from members of the same group

Options

Hi, I am looking for help on crafting a formula that would allow me to discriminate between [Type] -> "Dna/Seq". Right now, I know that they are either DNA plate barcodes, or Sequencing plate barcodes based on the "pcr" prefix. However, I would like to be exact with these and say it is either DNA, or Seq. It is fairly straightforward, as the smaller barcode number (ie 9615) will always be the DNA plate, while the larger of the two (ie 9617) will always be the Sequencing plate. This comparison must be done between identical values in the [source_order_reference] column. Thank you!

Tags:

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    I'm just trying to clarify the problem a bit.

    Would it be acceptable logic to say "If the barcode starts with pcr and the next row's bardcode also starts with pcr, then this is a DNA plate. Otherwise it is a Sequence plate"?

    Or would it have to be "If the barcode is pcr and the source # matches the source # on the next row, then it is a DNA plate, otherwise it is a sequence plate"?

    Do either of those thoughts track?

  • ScionoftheNight
    ScionoftheNight ✭✭✭✭
    Options

    Hi David, the second thought is on the right track

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    This formula might take a bit of explaining.

    =IF(LEFT(barcode@row, 3) = "pcr", IF(AND(Source@row = Source2, LEFT(barcode@row, 3) = LEFT(barcode2, 3)), "DNA", "Seq"), "N/A")

    So first it checks to see if the barcode is pcr. If so, then it checks to see if the source number matches the next source number (the formula starts in row 1 for this to work so the next source is Source2) AND that the current barcode is pcr and the next barcode is pcr (you could be more explicit in this and say that both LEFT have to match "pcr", but I just did a compare to make sure they both match each other). If all of that is true, then DNA, else if the barcode was PCR, then it is a Seq plate. I just put N/A in the false, but that's where you would put checks for other types of plates.

    Would that work?

  • ScionoftheNight
    ScionoftheNight ✭✭✭✭
    Options

    Ah that is straightforward, I was expecting something a lot harder as I didn't notice that the way the data is displayed is that the DNA plate is always listed before the Seq plate. Thanks!

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    No problem. Glad it worked out.

  • ScionoftheNight
    ScionoftheNight ✭✭✭✭
    edited 09/18/20
    Options

    Ah, but the data isn't always in that sort of order where the 2 are matched together, so this doesn't actually work. I changed my SQL query to make it so though, so should be good to go.

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    Nice! I'm sure we could come up with something to handle unsorted date, it just might be an uglier version of the formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!