Automatic sorting of values for recoding causing problems

Question

Here is what I need to do: I have list of alpha/numeric values that need to be recoded. The values are repeated many times, and are grouped together, BUT, they groups are not sorted (ie the values are not order). I want to give each unique value a number based on its position in the column. Here is a screen shot to explain (in Rows 10 and 11 -K07768 comes before K07684).

Here is the difficulty I've been having: When I use the Recode function in JMP, it automatically re-orders my unique values.  When dealing with over 60 unique values repeated up to 320 times each, it is difficult to keep track of the original order. Is there an easy way to accomplish my goal in JMP (with or without the Recode dialog box)?

Another question about re-code: there doesn't seem to be a way to quickly enter each of the new numbers. Is there a way to copy paste a list of new values, or something similar to "fill series" in Excel.

Thanks!

cwillden · Accepted Answer

Use this formula in the column "What I need":

If(
	Row() == 1, 1,
	:What I Have[Row()] == :What I Have[Row() - 1], :What I need[Row() - 1],
	:What I need[Row() - 1] + 1
)

It assigns the first row the value 1.  If the value in the column "What I Have" is the same as the previous row, then copy the value in "What I need" from the previous row to the current row.  If they are not the same, increase the value of "What I need" from the previous row by 1 for the current row.

Here's what my example looks like afterwards:

txnelson · Accepted Answer

Here is a formula that you can use that will create the new values

If( Row() == 1,
	count = 1,
	If( Lag( :What I Have, 1 ) != :What I Have,
		count = count + 1
	)
);
count;