Identifying row numbers where the date is the same

[Red over White]

I am trying to identify the rows where a particular date is shown. Whilst I can do this when the date is unique in the sequence, the sequence contains dates which appear more than once. I am looking for a formula so that when the date appears more than once, each of the respective row numbers will appear for that date.

I attach my workings to date. The dates in column E are chronlogically the dates in column B. You can that in column F if the date in column E is the same, the same row number appears. What I am trying to achieve is shown in column G.

Any help would be appreciated.

Thanks

Chronological dates.xlsx
	B	C	D	E	F	G
1	Random dates		Small 'k'	Chron dates	Row no	Want it to be
2						(and so on)
3	07/10/21		1	08/04/21	15	15
4	14/05/21		2	08/04/21	15	29
5	17/09/21		3	09/04/21	52	52
6	05/04/22		4	26/04/21	32	32
7	07/07/21		5	04/05/21	66	66
8	02/02/22		6	06/05/21	63	63
9	28/01/22		7	07/05/21	23	23
10	06/08/21		8	14/05/21	2	2
11			9	14/05/21	2	19
12			10	14/05/21	2	72
13	21/09/21		11	25/05/21	43
14	04/06/21		12	27/05/21	41
15			13	01/06/21	21
16	01/03/22		14	04/06/21	12
17	08/04/21		15	07/06/21	49
18	07/10/21		16	10/06/21	78
19	14/05/21		17	14/06/21	47
20			18	18/06/21	60
21			19	21/06/21	56
22	29/10/21		20	02/07/21	73
23	01/06/21		21	07/07/21	5
24	06/10/21		22	08/07/21	24
25	07/05/21		23	16/07/21	65
26	08/07/21		24	23/07/21	39
27	07/10/21		25	02/08/21	83
28	13/01/22		26	06/08/21	8
29	08/04/21		27	06/08/21	8
30			28	13/08/21	44
31			29	08/09/21	48
32	22/01/22		30	08/09/21	48
33	17/09/21		31	13/09/21	42
34	26/04/21		32	15/09/21	46
35	20/09/21		33	17/09/21	3
36			34	17/09/21	3
37			35	20/09/21	33
38	08/10/21		36	20/09/21	33
39	04/01/22		37	20/09/21	33
40			38	21/09/21	11
41	23/07/21		39	23/09/21	62
42	20/09/21		40	06/10/21	22
43	27/05/21		41	07/10/21	1
44	13/09/21		42	07/10/21	1
45	25/05/21		43	07/10/21	1
46	13/08/21		44	08/10/21	36
47	01/04/22		45	27/10/21	68
48	15/09/21		46	29/10/21	20
49	14/06/21		47	01/11/21	67
50	08/09/21		48	01/11/21	67
51	07/06/21		49	05/11/21	74
52			50	05/11/21	74
53			51	19/11/21	71
54	09/04/21		52	25/11/21	53
55	25/11/21		53	26/11/21	72
56			54	01/12/21	80
57			55	20/12/21	58
58	21/06/21		56	04/01/22	37
59	20/09/21		57	07/01/22	64
60	20/12/21		58	13/01/22	26
61	29/03/22		59	22/01/22	30
62	18/06/21		60	28/01/22	7
63	10/02/22		61	01/02/22	82
64	23/09/21		62	02/02/22	6
65	06/05/21		63	04/02/22	84
66	07/01/22		64	10/02/22	61
67	16/07/21		65	01/03/22	14
68	04/05/21		66	22/03/22	81
69	01/11/21		67	29/03/22	59
70	27/10/21		68	01/04/22	45
71
72	14/05/21
73	19/11/21
74	26/11/21
75	02/07/21
76	05/11/21
77
78
79
80	10/06/21
81	08/09/21
82	01/12/21
83	22/03/22
84	01/02/22
85	02/08/21
86	04/02/22
87	06/08/21
88	05/11/21
89
90	01/11/21
2022

Cell Formulas
Range	Formula
E3:E70	E3	=IFERROR(SMALL(B$3:B$151,D3),"")
F3:F70	F3	=MATCH(E3,B$3:B$151,0)
D4:D70	D4	=D3+1

 

[Fluff]

I think your highlighted numbers are wrong, but try

+Fluff 1.xlsm
	B	C	D	E	F	G
