filter Top 5 by 2 criterions

[Omer_K]

Dear All,
I have a database divided by countries and letters,
(Each letter has a separate column),
I also have a data validaion list in cell J3 with all letters (from letter A to letter F),
And another cell data validaion list in cell J6 that also has all the letters (from letter A to letter F),
I would like to know how I can use by functions (not 365 functions like filter) to list all countries so that if I select a letter in cell J3 the list will show me all the countries with the highest score (eg 10 countries) in that letter and once I select another letter in cell J6 the list will narrow For the 5 countries on the existing list with the highest score in the second list (J6),
I hope i was clear...

חוברת2
	B	C	D	E	F	G	H	I	J
1	A	B	C	D	E	F
2	1.25	4.65	1.73	3.68	4	6.47
3	4.5	6.26	5.1	4.32	1.23	1.57		First Choose	B
4	3.6	5.3	1.07	6.21	3.86	1.01
5	6.75	2.38	1.42	4.01	6.07	5.25
6	5.87	3.17	2.89	6.64	2.92	1.47		Second Choose
7	5.28	1.64	1.48	3.87	3.44	1.73
8	4.82	2.73	2.85	4.47	4.21	1.3
9	6.22	5.18	1.06	1.89	3.99	4.38
10	5.72	1.8	5.44	5.79	5.6	4.52
11	4.41	4.87	6.6	5.91	3.08	6.9
12	4.23	3.34	4.65	5.92	4.42	5.08
13	5.87	6.68	6.42	4.31	1.91	6.99
14	4.63	6.51	3.73	4.29	6.22	5.29
15	5.35	2.28	2.74	5.62	4.06	6.16
16	3.53	4.41	2.49	6.61	5.52	4.26
17	3.68	2.85	4.88	6.11	5.51	1.21
18	3.99	2.81	4.6	3.48	2.59	1.62
19	2.43	6.66	3.03	3.63	3.42	1.6
20	3.64	6.9	6.91	5.21	6.62	3.08
21	5.51	2.09	5.9	4.63	2.55	5.2
22	5.08	3.01	4.38	5.49	2.24	3
23	6.93	2.97	4.5	5.81	2.62	5.43
24	4.51	2.6	4.06	4.02	5.64	1.61
25	1.06	6.1	5.46	6.77	4.48	5.03
26	1.68	6.53	6.91	1.3	4.31	2.33
27	2.24	4.63	3.3	5.31	5.33	4.12
28	6.64	5.49	3.27	4.45	1.38	4.76
29	4.69	3.76	2.65	3.04	4.78	6.64
30	4.74	5.74	4.04	2.48	1.89	1.54
31	1.01	4.16	3.58	5.63	2.61	4.81
32	3.96	2.14	4.06	5.01	2.56	3.86
33	3.41	5.06	3.46	2.17	1.44	3.99
34	6.99	3.59	3.97	4.23	4.79	3.47
35	5.04	5.49	1.7	1.4	4.24	1.59
36	2.82	2.18	6.46	2.76	1.87	3.2
Data

Cells with Data Validation
Cell	Allow	Criteria
J3,J6	List	=$B$1:$G$1

Thank you,

 

[jasonb75]

I don't see any countries in your example, so I've assumed column A.

Sample Data.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Country	A	B	C	D	E	F
2	A	1.25	4.65	1.73	3.68	4	6.47
3	B	4.5	6.26	5.1	4.32	1.23	1.57		First Choose	A
4	C	3.6	5.3	1.07	6.21	3.86	1.01
5	D	6.75	2.38	1.42	4.01	6.07	5.25
6	E	5.87	3.17	2.89	6.64	2.92	1.47		Second Choose	C
7	F	5.28	1.64	1.48	3.87	3.44	1.73
8	G	4.82	2.73	2.85	4.47	4.21	1.3		Top 10		Top 5
9	H	6.22	5.18	1.06	1.89	3.99	4.38		AG	6.99	L	6.42
10	I	5.72	1.8	5.44	5.79	5.6	4.52		V	6.93	T	5.9
11	J	4.41	4.87	6.6	5.91	3.08	6.9		D	6.75	I	5.44
12	K	4.23	3.34	4.65	5.92	4.42	5.08		AA	6.64	V	4.5
13	L	5.87	6.68	6.42	4.31	1.91	6.99		H	6.22	AG	3.97
14	M	4.63	6.51	3.73	4.29	6.22	5.29		E	5.87
15	N	5.35	2.28	2.74	5.62	4.06	6.16		L	5.87
16	O	3.53	4.41	2.49	6.61	5.52	4.26		I	5.72
17	P	3.68	2.85	4.88	6.11	5.51	1.21		T	5.51
18	Q	3.99	2.81	4.6	3.48	2.59	1.62		N	5.35
19	R	2.43	6.66	3.03	3.63	3.42	1.6
20	S	3.64	6.9	6.91	5.21	6.62	3.08
21	T	5.51	2.09	5.9	4.63	2.55	5.2
22	U	5.08	3.01	4.38	5.49	2.24	3
23	V	6.93	2.97	4.5	5.81	2.62	5.43
24	W	4.51	2.6	4.06	4.02	5.64	1.61
25	X	1.06	6.1	5.46	6.77	4.48	5.03
26	Y	1.68	6.53	6.91	1.3	4.31	2.33
27	Z	2.24	4.63	3.3	5.31	5.33	4.12
28	AA	6.64	5.49	3.27	4.45	1.38	4.76
29	AB	4.69	3.76	2.65	3.04	4.78	6.64
30	AC	4.74	5.74	4.04	2.48	1.89	1.54
31	AD	1.01	4.16	3.58	5.63	2.61	4.81
32	AE	3.96	2.14	4.06	5.01	2.56	3.86
33	AF	3.41	5.06	3.46	2.17	1.44	3.99
34	AG	6.99	3.59	3.97	4.23	4.79	3.47
35	AH	5.04	5.49	1.7	1.4	4.24	1.59
36	AI	2.82	2.18	6.46	2.76	1.87	3.2
Sheet13

