How to fetch the highest or successive highest values?

[rajamdade]

Hello everyone,

Kindly help with the formula that could search with the given query id (column F) for the highest %Gene identity (column D), which could fetch the values for %gene identity (column I), the gene (column H) and relevant accession (column G). However, Query ID should match the highest % Gene identity only once.

For example, the query id: 27504.m000612, once matched with Raf29 having the overall highest %Gene identity (70.5%) will not be searched again eventhough it has higher %Gene identity for Raf21 (69.9%) than that of 27471.m000401 (Raf21: 68.9%). Now, the query ID: 27471.m000401, could be alloted with the following/successive highest (which is 2nd in this case) Raf21 (68.9%).

However, if the highest/following highest %Gene identity is exactly similar between two query IDs then it could indicate as conflict.

I have similar 300 query ids with such data. I would appreciate it if you could provide the formulas to fetch values for column G, H and I from the given query as in the column F, thanks in advance.

Mrexcel query.xlsx
	A	B	C	D	E	F	G	H	I
1	Query ID	Accession	Gene	% Gene Identity		Query ID	Accession	Gene	% Gene Identity
2	27471.m000401	AT4G35780	Raf29	69.1		27471.m000401	AT2G17700	Raf21	68.9
3	27471.m000401	AT2G17700	Raf21	68.9		27504.m000612	AT4G35780	Raf29	70.5
4	27471.m000401	AT4G38470	Raf30	66.3		27504.m000613	AT4G38470	Raf30	Conflict
5	27504.m000612	AT4G35780	Raf29	70.5		27894.m000774	AT4G38470	Raf30	Conflict
6	27504.m000612	AT2G17700	Raf21	69.9
7	27504.m000612	AT3G06630	Raf8	68.4
8	27504.m000612	AT4G38470	Raf30	67.3
9	27504.m000613	AT4G35780	Raf29	70
10	27504.m000613	AT2G17700	Raf21	67.4
11	27504.m000613	AT4G38470	Raf30	66.2
12	27894.m000774	AT4G38470	Raf30	66.2
13	27894.m000774	AT4G14780	Raf26	64.8
14	27894.m000774	AT4G35780	Raf29	63.7
Sheet1

 

[Eric W]

Maybe something like this. I used Conditional Formatting to show the conflicts.

Book2
	A	B	C	D	E	F	G	H	I
1	Query ID	Accession	Gene	% Gene Identity		Query ID	Accession	Gene	% Gene Identity
2	27471.m000401	AT4G35780	Raf29	69.1		27504.m000612	AT4G35780	Raf29	70.5
3	27471.m000401	AT2G17700	Raf21	68.9		27471.m000401	AT2G17700	Raf21	68.9
4	27471.m000401	AT4G38470	Raf30	66.3		27504.m000613	AT4G38470	Raf30	66.2
5	27504.m000612	AT4G35780	Raf29	70.5		27894.m000774	AT4G38470	Raf30	66.2
6	27504.m000612	AT2G17700	Raf21	69.9
7	27504.m000612	AT3G06630	Raf8	68.4
8	27504.m000612	AT4G38470	Raf30	67.3
9	27504.m000613	AT4G35780	Raf29	70
10	27504.m000613	AT2G17700	Raf21	67.4
11	27504.m000613	AT4G38470	Raf30	66.2
12	27894.m000774	AT4G38470	Raf30	66.2
13	27894.m000774	AT4G14780	Raf26	64.8
14	27894.m000774	AT4G35780	Raf29	63.7
Sheet15

