problem with rank and countif function

[mychi11]

I tried to use the formula
=IF($E2<>"IUI",RANK(C2,$C:$C,1)+COUNTIF($C$2:C2,C2)-1) to generate sequential number based on date without repetition.
This formula works well. However, when i add in new row, the formula in the last few row tends to change. For example =IF($E68<>"IUI",RANK(C68,$C:$C,1)+COUNTIF($C$2:C69,C68)-1).
Is there anyway to improve the formula?

 

[jasonb75]

Are you using a structured table, or regular ranges?

I've seen problems where relative rows adjust incorrectly when rows are added to a table, but not for a normal range.

 

[mychi11]

Hi Jason, I am currently using a structured table.

 

[jasonb75]

It is possible to correct the formula so that the rows adjust correctly. You will need to edit the formula below so that the names match your actual table.

=IF([@Col_E]<>"IUI",RANK([@Col_c],[Col_C],1)+COUNTIF(INDEX([Col_C],1):INDEX([Col_C],ROW([@Col_C])-ROW([[#Headers][Col_C]])),[@Col_C])-1)

Hopefully the parentheses are all in the right places.

 

[mychi11]

Hi Jason,

Thank you for your prompt response. After applying the formula, i realised no 1 and 2 are missing and I am not sure why N2 and N3 both rank 3.

ART RECORD SHEET(IVF(HK)).xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	PROCEDURE DATE	ART DATE	PROCEDURES	IUI DATE	OPU DATE	OC WARM DATE	ET DATE	FET DATE	EMB WARM DATE	ART NO.	IUI NO	IVF NO	CY NO (OC THW)	CY NO. (FET)	CY NO. (ALL)	TR	WIFE NAME	DOB(W)
2	14-05-20	14-05-20	ICSI		14-05-20		19-05-20			200514-01		3			2	1	AMY	01-05-87
3	15-05-20	15-05-20	ICSI		15-05-20					200515-01		3			1	1	ACE	27-08-86
4	18-05-20	18-05-20	IVF		18-05-20					200514-02		4			3	1	ALICE	20-05-82
5	23-06-20	23-06-20	ICSI		23-06-20					200515-01		5			4	2	ACE	27-08-86
6	07-07-20	07-07-20	ICSI		07-07-20		10-07-20			200706-01		6			5	1	GRACE	01-11-80
7	02-08-20	02-08-20	ICSI		02-08-20		05-08-20			200706-01		13			6	2	GRACE	01-11-80
8	08-08-20	08-08-20	OC FZ		08-08-20					200514-01		19			7	1	AMY	01-05-87
9	08-08-20	08-08-20	OC FZ		08-08-20					200707-01		20			8	1	LILY	22-12-78
10	17-08-20	17-08-20	BOTH		17-08-20		20-08-20			200622-02		22			9	1	KELLY	18-07-89
11	18-08-20	00-01-00	FET					18-08-20	18-08-20	200514-01		23		2		1	AMY	01-05-87
12	27-08-20	00-01-00	FET					27-08-20	25-08-20	200515-01		36		1		1	ACE	27-08-86
13	24-08-20	24-08-20	OC THW+ICSI			24-08-20				200514-01		27	7		10	2	AMY	01-05-87
14	10-09-20	10-09-20	OC FZ		10-09-20					200707-01		45			11	2	LILY	22-12-78
15	15-09-20	00-01-00	FET					15-09-20	15-09-20	200514-02		47		3		1	ALICE	20-05-82
16	02-09-20	02-09-20	OC THW+ICSI			02-09-20	05-09-20			200514-01		40	7		#####	3	AMY	01-05-87
17	23-11-20	00-01-00	FET					23-11-20	23-11-20	200514-01		54		2		2	AMY	01-05-87
18	12-12-20	00-01-00	FET					12-12-20	12-12-20	200706-01		56		6		1	GRACE	01-11-80
19	12-12-20	00-01-00	FET					12-12-20	12-12-20	200622-02		57		9		1	KELLY	18-07-89
ART

 

[jasonb75]

Looks like you lost an important little bit of the formula

=IF([@[PROCEDURES ]]<>"IUI",RANK([@[PROCEDURE DATE]],[PROCEDURE DATE],1)+COUNTIF(INDEX([PROCEDURE DATE],1):INDEX([PROCEDURE DATE],ROW([@[PROCEDURE DATE]])-ROW([[#Headers][PROCEDURE DATE]])),[@[PROCEDURE DATE]])-1)

 

[mychi11]

it's because when i add in the header part, an message : the syntax of the name isnt correct. not sure what exactly is wrong

 

[mychi11]

Hi Jason, I have solved the problem by adding an additional coma. Thank you so much for your help. It's working perfectly

 

[jasonb75]

I just found the problem but look like you beat me to it. Table formulas are always a pain to write.

=IF([@[PROCEDURES ]]<>"IUI",RANK([@[PROCEDURE DATE]],[PROCEDURE DATE],1)+COUNTIF(INDEX([PROCEDURE DATE],1):INDEX([PROCEDURE DATE],ROW([@[PROCEDURE DATE]])-ROW([[#Headers],[PROCEDURE DATE]])),[@[PROCEDURE DATE]])-1)

 

[mychi11]

Thank you so much for your help. Sorry I don't have much knowledge in writing these formulas. I basically googled everything to make up the formula, so it's difficult for me to spot what is missing or so. Thank you once again, Jason.