Number by Count in Other Columns

Question

I want to create a formula in a new column, Run Number, to count based off other columns. For example:

Sample     Test      Run Number

A                one      1

A                one      2

A                two      1

A                two      2

B               one      1

B               two      1

C               one     1

C               one     2

C               two     1

C               two     2

txnelson · Answer

@JMPUser9 ,

@jthi has provided an excellent answer.

However, the answer to this question has been answered multiple times before in the Discussion Community.  I request that before a request is submitted that a search is made to the past Discussions.

I did a search on "Count" and it returned several related responses.

Thank you

jthi · Answer

Using Col Cumulative Sum() is one option

Col Cumulative Sum(1, :Sample, :Test)

Col Rank() is also an option

Col Rank(:Sample, :Sample, :Test)

Full example with your table

Names Default To Here(1);

dt = New Table("Untitled 2",
	Add Rows(10),
	Compress File When Saved(1),
	New Column("Sample",
		Character,
		"Nominal",
		Set Values({"A", "A", "A", "A", "B", "B", "C", "C", "C", "C"})
	),
	New Column("Test",
		Character(16),
		"Nominal",
		Set Values({"one", "one", "two", "two", "one", "two", "one", "one", "two", "two"})
	),
	New Column("Run Number",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([1, 2, 1, 2, 1, 1, 1, 2, 1, 2])
	)
);

dt << New Column("RN", Numeric, Ordinal, Formula(
	Col Cumulative Sum(1, :Sample, :Test)
));