Ranking by certain criteria

[SwanB]

Hi! I am hoping to get some help on a ranking formula. What I am trying to do is rank a column (GXT). If the Category Column has "Including" then give that line the same rank number that falls within that same interval (From m) (To m) columns. Example:
View attachment 99994

If it does not have an "Including" then it just gives it the next rank. There will be numerous sites within this spreadsheet. It would be nice to have each year be ranked, so 2022 will have a rank starting with 1 to whatever and then 2023 will start over with rank 1 through ending on whatever date. The intervals (From m) and (To m) columns might over lap with other sites but if we could match with the Hole column and site columns that should keep things separated.

If there is a different/better way to do this, I am all for it.

I have attached a small spreadsheet. I hope this makes sense.

Thank you for your time!

Test for SI Rank.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Region	Concatenate	Site	Date	Project	Hole	Category	From (m)	To (m)	Int (m)	TW (m)	Column1	Column2	GXT	Comments	Rank
2	NA	KA18	KA	1/31/2022	awesome	35		495.3	507.5	12.2				74.8714
3	NA	KA19	KA	1/31/2022	awesome	35	Including	501.4	506	4.6				72.542
4	NA	KA7	KA	1/31/2022	awesome	35		45.7	51.8	6.1				22.5883
5	NA	KA8	KA	1/31/2022	awesome	35	Including	48.8	50.3	1.5				20.985
6	NA	KA16	KA	1/31/2022	awesome	35		486.2	492.3	6.1				12.7551
7	NA	KA17	KA	1/31/2022	awesome	35	Including	487.7	490.7	3				10.902
8	NA	KA21	KA	1/31/2022	awesome	35		530.4	547.1	16.7				8.5838
9	NA	KA12	KA	1/31/2022	awesome	35		234.7	259.1	24.4				8.3692
10	NA	KA13	KA	1/31/2022	awesome	35	Including	240.8	242.3	1.5				2.211
11	NA	KA20	KA	1/31/2022	awesome	35		510.5	527.3	16.8				6.3336
12	NA	KA9	KA	1/31/2022	awesome	35		129.5	138.7	9.2				5.6764
13	NA	AK10	KA	1/31/2022	awesome	35	Including	135.6	137.2	1.6				2.2496
14	NA	KA5	KA	1/31/2022	awesome	35		24.4	32	7.6				5.2136
15	NA	KA6	KA	1/31/2022	awesome	35	Including	24.4	25.9	1.5				2.16
16	NA	KA14	KA	1/31/2022	awesome	35		274.3	278.9	4.6				3.7858
17	NA	KA1	KA	1/31/2022	awesome	26		286.5	292.6	6.1				3.1354
18	NA	KA2	KA	1/31/2022	awesome	26	Including	291.1	292.6	1.5				1.9545
19	NA	KA22	KA	1/31/2022	awesome	35		534.9	536.5	1.6				2.8528
20	NA	KA23	KA	1/31/2022	awesome	35		550.2	554.7	4.5				2.6235
21	NA	KA3	KA	1/31/2022	awesome	28		9.1	16.8	7.7				2.1098
22	NA	KA4	KA	1/31/2022	awesome	28	Including	9.1	10.7	1.6				1.3712
23	NA	KA15	KA	1/31/2022	awesome	35		458.7	463.3	4.6				1.104
24	NA	KA11	KA	1/31/2022	awesome	35		182.9	187.5	4.6				1.104
25	NA	KA27	KA	2/28/2022	great	3		53.3	61	7.7				9.009
26	NA	KA28	KA	2/28/2022	great	3	Including	57.9	61	3.1				6.696
27	NA	KA199	KA	1/31/2023	awesome	55	Including	100.6	123.4	22.8	11.4			84.018
28	NA	KA145	KA	1/31/2023	awesome	58	Including	483.11	484.63	1.52				71.44
29	NA	KA180	KA	1/31/2023	awesome	58		472.4	490.7	18.3	14			60.2
30	NA	KA230	KA	1/31/2023	awesome	58		472.4	490.7	18.3	14			60.2
31	NA	KA182	KA	1/31/2023	awesome	58	Including	483.1	484.6	1.5	1.1			51.711
32	NA	KA232	KA	1/31/2023	awesome	58	Including	483.1	484.6	1.5	1.1			51.711
33	NA	KA136	KA	1/31/2023	awesome	55	Including	349	350.52	1.52				46.968
34	NA	KA152	KA	1/31/2023	Dis	25	Including	18.3	27.4	9.1	1.78			29.014
35	NA	KA186	KA	1/31/2023	Dis	25		10.7	27.4	16.7				21.877
36	NA	KA127	KA	1/31/2023	Dis	25	Including	18.29	27.43	9.14				16.2692
37	NA	KA188	KA	1/31/2023	Dis	25	Including	18.3	27.4	9.1				16.198
38	NA	MB1	MB	1/31/2022	close	6		222.90024	229.5144	6.61416				3.492236569
39	NA	MB2	MB	1/31/2022	close	6		713.994	739.4448	25.4508				485.5957143
40	NA	MB3	MB	1/31/2022	close	6	Including	716.82864	731.8248	14.99616				450.4452215
41	NA	MB4	MB	2/28/2022	close	6		479.1456	483.7176	4.572				9.56190215
42	NA	MB5	MB	2/28/2022	close	6		503.5296	508.1016	4.572				1.08159221
43	NA	MB6	MB	3/31/2022	close	6	No SI	623.316	633.0696	9.7536				0
44	NA	MB7	MB	3/31/2022	close	6	No SI	654.4056	680.0088	25.6032				0
45	NA	MB8	MB	3/31/2022	close	6		689.1528	749.808	60.6552				475.1847362
46	NA	MB9	MB	3/31/2022	close	6		715.2132	739.4448	24.2316				466.15527
47	NA	MB10	MB	3/31/2022	close	7	No SI							0
48	NA	MB11	MB	4/30/2022	close	7	No SI							0
49	NA	MB12	MB	5/31/2022	close	7		750.4176	762.6096	12.192				8.025727706
50	NA	MB13	MB	5/31/2022	close	7		776.3256	794.3088	17.9832				11.83794837
51	NA	MB14	MB	5/31/2022	close	7		816.5	877.8	61.3				47.34199
52	NA	MB15	MB	5/31/2022	close	7	Including	822	828.1	6.1				17.80895
53	NA	MB16	MB	5/31/2022	close	7		908.9	947	38.1				7.43712
Sheet 1