Cell Formulas
Range	Formula
I9:I18	I9	=INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$36)/(INDEX($B$2:$G$36,0,MATCH($J$3,$B$1:$G$1,0))=J9),COUNTIF(J$9:J9,J9)))
J9:J18	J9	=AGGREGATE(14,6,INDEX($B$2:$G$36,0,MATCH($J$3,$B$1:$G$1,0)),ROWS(I$9:I9))
K9:K13	K9	=INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$36)/ISNUMBER(MATCH($A$2:$A$36,$I$9:$I$18,0))/(INDEX($B$2:$G$36,0,MATCH($J$6,$B$1:$G$1,0))=L9),COUNTIF(L$9:L9,L9)))
L9:L13	L9	=AGGREGATE(14,6,INDEX($B$2:$G$36,0,MATCH($J$6,$B$1:$G$1,0))/ISNUMBER(MATCH($A$2:$A$36,$I$9:$I$18,0)),ROWS(K$9:K9))

Cells with Data Validation
Cell	Allow	Criteria
J3,J6	List	=$B$1:$G$1

 

[Omer_K]

Thank you Jason,
But I meant something else,
Apparently I didn't explain myself correctly so I'll show a more detailed example.
I want to sort through 2 columns using functions,
When finally I will see only the 5 countries (in column A) that the first sort will be by cell J3 and the second sort will be by cell J6,

The following is a more detailed example:

חוברת2.xlsx
	A	B	C	D	E	F	G	H	I	J
1	State	A	B	C	D	E	F
2	Alabama	2.67	1.67	2.33	1.67	3.67	2
3	Alaska	2	3.67	3.67	3	1.67	3.67		First Choose	A
4	Arizona	2.67	3	3	2	2.33	2.33
5	Arkansas	2	2	2.33	1.33	3	3
6	California	1.67	2.67	1.67	3	3	3		Second Choose	C
7	Colorado	1.67	1.33	3	3.67	2.33	2
8	Connecticut	2.67	2	1.33	1.33	3	2
9	Delaware	2.33	3.67	3	3.67	2.33	3.67
10	Florida	2.33	2	2.67	2	3	1.33
11	Georgia	1.33	2	2	2	2.33	3
12	Hawaii	2	2.67	1.33	2	3	2.33
13	Idaho	3	3.67	3	2	2.67	3.67
14	Illinois	2.33	1.33	2.33	1.33	2.67	2
15	Indiana	2.33	2.33	3.67	1.33	1.67	1.67
16	Iowa	3	3	3	2.33	3.67	2.67
17	Kansas	3	3	1.67	2.33	1.33	2
18	Kentucky[E]	3	3	3.67	2.67	1.33	1.67
19	Louisiana	2.67	1.33	1.33	1.33	2.33	2.33
20	Maine	1.67	3	1.67	3	1.33	2.33
21	Maryland	1.67	3.67	2.33	2	1.67	3.67
22	Massachusetts[E]	3	2	3	1.33	2.33	2
23	Michigan	2	3.67	2.33	1.67	3.67	2.67
24	Minnesota	1.33	2.67	2.33	2.67	3.67	2.33
25	Mississippi	2.33	1.67	1.67	3.67	3	3
26	Missouri	2.33	1.67	3.67	2	2.33	3.67
27	Montana	2	1.67	2	3	1.67	2.67
28	Nebraska	2.67	2.67	2.67	1.33	2	3
29	Nevada	3.67	3.67	1.67	2.67	3.67	3
30	New Hampshire	1.33	3.67	3	1.67	1.67	3
31	New Jersey	1.33	1.33	1.67	2.67	1.67	2
32	New Mexico	3	3	2.33	1.67	2.33	1.67
33	New York	1.67	2.33	2.67	2	3	3.67
34	North Carolina	2.33	2	3	2.67	3.67	3
35	North Dakota	3.67	1.67	2	2.67	3.67	3.67
36	Ohio	2.67	3	2.33	3.67	1.67	3
Data

