Results 1 to 4 of 4

Thread: Sort formula that also handles duplicates
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sort formula that also handles duplicates

    I have a table with 4 columns and information in each column can repeat where:
    Column 1 is "NAMES"
    Column 2 is "Start date"
    Column 3 is "End date"
    Column 4 is "Description"

    I initially used the built in sort and kept replying to handle this but after a while it became troublesome as there is a lot of downtime when using "Reapply". More insight on this table is that you input data here and it appears in a calendar on another sheet, how my formula for this works is that it only accepts the unique information at the top of the table but I would like data to be overwritten instead of having to search for it in the table and this is where the sort formula comes in. Any ideas on how I can get this done?

  2. #2
    New Member
    Join Date
    Jul 2019
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort formula that also handles duplicates

    I'm currently using a custom sort formula made with if statements:
    =IF(E26="Aa";1;IF(E26="Ap";3;IF(E26="Ar";2;IF(E26="Os";9;IF(E26="Ts";10;IF(E26="Ps";11;IF(E26="Ss";12;IF(E26="J";4;IF(E26="Es";5;IF(E26="IL";6;IF(E26="Sp";8;IF(E26="Stl";8;IF(E26="El";17;IF(E26="Elh";18;IF(E26="Sl";19;IF(E26="Slh";20;IF(E26="Slc";21;IF(E26="Slch";22;IF(E26="LS";23;IF(E26="P";13;IF(E26="O";14;IF(E26="S";15;IF(E26="T";16;"X")))))))))))))))))))))))

    The problem with this is that I can't use index match to sort because of the duplicates, wherever there is a duplicate, I get "#N/A" so I'm not able to replicate the table in a sorted form. Please let me know if there's any information I'm leaving out. Also, this is the index match formula that is giving me the errors:

    =INDEX($B$31:$F$36;MATCH(ROWS($H$26:H26);$F$31:$F$36;0);1)

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,326
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Sort formula that also handles duplicates

    You could give examples of what you have and the expected result.
    Regards Dante Amor

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,604
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Sort formula that also handles duplicates

    Consider the CSE formula
    =INDEX($C:$C, 1000*MOD(SMALL(COUNTIF(B:B,"<="&$B$1:$B$5)+ROW($B$1:$B$5)/1000,ROW(A1)),1), 1)

    put it in a cell and it will return the value from column C that goes with the smallest value in column B.

    drag it down and it will return the second smallest value from column B etc, note that it will account for duplicate entries in column B.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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