Cell Formulas
Range	Formula
J2:J26	J2	=[@[To (m)]]-[@[From (m)]]

 

[SanjayGMusafir]

If it does not have an "Including" then it just gives it the next rank

To achieve this we mandatory need to have data sorted by date in ascending order, else whatever formula we will populate would give error (I'm forgetting the name of it)

If we can sort the data then I have to think of second thing that is ranking should start fresh every year but that can be done (probably) - for that I will work with your sample data once you agree to sorting...

Please revert

 

[SwanB]

To achieve this we mandatory need to have data sorted by date in ascending order, else whatever formula we will populate would give error (I'm forgetting the name of it)

If we can sort the data then I have to think of second thing that is ranking should start fresh every year but that can be done (probably) - for that I will work with your sample data once you agree to sorting...

Please revert

If that is the way you think we need to do it then lets try that.
thanks

 

[SanjayGMusafir]

Check below and revert.

Note:
# There is a challenge with your sample data every time there is "including" in Column G, it repeats some value from above. That's fine. But first of them should not carry "including" for Excel to understand from where it needs to copy. Check test Column R for understanding. Hope rest of the needs it fullfills. Error in Data you need to rectify as per your need

# The solution existed on following link. I just improvised - Rank if formula

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Region	Concatenate	Site	Date	Project	Hole	Category	From (m)	To (m)	Int (m)	TW (m)	Column1	Column2	GXT	Comments	Year	Rank	Test
2	NA	KA18	KA	44592	awesome	35		495.3	507.5	12.20				74.8714		2022	4
3	NA	KA19	KA	44592	awesome	35	Including	501.4	506	4.60				72.542		2022	NF	0
4	NA	KA7	KA	44592	awesome	35		45.7	51.8	6.10				22.5883		2022	6
5	NA	KA8	KA	44592	awesome	35	Including	48.8	50.3	1.50				20.985		2022	NF	0
6	NA	KA16	KA	44592	awesome	35		486.2	492.3	6.10				12.7551		2022	7
7	NA	KA17	KA	44592	awesome	35	Including	487.7	490.7	3.00				10.902		2022	NF	0
8	NA	KA21	KA	44592	awesome	35		530.4	547.1	16.70				8.5838		2022	11
9	NA	KA12	KA	44592	awesome	35		234.7	259.1	24.40				8.3692		2022	12
10	NA	KA13	KA	44592	awesome	35	Including	240.8	242.3	1.50				2.211		2022	NF	0
11	NA	KA20	KA	44592	awesome	35		510.5	527.3	16.80				6.3336		2022	15
12	NA	KA9	KA	44592	awesome	35		129.5	138.7	9.20				5.6764		2022	16
13	NA	AK10	KA	44592	awesome	35	Including	135.6	137.2	1.60				2.2496		2022	NF	0
14	NA	KA5	KA	44592	awesome	35		24.4	32	7.60				5.2136		2022	17
15	NA	KA6	KA	44592	awesome	35	Including	24.4	25.9	1.50				2.16		2022	NF	0
16	NA	KA14	KA	44592	awesome	35		274.3	278.9	4.60				3.7858		2022	18
17	NA	KA1	KA	44592	awesome	26		286.5	292.6	6.10				3.1354		2022	20
18	NA	KA2	KA	44592	awesome	26	Including	291.1	292.6	1.50				1.9545		2022	NF	0
19	NA	KA22	KA	44592	awesome	35		534.9	536.5	1.60				2.8528		2022	21
20	NA	KA23	KA	44592	awesome	35		550.2	554.7	4.50				2.6235		2022	22
21	NA	KA3	KA	44592	awesome	28		9.1	16.8	7.70				2.1098		2022	23
22	NA	KA4	KA	44592	awesome	28	Including	9.1	10.7	1.60				1.3712		2022	NF	0
23	NA	KA15	KA	44592	awesome	35		458.7	463.3	4.60				1.104		2022	24
24	NA	KA11	KA	44592	awesome	35		182.9	187.5	4.60				1.104		2022	25
25	NA	MB1	MB	44592	close	6		222.90024	229.5144	6.61				3.492236569		2022	19
26	NA	MB2	MB	44592	close	6		713.994	739.4448	25.45				485.5957143		2022	1
27	NA	MB3	MB	44592	close	6	Including	716.82864	731.8248	15.00				450.4452215		2022	NF	0
28	NA	KA27	KA	44620	great	3		53.3	61	7.70				9.009		2022	10
29	NA	KA28	KA	44620	great	3	Including	57.9	61	3.10				6.696		2022	NF	0
30	NA	MB4	MB	44620	close	6		479.1456	483.7176	4.57				9.56190215		2022	9
31	NA	MB5	MB	44620	close	6		503.5296	508.1016	4.57				1.08159221		2022	26
32	NA	MB6	MB	44651	close	6	No SI	623.316	633.0696	9.75				0		2022	27
33	NA	MB7	MB	44651	close	6	No SI	654.4056	680.0088	25.60				0		2022	27
34	NA	MB8	MB	44651	close	6		689.1528	749.808	60.66				475.1847362		2022	2
35	NA	MB9	MB	44651	close	6		715.2132	739.4448	24.23				466.15527		2022	3
36	NA	MB10	MB	44651	close	7	No SI							0		2022	27
37	NA	MB11	MB	44681	close	7	No SI							0		2022	27
38	NA	MB12	MB	44712	close	7		750.4176	762.6096	12.19				8.025727706		2022	13
39	NA	MB13	MB	44712	close	7		776.3256	794.3088	17.98				11.83794837		2022	8
40	NA	MB14	MB	44712	close	7		816.5	877.8	61.30				47.34199		2022	5
41	NA	MB15	MB	44712	close	7	Including	822	828.1	6.10				17.80895		2022	NF	0
42	NA	MB16	MB	44712	close	7		908.9	947	38.10				7.43712		2022	14
43	NA	KA199	KA	44957	awesome	55	Including	100.6	123.4	22.80	11.4			84.018		2023	NF	0
44	NA	KA145	KA	44957	awesome	58	Including	483.11	484.63	1.52				71.44		2023	NF	0
45	NA	KA180	KA	44957	awesome	58		472.4	490.7	18.30	14			60.2		2023	1
46	NA	KA230	KA	44957	awesome	58		472.4	490.7	18.30	14			60.2		2023	1
47	NA	KA182	KA	44957	awesome	58	Including	483.1	484.6	1.50	1.1			51.711		2023	NF	0
48	NA	KA232	KA	44957	awesome	58	Including	483.1	484.6	1.50	1.1			51.711		2023	NF	0
49	NA	KA136	KA	44957	awesome	55	Including	349	350.52	1.52				46.968		2023	NF	0
50	NA	KA152	KA	44957	Dis	25	Including	18.3	27.4	9.10	1.78			29.014		2023	NF	0
51	NA	KA186	KA	44957	Dis	25		10.7	27.4	16.70				21.877		2023	3
52	NA	KA127	KA	44957	Dis	25	Including	18.29	27.43	9.14				16.2692		2023	NF	0
53	NA	KA188	KA	44957	Dis	25	Including	18.3	27.4	9.10				16.198		2023	NF	0
Sheet1

Cell Formulas
Range	Formula
P2:P53	P2	=YEAR(D2)
Q2:Q53	Q2	=IFS(G2="including",XLOOKUP(1,($J$1:J1=J2)*($P$1:P1=P2),$Q$1:Q1,"NF"),TRUE, COUNTIFS($P$2:$P$53,P2,$G$2:$G$53,"<>including",$N$2:$N$53,">"&N2)+1)
R2:R53	R2	=IFS(Q2="NF",COUNTIFS($J$1:J1,J2,$G$1:G1,"<>"&"including"),TRUE,"")
J2:J35	J2	=I2-H2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q:Q	Expression	=AND(R1<>"Test",R1<>"",R1>=1)	text	NO