How do I select data from a column between two empty rows in JMP?

Question

Hi Guys,

I am trying to select data from a column between two empty rows and paste it another row.(i.e. transpose it). Here is what I have:
----------------------------
Column 1
(empty row)
(empty row)
3
4
5
6
7
8
9
(empty row)
(empty row)
3
4
5
(empty row)
(empty row)
34
6
7
8
(empty row)
(empty row)
---------------------

The number of data points between two sets of empty rows is variable. So here is the output I would like to have:
ROW1: 3 4 5 6 7 8 9 
ROW2: 3 4 5
ROW3: 34 6 7 8

I am a newbie and any help is appreciated. 
Thanks.

ms · Answer

If you are grep savvy this particular problem may be easier to solve using a text editor.
Paste your data in a text editor
Find: (\d+)
Replace with: \1	
Now you have your rows (tab separated)
To get rid of empty rows (and the dots representing missing values in jmp in case of continuous variables)
Find: 	\.*\.*
Replace with: nothing
Paste back into a new jmp data table.
However the trick given above should work as well.

(syntax may vary with grep dist)

Message was edited by: MS

unknown · Answer

Thanks, Jeff and mpb.

I will try your suggestions and see if they work for me.

Jeff_Perkinson · Answer

Forum user mpb pointed out to me privately that my solution didn't actually work.

He's right. The groups of non-missing values don't end up in the same rows without a Grouping column.

So, you'll need another new column - call it Group - to identify groups of non-missing values. Here's a formula:

If(Row() == 1, 1, If(Is Missing(Lag(:Column 1, 1)) & !Is Missing(:Column 1), Lag(:Group, 1) + 1, Lag(:Group, 1)))

You can use this as the Group column in the Split dialog and you'll get what you want.

However, mpb also points out that once you have this Group column you can use Tables ->Transpose with Column 1 in the Transpose Columns role and Group in the By role. Which means that you don't need the column from my first solution at all.

Sorry for the confusion.

Jeff