1	Random dates		Small 'k'	Chron dates	Row no	Want it to be
2						(and so on)
3	07/10/2021		1	08/04/2021	15	15
4	14/05/2021		2	08/04/2021	27	29
5	17/09/2021		3	09/04/2021	52	52
6	05/04/2022		4	26/04/2021	32	32
7	07/07/2021		5	04/05/2021	66	66
8	02/02/2022		6	06/05/2021	63	63
9	28/01/2022		7	07/05/2021	23	23
10	06/08/2021		8	14/05/2021	2	2
11			9	14/05/2021	17	19
12			10	14/05/2021	70	72
13	21/09/2021		11	25/05/2021	43
14	04/06/2021		12	27/05/2021	41
15			13	01/06/2021	21
16	01/03/2022		14	04/06/2021	12
17	08/04/2021		15	07/06/2021	49
18	07/10/2021		16	10/06/2021	78
19	14/05/2021		17	14/06/2021	47
20			18	18/06/2021	60
21			19	21/06/2021	56
22	29/10/2021		20	02/07/2021	73
23	01/06/2021		21	07/07/2021	5
24	06/10/2021		22	08/07/2021	24
25	07/05/2021		23	16/07/2021	65
26	08/07/2021		24	23/07/2021	39
27	07/10/2021		25	02/08/2021	83
28	13/01/2022		26	06/08/2021	8
29	08/04/2021		27	06/08/2021	85
30			28	13/08/2021	44
31			29	08/09/2021	48
32	22/01/2022		30	08/09/2021	79
33	17/09/2021		31	13/09/2021	42
34	26/04/2021		32	15/09/2021	46
35	20/09/2021		33	17/09/2021	3
36			34	17/09/2021	31
37			35	20/09/2021	33
38	08/10/2021		36	20/09/2021	40
39	04/01/2022		37	20/09/2021	57
40			38	21/09/2021	11
41	23/07/2021		39	23/09/2021	62
42	20/09/2021		40	06/10/2021	22
43	27/05/2021		41	07/10/2021	1
44	13/09/2021		42	07/10/2021	16
45	25/05/2021		43	07/10/2021	25
46	13/08/2021		44	08/10/2021	36
47	01/04/2022		45	27/10/2021	68
48	15/09/2021		46	29/10/2021	20
49	14/06/2021		47	01/11/2021	67
50	08/09/2021		48	01/11/2021	88
51	07/06/2021		49	05/11/2021	74
52			50	05/11/2021	86
53			51	19/11/2021	71
54	09/04/2021		52	25/11/2021	53
55	25/11/2021		53	26/11/2021	72
56			54	01/12/2021	80
57			55	20/12/2021	58
58	21/06/2021		56	04/01/2022	37
59	20/09/2021		57	07/01/2022	64
60	20/12/2021		58	13/01/2022	26
61	29/03/2022		59	22/01/2022	30
62	18/06/2021		60	28/01/2022	7
63	10/02/2022		61	01/02/2022	82
64	23/09/2021		62	02/02/2022	6
65	06/05/2021		63	04/02/2022	84
66	07/01/2022		64	10/02/2022	61
67	16/07/2021		65	01/03/2022	14
68	04/05/2021		66	22/03/2022	81
69	01/11/2021		67	29/03/2022	59
70	27/10/2021		68	01/04/2022	45
71
72	14/05/2021
73	19/11/2021
74	26/11/2021
75	02/07/2021
76	05/11/2021
77
78
79
80	10/06/2021
81	08/09/2021
82	01/12/2021
83	22/03/2022
84	01/02/2022
85	02/08/2021
86	04/02/2022
87	06/08/2021
88	05/11/2021
89
90	01/11/2021
Lists

Cell Formulas
Range	Formula
E3:E70	E3	=IFERROR(SMALL(B$3:B$151,D3),"")
F3:F70	F3	=AGGREGATE(15,6,(ROW($B$3:$B$100)-ROW($B$3)+1)/($B$3:$B$100=E3),COUNTIFS(E$3:E3,E3))
D4:D70	D4	=D3+1

 

[Red over White]

Fluff, thanks again for helping me out.

The formula works for but are out by 2. I have not used aggregate before and would like to learn a bit more about this function to see if I can fix the issue myself. Are you ok if I try tomorrow and come back back and let you know how I got on?

Chronological dates.xlsx
	B	C	D	E	F	G
