Results 1 to 5 of 5

Index, aggregate and return the highest or following highest value (Avoid duplicates)

Threaded View

rajamdade Index, aggregate and return... 04-22-2021, 12:02 PM
Limor_OP Re: Index, aggregate and... 04-22-2021, 01:43 PM
rajamdade Re: Index, aggregate and... 04-22-2021, 01:59 PM
Limor_OP Re: Index, aggregate and... 04-24-2021, 12:47 PM
rajamdade Re: Index, aggregate and... 04-25-2021, 05:27 AM
  1. #1
    Registered User
    Join Date
    06-21-2014
    Location
    India
    MS-Off Ver
    MS Excel 2016
    Posts
    34

    Arrow Index, aggregate and return the highest or following highest value (Avoid duplicates)

    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 even though it has a higher %Gene identity for Raf21 (69.9%) than that of 27471.m000401 (Raf21: 68.9%). Now, the query ID: 27471.m000401, could be allotted with the following/successive highest (which is 2nd in this case) Raf21 (68.9%) [This is to Avoid alloting duplicate genes to the following query IDs]

    Let me explain this in another way, For example, the query id: 27504.m000612 matches with the gene 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.

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

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

    I have also uploaded the following sample test data image as an excel attachment.

    Screenshot_2021-04-22.png
    Attached Files Attached Files
    Last edited by rajamdade; 04-22-2021 at 01:57 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Trying to use =AGGREGATE to find the highest number
    By tom hatten in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-26-2018, 12:37 PM
  2. Remove Highest Outlier, Return Highest Value
    By RACHAN in forum Excel General
    Replies: 4
    Last Post: 02-22-2017, 12:26 PM
  3. Replies: 3
    Last Post: 06-09-2016, 12:51 PM
  4. [SOLVED] Max formula to return total of highest, second highest and third highest value
    By JonWilf in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-06-2016, 08:20 AM
  5. [SOLVED] Index Match return highest value in repeated matches
    By izk630 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-30-2015, 04:09 PM
  6. Index Match return highest value in repeated matches
    By izk630 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-29-2015, 12:57 PM
  7. Replies: 3
    Last Post: 08-10-2006, 11:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1