Formula within groups of values

[#1]

Hi

I have a table of process steps and process times for different IDs. However, some of my process steps occur more than once in the process for each ID. I would like to create a column that separates all the process steps into either "synthesis" or "processing". These groups would be determined based on if they occur before or after another process step.

I want the formula to find the time of the spawn step FOR EACH ID and compare it to all the other process step times for that ID. 

I was trying to use some sort of loop, but I am new at scripting and loops are very confusing!

Is there any way to do this???

For example, what I have looks like this and I want to add the Category column.

ID	Process Step	Process time	Category
10	Drying	9:00	Synthesis
10	Spawned	9:30	Synthesis
10	Drying	10:00	Processing
10	Finished	11:00	Processing
11	Drying	2:00	Synthesis
11	Spawned	2:30	Synthesis
11	Drying	2:45	Processing
11	Finished	4:00	Processing
12	Drying	7:30	Synthesis
12	Spawned	7:45	Synthesis
12	Drying	8:00	Processing
12	Finished	9:00	Processing

Any ideas??

Thank you!

[#2 · Accepted solution]

This script seems to be what you want.  It could be turned into a formula if you would prefer that

Names Default To Here( 1 );

dt = New Table( "Untitled 13",
	Add Rows( 12 ),
	New Column( "ID",
		Character,
		"Nominal",
		Set Values( {"10", "10", "10", "10", "11", "11", "11", "11", "12", "12", "12", "12"} )
	),
	New Column( "Process Step",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"Drying", "Spawned", "Drying", "Finished", "Drying", "Spawned", "Drying", "Finished", "Drying", "Spawned", "Drying",
			"Finished"}
		)
	),
	New Column( "Process Time",
		Numeric,
		"Nominal",
		Format( "h:m", 12 ),
		Input Format( "h:m" ),
		Set Values( [32400, 34200, 36000, 39600, 7200, 9000, 9900, 14400, 27000, 27900, 28800, 32400] )
	)
);

dt << New Column( "Category", Character );

For( i = 1, i <= N Rows( dt ), i++,
	currID = dt:id[i];
	If( i == 1,
		spawnTime = dt:Process Time[(dt << get rows where( dt:id == currID & dt:Process Step == "Spawned" ))[1]]
	);
	If( i > 1,
		If( dt:ID[i] != dt:ID[i - 1],
			spawnTime = dt:Process Time[(dt << get rows where( dt:id == currID & dt:Process Step == "Spawned" ))[1]]
		)
	);
	If( dt:Process Time[i] <= spawnTime,
		dt:Category[i] = "Synthesis",
		dt:Category[i] = "Processing";
	);
);

 The script assumes the data are sorted by ID.  However, if the spawnTime was freshly calculated for each loop through the data, the table would not have to be sorted.  It would slow the script down, calculating the SpawnTime on each loop, but that might be a tradeoff you want to make.