1	Random dates		Small 'k'	Chron dates	Row no	Want it to be
2						(and so on)
3	07/10/21		1	08/04/21	15	15
4	14/05/21		2	08/04/21	27	29
5	17/09/21		3	09/04/21	52	52
6	05/04/22		4	26/04/21	32	32
7	07/07/21		5	04/05/21	66	66
8	02/02/22		6	06/05/21	63	63
9	28/01/22		7	07/05/21	23	23
10	06/08/21		8	14/05/21	2	2
11			9	14/05/21	17	19
12			10	14/05/21	70	72
13	21/09/21		11	25/05/21	43
14	04/06/21		12	27/05/21	41
15			13	01/06/21	21
16	01/03/22		14	04/06/21	12
17	08/04/21		15	07/06/21	49
18	07/10/21		16	10/06/21	78
19	14/05/21		17	14/06/21	47
20			18	18/06/21	60
21			19	21/06/21	56
22	29/10/21		20	02/07/21	73
23	01/06/21		21	07/07/21	5
24	06/10/21		22	08/07/21	24
25	07/05/21		23	16/07/21	65
26	08/07/21		24	23/07/21	39
27	07/10/21		25	02/08/21	83
28	13/01/22		26	06/08/21	8
29	08/04/21		27	06/08/21	85
30			28	13/08/21	44
31			29	08/09/21	48
32	22/01/22		30	08/09/21	79
33	17/09/21		31	13/09/21	42
34	26/04/21		32	15/09/21	46
35	20/09/21		33	17/09/21	3
36			34	17/09/21	31
37			35	20/09/21	33
38	08/10/21		36	20/09/21	40
39	04/01/22		37	20/09/21	57
40			38	21/09/21	11
41	23/07/21		39	23/09/21	62
42	20/09/21		40	06/10/21	22
43	27/05/21		41	07/10/21	1
44	13/09/21		42	07/10/21	16
45	25/05/21		43	07/10/21	25
46	13/08/21		44	08/10/21	36
47	01/04/22		45	27/10/21	68
48	15/09/21		46	29/10/21	20
49	14/06/21		47	01/11/21	67
50	08/09/21		48	01/11/21	88
51	07/06/21		49	05/11/21	74
52			50	05/11/21	86
53			51	19/11/21	71
54	09/04/21		52	25/11/21	53
55	25/11/21		53	26/11/21	72
56			54	01/12/21	80
57			55	20/12/21	58
58	21/06/21		56	04/01/22	37
59	20/09/21		57	07/01/22	64
60	20/12/21		58	13/01/22	26
61	29/03/22		59	22/01/22	30
62	18/06/21		60	28/01/22	7
63	10/02/22		61	01/02/22	82
64	23/09/21		62	02/02/22	6
65	06/05/21		63	04/02/22	84
66	07/01/22		64	10/02/22	61
67	16/07/21		65	01/03/22	14
68	04/05/21		66	22/03/22	81
69	01/11/21		67	29/03/22	59
70	27/10/21		68	01/04/22	45
71
72	14/05/21
73	19/11/21
74	26/11/21
75	02/07/21
76	05/11/21
77
78
79
80	10/06/21
81	08/09/21
82	01/12/21
83	22/03/22
84	01/02/22
85	02/08/21
86	04/02/22
87	06/08/21
88	05/11/21
89
90	01/11/21
2022

Cell Formulas
Range	Formula
E3:E70	E3	=IFERROR(SMALL(B$3:B$151,D3),"")
F3:F70	F3	=AGGREGATE(15,6,(ROW($B$3:$B$150)-ROW($B$3)+1)/($B$3:$B$150=E3),COUNTIFS(E$3:E3,E3))
D4:D70	D4	=D3+1

 

[Fluff]

How do you get both 2 & 19 for 14/5/21?
Surely it should be 2 & 17 or 4 & 19

 

[Red over White]

Fluff

The figures you quote come from the aggregate formula =AGGREGATE(15,6,(ROW($B$3:$B$150)-ROW($B$3)+1)/($B$3:$B$150=E3),COUNTIFS(E$3:E3,E3)). I suspect the constant discrepancy of 2 has something to do with the two lines at the top which don't have any dates in them (column B) and suffixing the formula with +2 which correct the issue. I will still have a look at the AGGREGATE function in more detail to tomorrow.

 

[Fluff]

You are saying that the 8th should return 15 & 29 which makes no sense.
It should either be 17 & 29 the actual row number, or 15 & 27 their position in the list.