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

1. ## 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. ## 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. ## Re: Sort formula that also handles duplicates

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

4. ## 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.