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

ScionoftheNight

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!

David Tutwiler

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

Hi David, the second thought is on the right track

David Tutwiler

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

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

No problem. Glad it worked out.

ScionoftheNight

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

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