• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SORTING USING VLOOKUP AND COUNTIF

M

meshark

New Member
Thank you for your post on how to sort in excel using the countif and vlookup, I tried it and it worked, however the formula breaks down when there are dupplicate names. How would you fix this, please. These are the formulae I used

(A)=IF(G5="","",COUNTIF($C$3:$C$2000,"<="&G5)) (B)=IF(D3="","",VLOOKUP(ROW()-ROW($C$2),$B$3:$C$2000,2,FALSE))

Thank you.
 
Welcome to the forums!

I assume this is the article you are talking about:

http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/


Did Chandoo's formula of:

=VLOOKUP(small(SORT ORDER COLUMN,1),SORT ORDER TABLE,2,FALSE)


Not work?

Also, in chandoo's article, the Countif function uses the same column for all arguments, while your's uses C and G. Is there a reason for this?
 
Thank you Luke, the G was an error on my part, I regret it. With these formulae

=IF(F20="","",COUNTIF($C$3:$C$2000,"<="&F20))......For the sort order

=IF(C20="","",VLOOKUP(SMALL($B20,1),$B$3:$C$2000,2,FALSE))......For Sorted Names

my concern is yet to be resolved. Please if you have it figured out, do let me know.

Thank you.
 
Again, not sure why the COUNTIF formula is not:

=IF(C20="","",COUNTIF($C$3:$C$2000,"<="&C20))

I'm assuming this formula is in column B


But your 2nd formula needs to callout entire column within the SMALL function would be:

=VLOOKUP(SMALL(B:B,ROW(A1)),$B$3:$C$2000,2,FALSE)

Copy this down as needed. Note that I modified Chandoo's formula by using the ROW function so that it automatically chooses the 1st smallest, then 2nd smallest, etc. Also, since the first formula is already sorting out the blanks, that part of the formula is not needed.


Making a large guess, if you're needing to do the VLOOKUP to pull in other data besides the sorted list (say, you sorted names, but also want to have additional columns sorted), we need an extra helper column with this formula:

=RANK(B3,$B$3:$B$6)+COUNTIF($C$2:C2,C3)

Again, this assumes that col B has the first COUNTIF formula. Now, change the VLOOKUP formula to reference this new helper column (I'll assume you used column A) like so:

=VLOOKUP(SMALL(A:A,ROW(A1)),$A$3:$C$2000,3,FALSE)
 
You must log in or register to reply here.
Back
Top