Rearranging Data

[#1]

I have a data set that comes in from Excel whereby several key pieces of information are arranged in individual cells.  To make this data useful in JMP, I came up with a scheme to populate the data systematically in columns using lag statements.  I have some consistency in the layout which allows me to use fixed lag values.  I included an example for rearranging the data contained in the cell to the right of "Recipe".  I suspect that what I'm doing is more cumbersome than necessary if anyone has a better suggestion?

Before

After

	New Column("Recipe2", Character, "Nominal", Formula(If(!Is Missing(:Column 7), If(:Column 1 == "Recipe", :Column 2, Lag(:Recipe2, 1)))));
	New Column("Recipe", Character, "Nominal", Formula(If(!Is Missing(:Column 7), If(Is Missing(:"Recipe2"n), Lag(:"Recipe2"n, -4), :"Recipe2"n))));

[#2]

Split and join/update could potentially be enough but difficult to say without seeing your data. For example from here

You can split your data and get clean single row which you could possibly join/update to your data

With multiple groups? you could add running number to the groups and use that as group in split

[#3]

My suggestions to reformat the Excel sheets you are importing in the first place.  I suggest to my clients who all use excel is to create the data table the way you want in JMP and then export it into excel to be populated.

[#4 · Accepted solution]

I came up with a simplification of the approach I'm already using to eliminate the need for pairs of columns.

	New Column("Recipe", Character, "Nominal", Formula(If(!Is Missing(:Column 7), If(Lag(:Column 1, -4) == "Recipe", Lag(:Column 2, -4), Lag(:Recipe, 1)))));