Searching for clustered numbers

[keranali]

Need to input a number on a cell in red then excel searches a next tab where the database is and lists, 1. The dates the number was on the database list. 2. The number in the cell above it in the "reverse" label. 3. The number below it in the bottom cell labeled "Forward". and 4. when the number is inputted in red it populates the information for that day.

the project.xlsx
	B	C	D	E	F	G	H	I
5	24
6
7
8
9
10
11
12		Clustering Algorithms			Day Line
13		Reverse	Forward	Dates	AM	MD	EV	PM
14
15
16
17
18
19
20
21
22
23
24
Overview

Cell Formulas
Range	Formula
B5	B5	=B3

Database

the project.xlsx
	B	C	D
1	Date	Time	Num
2	2-Jan-23	Morning	33
3	2-Jan-23	Midday	21
4	2-Jan-23	Afternoon	4
5	2-Jan-23	Evening	1
6	3-Jan-23	Morning	2
7	3-Jan-23	Midday	15
8	3-Jan-23	Afternoon	32
9	3-Jan-23	Evening	22
10	4-Jan-23	Morning	27
11	4-Jan-23	Midday	5
12	4-Jan-23	Afternoon	32
13	4-Jan-23	Evening	35
14	5-Jan-23	Morning	18
15	5-Jan-23	Midday	12
16	5-Jan-23	Afternoon	15
17	5-Jan-23	Evening	11
18	6-Jan-23	Morning	19
19	6-Jan-23	Midday	12
20	6-Jan-23	Afternoon	2
21	6-Jan-23	Evening	20
22	7-Jan-23	Morning	36
23	7-Jan-23	Midday	36
24	7-Jan-23	Afternoon	5
25	7-Jan-23	Evening	20
26	9-Jan-23	Morning	32
27	9-Jan-23	Midday	13
28	9-Jan-23	Afternoon	5
29	9-Jan-23	Evening	21
30	10-Jan-23	Morning	18
31	10-Jan-23	Midday	5
32	10-Jan-23	Afternoon	35
33	10-Jan-23	Evening	4
34	11-Jan-23	Morning	30
35	11-Jan-23	Midday	17
36	11-Jan-23	Afternoon	33
37	11-Jan-23	Evening	11
38	12-Jan-23	Morning	13
39	12-Jan-23	Midday	33
40	12-Jan-23	Afternoon	28
41	12-Jan-23	Evening	33
42	13-Jan-23	Morning	34
43	13-Jan-23	Midday	21
44	13-Jan-23	Afternoon	19
45	13-Jan-23	Evening	8
46	14-Jan-23	Morning	18
47	14-Jan-23	Midday	11
48	14-Jan-23	Afternoon	12
49	14-Jan-23	Evening	19
50	16-Jan-23	Morning	6
51	16-Jan-23	Midday	13
52	16-Jan-23	Afternoon	6
53	16-Jan-23	Evening	10
54	17-Jan-23	Morning	33
55	17-Jan-23	Midday	3
56	17-Jan-23	Afternoon	15
57	17-Jan-23	Evening	22
58	18-Jan-23	Morning	20
59	18-Jan-23	Midday	26
60	18-Jan-23	Afternoon	30
61	18-Jan-23	Evening	4
62	19-Jan-23	Morning	13
63	19-Jan-23	Midday	12
64	19-Jan-23	Afternoon	1
65	19-Jan-23	Evening	14
66	20-Jan-23	Morning	10
67	20-Jan-23	Midday	19
68	20-Jan-23	Afternoon	18
69	20-Jan-23	Evening	28
70	21-Jan-23	Morning	24
71	21-Jan-23	Midday	30
72	21-Jan-23	Afternoon	3
73	21-Jan-23	Evening	32
74	23-Jan-23	Morning	4
75	23-Jan-23	Midday	20
76	23-Jan-23	Afternoon	36
77	23-Jan-23	Evening	10
78	24-Jan-23	Morning	9
79	24-Jan-23	Midday	10
80	24-Jan-23	Afternoon	12
81	24-Jan-23	Evening	21
82	25-Jan-23	Morning	29
83	25-Jan-23	Midday	8
84	25-Jan-23	Afternoon	15
85	25-Jan-23	Evening	21
Data

 

[awoohaw]

can you type in some examples of expected results?

 

[keranali]

Thanks for your reply please see below

the project.xlsx
	B	C	D	E	F	G	H	I
1
2	Entries
3	24	Beta	Submit
4
5	23
6
7
8
9
10
11
12		Clustering Algorithms			Day Line
13		Reverse	Forward	Dates	AM	MD	EV	PM
14		33	23	3/2/2023	23	23	35	32
15		9	23	9/3/2023	23	23	5	36
16
17
18
19
20
21
22
23
24
Overview

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=Stats!$B$1:$AK$1
C3	List	=Repo!$A$1:$A$4

the project.xlsx
	B	C	D