Cells with Data Validation
Cell	Allow	Criteria
J3,J6	List	=$B$1:$G$1

This will be the result:

חוברת2.xlsx
	A	B	C	D	E	F	G
1	State	A	B	C	D	E	F
2	North Dakota	3.67	1.67	2	2.67	3.67	3.67
3	Nevada	3.67	3.67	1.67	2.67	3.67	3
4	Kentucky[E]	3	3	3.67	2.67	1.33	1.67
5	Idaho	3	3.67	3	2	2.67	3.67
6	Iowa	3	3	3	2.33	3.67	2.67
Data

 

[jasonb75]

You will not be able to sort and filter the original table with formulas if that is what you mean, the best that you will be able to do with formulas is to reproduce the table in another part of the sheet.

Using my existing suggestion as a starting point, you could then sort the countries in K9:K13 in another column, then use the country names with vlookup to get the rest of the data from the table.

Will that do what you need?

 

[petertenthije]

Would you mind to add a helper-column?
What you could do is add a column in which you muliply the results of column B (header A) by a large number, and add to that the results of column D (header C).

Assume this formula is placed in column H. Using the example you posted in reply #3 (first table), the formula would be:
H2 = ($B2*10,000)+$D2 = 26,702.33
H3 = ($B3*10,000)+$D3 = 20,003.67
H4 = ($B4*10,000)+$D4 = 26,703.00
and so on

If you create a ranking formula based on this new column, you would be able to sort the results to get the same order as in your second table (ND=1, NV=2, KY=3, ID=4, IA=5)

 

[Omer_K]

You will not be able to sort and filter the original table with formulas if that is what you mean, the best that you will be able to do with formulas is to reproduce the table in another part of the sheet.

Using my existing suggestion as a starting point, you could then sort the countries in K9:K13 in another column, then use the country names with vlookup to get the rest of the data from the table.

Will that do what you need?

If I would like only sort by 2 columns (without filter), that the main sort will be by J3 ant then sort by J6 - Is it possible?

 

[Omer_K]

Would you mind to add a helper-column?
What you could do is add a column in which you muliply the results of column B (header A) by a large number, and add to that the results of column D (header C).

Assume this formula is placed in column H. Using the example you posted in reply #3 (first table), the formula would be:
H2 = ($B2*10,000)+$D2 = 26,702.33
H3 = ($B3*10,000)+$D3 = 20,003.67
H4 = ($B4*10,000)+$D4 = 26,703.00
and so on

If you create a ranking formula based on this new column, you would be able to sort the results to get the same order as in your second table (ND=1, NV=2, KY=3, ID=4, IA=5)

Interesting idea,
The problem is that the columns chaging according to the data validation...

 

[jasonb75]

You need helpers of some kind, it will be impossible without. Here's what I had in mind, is it something that you can use?

The section with the yellow fill contains all of the helpers that feed the results table in I20:O25

