Creating a new column from 2 columns with a formula

[#1]

Hi,

Can someone help me write a code in JMP to recreate Column 3 in the attached file.

Column 3 was derived from columns 1 and 2. Basically if Column 1 is "PM01" and Column 2 is "A01" or "B01" or "C01" or "D01" or "E01" or "F01", then Column 3 will be "Lot 1" and so on.

Best,

Meda

[#2]

Not really sure I can understand the logic you have in xlsx file. What do you do when the numbers don't match?

You can get last characters of string for example with Right()

Right("PM12", 2); //12

[#3]

I want to recreate Column 3 in jmp using columns 1 and 2

[#4]

I used this formula and it works for Lot 1; but I don't know how to expand it for PM01 to PM12 and for Lot 2 and 3.

If(
	:Panel Member == "PM01" & :Well # == "A01" | :Panel Member == "PM01" & :Well # == "B01" | :Panel Member == "PM01" & :Well # == "C01" |
	:Panel Member == "PM01" & :Well # == "D01" | :Panel Member == "PM01" & :Well # == "E01" | :Panel Member == "PM01" & :Well # == "F01",
	"Lot 1",
	"TBD"
)

[#5]

Based on this, I would say CC Lot# is determined only by Well#

[#6]

Ok, can you then write a formula to recreate column 3 by well#

[#7]

Attachment in previous post (Sheet1.jmp) has one idea for it

[#8]

Ok I got it thank you so much!

[#9]

Hi Meda

Pls refer to annex files and click '+' after column check formula.

Have a nice day.

[#10]

Since you have few levels of Lots, you can recode the data to a new column and JMP will create the formula for you.