106	1-Feb-23	Morning	11
107	1-Feb-23	Midday	27
108	1-Feb-23	Afternoon	16
109	1-Feb-23	Evening	13
110	2-Feb-23	Morning	31
111	2-Feb-23	Midday	36
112	2-Feb-23	Afternoon	29
113	2-Feb-23	Evening	33
114	3-Feb-23	Morning	23
115	3-Feb-23	Midday	23
116	3-Feb-23	Afternoon	35
117	3-Feb-23	Evening	32
118	4-Feb-23	Morning	30
119	4-Feb-23	Midday	14
120	4-Feb-23	Afternoon	9
121	4-Feb-23	Evening	13
122	6-Feb-23	Morning	4
123	6-Feb-23	Midday	12
124	6-Feb-23	Afternoon	2
125	6-Feb-23	Evening	32
126	7-Feb-23	Morning	18
127	7-Feb-23	Midday	21
128	7-Feb-23	Afternoon	14
129	7-Feb-23	Evening	1
130	8-Feb-23	Morning	22
131	8-Feb-23	Midday	13
132	8-Feb-23	Afternoon	31
133	8-Feb-23	Evening	20
134	9-Feb-23	Morning	27
135	9-Feb-23	Midday	34
136	9-Feb-23	Afternoon	6
137	9-Feb-23	Evening	3
138	10-Feb-23	Morning	7
139	10-Feb-23	Midday	31
140	10-Feb-23	Afternoon	21
141	10-Feb-23	Evening	9
142	11-Feb-23	Morning	30
143	11-Feb-23	Midday	35
144	11-Feb-23	Afternoon	10
145	11-Feb-23	Evening	10
146	13-Feb-23	Morning	29
147	13-Feb-23	Midday	4
148	13-Feb-23	Afternoon	27
149	13-Feb-23	Evening	10
150	14-Feb-23	Morning	18
151	14-Feb-23	Midday	20
152	14-Feb-23	Afternoon	21
153	14-Feb-23	Evening	16
154	15-Feb-23	Morning	28
155	15-Feb-23	Midday	24
156	15-Feb-23	Afternoon	35
157	15-Feb-23	Evening	22
158	16-Feb-23	Morning	5
159	16-Feb-23	Midday	2
160	16-Feb-23	Afternoon	14
161	16-Feb-23	Evening	3
162	17-Feb-23	Morning	22
163	17-Feb-23	Midday	36
164	17-Feb-23	Afternoon	30
165	17-Feb-23	Evening	28
166	18-Feb-23	Morning	19
167	18-Feb-23	Midday	16
168	18-Feb-23	Afternoon	35
169	18-Feb-23	Evening	25
170	20-Feb-23	Morning	11
171	20-Feb-23	Midday	18
172	20-Feb-23	Afternoon	13
173	20-Feb-23	Evening	11
174	21-Feb-23	Morning	29
175	21-Feb-23	Midday	30
176	21-Feb-23	Afternoon	6
177	21-Feb-23	Evening	16
178	22-Feb-23	Morning	28
179	22-Feb-23	Midday	4
180	22-Feb-23	Afternoon	15
181	22-Feb-23	Evening	34
182	23-Feb-23	Morning	29
183	23-Feb-23	Midday	34
184	23-Feb-23	Afternoon	8
185	23-Feb-23	Evening	25
186	24-Feb-23	Morning	7
187	24-Feb-23	Midday	35
188	24-Feb-23	Afternoon	14
189	24-Feb-23	Evening	10
190	25-Feb-23	Morning	10
191	25-Feb-23	Midday	29
192	25-Feb-23	Afternoon	1
193	25-Feb-23	Evening	8
194	27-Feb-23	Morning	24
195	27-Feb-23	Midday	30
196	27-Feb-23	Afternoon	7
197	27-Feb-23	Evening	24
198	28-Feb-23	Morning	21
199	28-Feb-23	Midday	4
200	28-Feb-23	Afternoon	4
201	28-Feb-23	Evening	3
202	1-Mar-23	Morning	12
203	1-Mar-23	Midday	18
204	1-Mar-23	Afternoon	3
205	1-Mar-23	Evening	15
206	2-Mar-23	Morning	32
207	2-Mar-23	Midday	27
208	2-Mar-23	Afternoon	15
209	2-Mar-23	Evening	19
210	3-Mar-23	Morning	8
211	3-Mar-23	Midday	21
212	3-Mar-23	Afternoon	1
213	3-Mar-23	Evening	6
214	4-Mar-23	Morning	14
215	4-Mar-23	Midday	18
216	4-Mar-23	Afternoon	30
217	4-Mar-23	Evening	12
218	6-Mar-23	Morning	10
219	6-Mar-23	Midday	28
220	6-Mar-23	Afternoon	27
221	6-Mar-23	Evening	34
222	7-Mar-23	Morning	20
223	7-Mar-23	Midday	25
224	7-Mar-23	Afternoon	27
225	7-Mar-23	Evening	32
226	8-Mar-23	Morning	6
227	8-Mar-23	Midday	1
228	8-Mar-23	Afternoon	35
229	8-Mar-23	Evening	9
230	9-Mar-23	Morning	23
231	9-Mar-23	Midday	23
232	9-Mar-23	Afternoon	5
233	9-Mar-23	Evening	36
234	10-Mar-23	Morning	27
235	10-Mar-23	Midday	7
236	10-Mar-23	Afternoon	21
237	10-Mar-23	Evening	10
Data

 

[awoohaw]

well, thank you for the sample output and the additional data source. Since you have multiple instances of the same number getting a list of dates is problematic for me. If you had 365 it can probably be done with LAMBDA, LET, and FILTER functions

I know there is a way to do pull down array formulas to get what you ask, but my expertise is not at that level.

 

[keranali]

Thanks brother for looking into it really appreciated.

 

[kweaver]

The OP runs 2016 and LAMBDA, etc. aren't available in 2016.

 

[keranali]

Can macros or vba work in this instance?

 

[keranali]

Revised version on the table expanding on the Times per day

the project.xlsx
	B	C	D	E	F	G	H	I
5	23
6
7
8
9
10
11
12		Clustering Algorithms			Day Line
13		Reverse	Forward	Dates	Morning	Midday	Afternoon	Evening
14		33	23	3/2/2023	23	23	35	32
15		9	23	9/3/2023	23	23	5	36
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Overview