Cell Formulas
Range	Formula
F2:H5	F2	=INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$14)/($D$2:$D$14=$I2),COUNTIF($I$2:$I2,$I2)))
I2:I5	I2	=AGGREGATE(14,6,D$2:D$14/(COUNTIFS($F$1:$F1,$A$2:$A$14)=0)/(COUNTIFS($H$1:$H1,$C$2:$C$14,$I$1:$I1,"<>"&$D$2:$D$14)=0),1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I:I	Cell Value	duplicates	text	NO

 

[rajamdade]

Maybe something like this. I used Conditional Formatting to show the conflicts.

Book2
	A	B	C	D	E	F	G	H	I
1	Query ID	Accession	Gene	% Gene Identity		Query ID	Accession	Gene	% Gene Identity
2	27471.m000401	AT4G35780	Raf29	69.1		27504.m000612	AT4G35780	Raf29	70.5
3	27471.m000401	AT2G17700	Raf21	68.9		27471.m000401	AT2G17700	Raf21	68.9
4	27471.m000401	AT4G38470	Raf30	66.3		27504.m000613	AT4G38470	Raf30	66.2
5	27504.m000612	AT4G35780	Raf29	70.5		27894.m000774	AT4G38470	Raf30	66.2
6	27504.m000612	AT2G17700	Raf21	69.9
7	27504.m000612	AT3G06630	Raf8	68.4
8	27504.m000612	AT4G38470	Raf30	67.3
9	27504.m000613	AT4G35780	Raf29	70
10	27504.m000613	AT2G17700	Raf21	67.4
11	27504.m000613	AT4G38470	Raf30	66.2
12	27894.m000774	AT4G38470	Raf30	66.2
13	27894.m000774	AT4G14780	Raf26	64.8
14	27894.m000774	AT4G35780	Raf29	63.7
Sheet15

Cell Formulas
Range	Formula
F2:H5	F2	=INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$14)/($D$2:$D$14=$I2),COUNTIF($I$2:$I2,$I2)))
I2:I5	I2	=AGGREGATE(14,6,D$2:D$14/(COUNTIFS($F$1:$F1,$A$2:$A$14)=0)/(COUNTIFS($H$1:$H1,$C$2:$C$14,$I$1:$I1,"<>"&$D$2:$D$14)=0),1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I:I	Cell Value	duplicates	text	NO

Thank you @Eric W for your answer. But, the expected output is somewhat different than what I have expected/shown... Please excuse me, let me explain this way...

For example, the query id: 27504.m000612 matches with Raf29 having the overall highest %Gene identity (70.5%). We don't want Raf29 to be recognized again for 27471.m000401 (which has a lower %Gene identity than 27504.m000612), so it will be matched to the following highest %Gene identity which is Raf21 (69.9%), this will exclude Raf29 in order to avoid duplication.

 

[Eric W]

Except for order, my results are the same as your example. I had assumed that the Raf21 (69.9%) entry was excluded because you wanted at most 1 gene per query. Is this not so?

 

[rajamdade]

Except for order, my results are the same as your example. I had assumed that the Raf21 (69.9%) entry was excluded because you wanted at most 1 gene per query. Is this not so?

Yeah, I was able to resolve the issue, now I have added the formula for G and H columns also, kindly let me know whether the formula is fetching correctly (looks though...).

TEST.xlsx
	A	B	C	D	E	F	G	H	I
1	Query ID	Accession	Gene	% Gene Identity		Query ID	Accession	Gene	% Gene Identity
2	27471.m000401	AT4G35780	Raf29	69.1		27504.m000612	AT4G35780	Raf29	70.5
3	27471.m000401	AT2G17700	Raf21	68.9		27471.m000401	AT2G17700	Raf21	68.9
4	27471.m000401	AT4G38470	Raf30	66.3		27504.m000613	AT4G38470	Raf30	66.2
5	27504.m000612	AT4G35780	Raf29	70.5		27894.m000774	AT4G38470	Raf30	66.2
6	27504.m000612	AT2G17700	Raf21	69.9
7	27504.m000612	AT3G06630	Raf8	68.4
8	27504.m000612	AT4G38470	Raf30	67.3
9	27504.m000613	AT4G35780	Raf29	70
10	27504.m000613	AT2G17700	Raf21	67.4
11	27504.m000613	AT4G38470	Raf30	66.2
12	27894.m000774	AT4G38470	Raf30	66.2
13	27894.m000774	AT4G14780	Raf26	64.8
14	27894.m000774	AT4G35780	Raf29	63.7
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$14)/($D$2:$D$14=$I2),COUNTIF($I$2:$I2,$I2)))
G2:G5	G2	=INDEX($B$2:$B$14,MATCH(F2&I2, $A$2:$A$14&$D$2:$D$14,0))
H2:H5	H2	=INDEX($C$2:$C$14,MATCH(2,1/($A$2:$A$14=F2)/($D$2:$D$14=I2)))
I2:I5	I2	=AGGREGATE(14,6,D$2:D$14/(COUNTIFS($F$1:$F1,$A$2:$A$14)=0)/(COUNTIFS($H$1:$H1,$C$2:$C$14,$I$1:$I1,"<>"&$D$2:$D$14)=0),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$D$26	F2:F5

 

[Eric W]

My original F2 formula was designed to work in columns G and H as well. I'd recommend sticking with that formula if the values in G and H can change. But in looking at your sample table, B11 = B12 and C11 = C12, so if that is consistent then your formulas should work as well.

In any case, I'm glad it works for you! Let us know if you have any other questions.