Formula to identify the bottom 2 percentage and extract those rows.

[Ebraham]

i have below pivot where i want to extract the bottom 2 percentage from satisfied/Dissatisfied column at an custom and supervisor level.

Years	Supervisor	Custom	Satisfied/Dis-satisfied	of inquiries
2019	asmani1	Aalia	100%​	5​
2019	asmani1	judaan	33%​	6​
2019	asmani1	Kompal	67%​	2​
2019	asmani3	Yogesh	0%​	0​
2019	asmani3	Baba	40%​	1​
2019	asmani3	Sonam	50%​	3​
2020	asmani3	kabhikasha	31%​	5​
2020	asmani3	chandbhai	7%​	6​
2020	asmani3	Deep	18%​	4​
2020	asmiani2	Deshbhakt	18%​	6​
2020	asmiani2	Manish	40%​	7​
2020	asmiani2	Singh	20%​	2​

For example if i select asmani1 in the datavalidation drop down for supervisor then the display figures for other columns should be as below.

Supervisor	Custom	Satisfied/Dis-satisfied	of inquiries
asmani1	judaan	33%​	6​
	kompal	67%​	2​

Thanks in advance. !!.

 

[Toadstool]

Hi Ebraham,

Does this do what you want?

Ebraham.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Years	Supervisor	Custom	Satisfied/Dis-satisfied	of inquiries		Selection		Supervisor	Custom	Satisfied/Dis-satisfied	of inquiries
2	2019	asmani1	Aalia	100%	5		asmani1		asmani1	judaan	33%	6
3	2019	asmani1	judaan	33%	6				asmani1	Kompal	67%	2
4	2019	asmani1	Kompal	67%	2
5	2019	asmani3	Yogesh	0%	0
6	2019	asmani3	Baba	40%	1
7	2019	asmani3	Sonam	50%	3
8	2020	asmani3	kabhikasha	31%	5
9	2020	asmani3	chandbhai	7%	6
10	2020	asmani3	Deep	18%	4
11	2020	asmiani2	Deshbhakt	18%	6
12	2020	asmiani2	Manish	40%	7
13	2020	asmiani2	Singh	20%	2
14
Sheet1

Cell Formulas
Range	Formula
L2:L3,I2:J3	I2	=INDEX(B$2:B$16,MATCH(1,INDEX(($K2=$D$2:$D$16)*($G$2=$B$2:$B$16),0,1),0))
K2:K3	K2	=AGGREGATE(15,6,$D$2:$D$16/($B$2:$B$16=$G$2),ROW()-ROW($K$1))

Cells with Data Validation
Cell	Allow	Criteria
G2	List	asmani1, asmani3, asmiani2

 

[Ebraham]

Hi ,

Thanks for the reply.

it works well even if my data book is closed.

it almost 95% did the work for me but while testing i had one error that is if i have duplicate % in column satisfies/Dissatisfied than the extraction for the other columns is not proper. ie it catches the first matched row as a lookup for the second duplicated %.

 

[Eric W]

This should resolve the tie issue.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Years	Supervisor	Custom	Satisfied/Dis-satisfied	of inquiries		Selection		Supervisor	Custom	Satisfied/Dis-satisfied	of inquiries
2	2019	asmani1	Aalia	1	5		asmani1		asmani1	judaan	0.33	6
3	2019	asmani1	judaan	0.33	6				asmani1	Kompal	0.33	2
4	2019	asmani1	Kompal	0.33	2
5	2019	asmani3	Yogesh	0	0
6	2019	asmani3	Baba	0.4	1
7	2019	asmani3	Sonam	0.5	3
8	2020	asmani3	kabhikasha	0.31	5
9	2020	asmani3	chandbhai	0.07	6
10	2020	asmani3	Deep	0.18	4
11	2020	asmiani2	Deshbhakt	0.18	6
12	2020	asmiani2	Manish	0.4	7
13	2020	asmiani2	Singh	0.2	2
14
Sheet3

Cell Formulas
Range	Formula
I2:I3	I2	=G$2
J2:J3	J2	=INDEX(C:C,AGGREGATE(15,6,ROW($B$2:$B$16)/($D$2:$D$16=K2)/($B$2:$B$16=$G$2),COUNTIF(K$2:K2,K2)))
K2:K3	K2	=AGGREGATE(15,6,$D$2:$D$16/($B$2:$B$16=$G$2),ROW()-ROW($K$1))
L2:L3	L2	=SUMIFS(E:E,B:B,I2,C:C,J2)

 

[Ebraham]

Thanks for the reply.

Can we have any other alternatives other than countif and sumifs as I can have such data in closed book as well.

 

[Eric W]

L2: =INDEX(E:E,AGGREGATE(15,6,ROW($B$2:$B$16)/($D$2:$D$16=K2)/($B$2:$B$16=$G$2),COUNTIF(K$2:K2,K2)))

Same as the J2 formula except for the range. The COUNTIF in thiis formula should be OK, since it will refer to the workbook with the formula, not the workbook with the data.

 

[Ebraham]

Thanks it worked for me !!!