VBA or Formula to find (#3 and #4 digit) MOST match key (defined key)

[aanisnaini]

Dear All,
as per subject, I'M looking forward to have your help to make VBA or Formula to to find (#3 and #4 digit) MOST match key (defined key). Attached the file and example the goal (just write one alphabet where is the most matched key).

- the goal is explained at second yellow highlighted, looking for the most key matched with each column number (just find #3 and #4digit).
example: in cell A2 (1221), looking for 21 match with what a KEYS?. if match with more than one key, then calculate which one the most (large/top) one.
- there is row with describe:
1. All Data key: index and match all data(number-just find #3 and #4digit) from A2 to Today
2. 1 month Key: index and match data(number-just find #3 and #4digit) from Today - one month (past)
3. 2 week key: index and match data(number-just find #3 and #4digit) from Today - 2 week (past)
4. 3 week key: index and match data(number-just find #3 and #4digit) from Today - 3 week (past)
5. 10 days key: index and match data(number-just find #3 and #4digit) from Today - 10 days (past)
6. 7 days key: index and match data(number-just find #3 and #4digit) from Today - 7 days (past)
that's why in A1..CQ1 value is date.

Thank you

looking for key.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY
1	01/10/2021	02/10/2021	03/10/2021	04/10/2021	05/10/2021	06/10/2021	07/10/2021	08/10/2021	09/10/2021	10/10/2021	11/10/2021	12/10/2021	13/10/2021	14/10/2021	15/10/2021	16/10/2021	17/10/2021	18/10/2021	19/10/2021	20/10/2021	21/10/2021	22/10/2021	23/10/2021	24/10/2021	25/10/2021	26/10/2021	27/10/2021	28/10/2021	29/10/2021	30/10/2021	31/10/2021	01/11/2021	02/11/2021	03/11/2021	04/11/2021	05/11/2021	06/11/2021	07/11/2021	08/11/2021	09/11/2021	10/11/2021	11/11/2021	12/11/2021	13/11/2021	14/11/2021	15/11/2021	16/11/2021	17/11/2021	18/11/2021	19/11/2021	20/11/2021	21/11/2021	22/11/2021	23/11/2021	24/11/2021	25/11/2021	26/11/2021	27/11/2021	28/11/2021	29/11/2021	30/11/2021	01/12/2021	02/12/2021	03/12/2021	04/12/2021	05/12/2021	06/12/2021	07/12/2021	08/12/2021	09/12/2021	10/12/2021	11/12/2021	12/12/2021	13/12/2021	14/12/2021	15/12/2021	16/12/2021	17/12/2021	18/12/2021	19/12/2021	20/12/2021	21/12/2021	22/12/2021	23/12/2021	24/12/2021	25/12/2021	26/12/2021	27/12/2021	28/12/2021	29/12/2021	30/12/2021	31/12/2021	01/01/2022	02/01/2022	03/01/2022		KEY	Key Number
2	1221	6261	6867	1255	9301	7257	4538	9457	7675	0668	3339	9564	4175	8288	7361	9726	5907	2703	4661	5201	9145	2144	4827	9608	9464	9464	7696	7216	3485	1967	9184	9340	3627	8182	9128	9939	9191	3228	0775	9276	3583	0687	7907	3377	3248	4620	5153	4959	7844	8547	0878	6085	0867	1421	0647	6584	2188	4480	9991	5012	0478	2545	3802	1186	1212	7856	8498	6961	8245	5619	4893	4398	2935	6948	3340	3298	5306	7489	1075	2131	0500	5921	6109	7718	4867	4522	0370	5963	5567	3702	6188	0	0	0	0		A	0	1	2	3	4	5
3																																																																																											#N/A				0		B	0	1	2	3	4	6
4																																																																																											#N/A						C	0	1	2	3	4	7
5																																																																																													Looking for #3 and #4 Digit Most Match key FORMULA				D	0	1	2	3	4	8
6																																																																																														All data Key			E	0	1	2	3	4	9
7																																																																																														1 Month Key			F	0	1	2	3	5	6
8																																																																																														2 week key			G	0	1	2	3	5	7
9																																																																																														3 week key			H	0	1	2	3	5	8
10																																																																																														10 Days Key			I	0	1	2	3	5	9
11																																																																																														7 days Key			J	0	1	2	3	6	7
12																																																																																																	K	0	1	2	3	6	8
13																																																																																														The Goal: Looking for #3 and #4 Digit MOST Match key			L	0	1	2	3	6	9
14																																																																																														All data Key	A		M	0	1	2	3	7	8
15																																																																																														1 Month Key	Y		N	0	1	2	3	7	9
16																																																																																														2 week key	Z		O	0	1	2	3	8	9
17																																																																																														3 week key	AA		P	0	1	2	4	5	6
18																																																																																														10 Days Key	WW		Q	0	1	2	4	5	7
19																																																																																														7 days Key	XX		R	0	1	2	4	5	8
20																																																																																																	S	0	1	2	4	5	9
21																																																																																																	T	0	1	2	4	6	7
22																																																																																																	U	0	1	2	4	6	8
23																																																																																																	V	0	1	2	4	6	9
24																																																																																																	W	0	1	2	4	7	8
25																																																																																																	X	0	1	2	4	7	9
26																																																																																																	Y	0	1	2	4	8	9
Sheet1 (2)

Cell Formulas
Range	Formula
CQ3	CQ3	=VALUE(CW1)
CM3	CM3	=INDEX($CS$1:$CS$199,MATCH(MID(CM2,2,1),$CT$1:$CT$199,0),1)
CM4	CM4	=VLOOKUP(MID(CM2,3,1),CS1:CY2,CT1:CY2,TRUE)
CS2:CS26	CS2	=LEFT(ADDRESS(1, ROW(CS1), 4, TRUE), (ROW(CS1)>26)+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:CQ2	Expression	=AND(ISODD(LEFT(RIGHT(A2;2);1));RIGHT(A2)="0")	text	NO
A2:CQ2	Expression	=AND(LEN(A2)>5)	text	NO
A2:CQ2	Expression	=AND(LEN(A2)<2)	text	NO
A2:CQ2	Expression	=AND(LEFT(RIGHT(A2;2);1)="8";ISEVEN(RIGHT(A2)))	text	NO
A2:CQ2	Expression	=AND(LEFT(RIGHT(A2;2);1)="8";ISODD(RIGHT(A2)))	text	NO
A2:CQ2	Expression	=AND(LEFT(RIGHT(A2;2);1)="0";ISEVEN(RIGHT(A2)))	text	NO
A2:CQ2	Expression	=AND(ISEVEN(LEFT(RIGHT(A2;2);1));RIGHT(A2)="0")	text	NO
A2:CQ2	Expression	=AND(RIGHT(A2)="8")	text	NO
A2:CQ2	Expression	=AND(LEFT(RIGHT(A2;2);1)="9")	text	NO
A2:CQ2	Expression	=AND(ISEVEN(LEFT(RIGHT(A2;2);1));RIGHT(A2)="0")	text	NO
A2:CQ2	Expression	=AND(LEFT(RIGHT(A2;2);1)="0";ISODD(RIGHT(A2)))	text	NO
A2:CQ2	Expression	=AND(ISEVEN(LEFT(RIGHT(A2;2);1));RIGHT(A2)="9")	text	NO
A2:CQ2	Expression	=AND(ISODD(LEFT(RIGHT(A2;2);1));RIGHT(A2)="9")	text	NO
A2:CQ2	Expression	=OR(LEFT(RIGHT(A2;2);1)="1";LEFT(RIGHT(A2;2);1)="2";LEFT(RIGHT(A2;2);1)="3";LEFT(RIGHT(A2;2);1)="4";LEFT(RIGHT(A2;2);1)="5";LEFT(RIGHT(A2;2);1)="6";LEFT(RIGHT(A2;2);1)="7";RIGHT(A2)="1";RIGHT(A2)="2";RIGHT(A2)="3";RIGHT(A2)="4";RIGHT(A2)="5";RIGHT(A2)="6";RIGHT(A2)="7")	text	NO

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA/Formula to find (#3 and #4 digit) MOST match key (defined key)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[aanisnaini]

so, I must delete this post?

Thank You

 

[Joe4]

so, I must delete this post?

No, that is not what he is telling you at all.
Please take the time to read the link he provided, and it should be clear.

 

[aanisnaini]

Dear All,
I made mistake and cross posting, here the links:
https://www.excelforum.com/excel-pro...fined-key.html
https://www.mrexcel.com/board/thread...d-key.1191506/

Thank you