Sample Data.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	State	A	B	C	D	E	F
2	Alabama	2.67	1.67	2.33	1.67	3.67	2
3	Alaska	2	3.67	3.67	3	1.67	3.67		First Choose	A
4	Arizona	2.67	3	3	2	2.33	2.33
5	Arkansas	2	2	2.33	1.33	3	3
6	California	1.67	2.67	1.67	3	3	3		Second Choose	C
7	Colorado	1.67	1.33	3	3.67	2.33	2
8	Connecticut	2.67	2	1.33	1.33	3	2		Top 10	1st	2nd	rank
9	Delaware	2.33	3.67	3	3.67	2.33	3.67		Nevada	3.67	1.67	2
10	Florida	2.33	2	2.67	2	3	1.33		North Dakota	3.67	2	1
11	Georgia	1.33	2	2	2	2.33	3		Idaho	3	3	4
12	Hawaii	2	2.67	1.33	2	3	2.33		Iowa	3	3	5
13	Idaho	3	3.67	3	2	2.67	3.67		Kansas	3	1.67	8
14	Illinois	2.33	1.33	2.33	1.33	2.67	2		Kentucky[E]	3	3.67	3
15	Indiana	2.33	2.33	3.67	1.33	1.67	1.67		Massachusetts[E]	3	3	6
16	Iowa	3	3	3	2.33	3.67	2.67		New Mexico	3	2.33	7
17	Kansas	3	3	1.67	2.33	1.33	2
18	Kentucky[E]	3	3	3.67	2.67	1.33	1.67
19	Louisiana	2.67	1.33	1.33	1.33	2.33	2.33
20	Maine	1.67	3	1.67	3	1.33	2.33		State	A	B	C	D	E	F
21	Maryland	1.67	3.67	2.33	2	1.67	3.67		North Dakota	3.67	1.67	2	2.67	3.67	3.67
22	Massachusetts[E]	3	2	3	1.33	2.33	2		Nevada	3.67	3.67	1.67	2.67	3.67	3
23	Michigan	2	3.67	2.33	1.67	3.67	2.67		Kentucky[E]	3	3	3.67	2.67	1.33	1.67
24	Minnesota	1.33	2.67	2.33	2.67	3.67	2.33		Idaho	3	3.67	3	2	2.67	3.67
25	Mississippi	2.33	1.67	1.67	3.67	3	3		Iowa	3	3	3	2.33	3.67	2.67
26	Missouri	2.33	1.67	3.67	2	2.33	3.67
27	Montana	2	1.67	2	3	1.67	2.67
28	Nebraska	2.67	2.67	2.67	1.33	2	3
29	Nevada	3.67	3.67	1.67	2.67	3.67	3
30	New Hampshire	1.33	3.67	3	1.67	1.67	3
31	New Jersey	1.33	1.33	1.67	2.67	1.67	2
32	New Mexico	3	3	2.33	1.67	2.33	1.67
33	New York	1.67	2.33	2.67	2	3	3.67
34	North Carolina	2.33	2	3	2.67	3.67	3
35	North Dakota	3.67	1.67	2	2.67	3.67	3.67
36	Ohio	2.67	3	2.33	3.67	1.67	3
Sheet13

Cell Formulas
Range	Formula
I9:I18	I9	=IF(J9="","",INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$36)/(INDEX($B$2:$G$36,0,MATCH($J$3,$B$1:$G$1,0))=J9),COUNTIF(J$9:J9,J9))))
J9:J18	J9	=IF(ROWS(J$9:J9)>COUNTIF(INDEX($B$2:$G$36,0,MATCH($J$3,$B$1:$G$1,0)),">="&LARGE(INDEX($B$2:$G$36,0,MATCH($J$3,$B$1:$G$1,0)),5)),"",AGGREGATE(14,6,INDEX($B$2:$G$36,0,MATCH($J$3,$B$1:$G$1,0)),ROWS(J$9:J9)))
K9:K18	K9	=IF(J9="","",INDEX($B$2:$G$36,MATCH(I9,$A$2:$A$36,0),MATCH($J$6,$B$1:$G$1,0)))
L9:L18	L9	=IF(K9="","",COUNTIFS($J$9:$J$18,">"&J9)+COUNTIFS($J$9:$J$18,J9,$K$9:$K$18,">"&K9)+COUNTIFS($J$9:$J9,J9,$K$9:$K9,K9))
I21:I25	I21	=INDEX($I$9:$I$18,MATCH(SMALL($L$9:$L$18,ROWS(I$21:I21)),$L$9:$L$18,0))
J21:O25	J21	=INDEX($B$2:$G$36,MATCH($I21,$A$2:$A$36,0),MATCH(J$20,$B$1:$G$1,0))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet13!$A$1:$G$36	I9:I18

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J21:O25	Expression	=J$20=$J$6	text	NO
J21:O25	Expression	=J$20=$J$3	text	NO

Cells with Data Validation
Cell	Allow	Criteria
J3,J6	List	=$B$1:$G$1

 

[petertenthije]

Interesting idea,
The problem is that the columns chaging according to the data validation...

You could use this formula instead of the one I mentioned earlier.
But it will only work for the columns with numerical values. Not for column A (header State).

=INDEX($A2:$G2,1,MATCH($J$3,$A$1:$G$1,0))*10000+INDEX($A2:$G2,1,MATCH($J$6,$A$1:$G$1,0))

 

[petertenthije]

Actually, let me update the formula just a bit more, so it will also work in case both selected columns have same data:

=INDEX($C2:$I2;1;MATCH($L$3;$C$1:$I$1;0))*10000+INDEX($C2:$I2;1;MATCH($L$6;$C$1:$I$1;0))+((100-ROW())/1000000000)