Record ID Query

[DHB]

I have data that looks like this (the actual version has about 2400 rows);

Name	Course
James	a
James	f
Bill	m
Bill	f
Bill	r
Steve	k

I'd like to give a record ID to each course starting at 1 for each person.  So in the end I want the data to look like this;

ID	Name	Course
1	James	a
2	James	f
1	Bill	m
2	Bill	f
3	Bill	r
1	Steve	k

Does anyone know how this can be achieved?

Thank you in advance for your help.

Best,

DHB.

[Bob_Blackey]

Hi DHB,

You can use a Multi_row formula tool.

in the config

Create a new field, ID  (Int 32)

and the formula would be

IF [Name] = [Row-1:Name] Then [Row-1:ID]+1  Else 1 ENDIF

It looks and sees if the Name is the same as the previous row, if so it adds one, else starts again at 1.

P.S. oh yeah, Group By  (see below)... must be five oclock somewhere...

Cheers,
Bob

[MarqueeCrew · Accepted solution]

You can use the multi-row formula to create a new field, ID

Group By: Name

[Row-1:ID] + 1

Cheers,
mark

[DHB]

Hi Bob

Not sure if I followed your instruction correctly as I got 1,1,2,2,2,3.  Instead of 1,2,1,2,3,1.

[DHB]

This one worked for me.  Thank you so much for your help.