Finding all the non empty rows in a column depending on user input in custom order.

I have user input in col B2 and output from A14 (containing formula) . I have managed to extract empty rows based on user input (doesnt work if country name repeats and adjacent rows are empty) . I want to sort the data in a order . My expected output is from E14.

...I want to sort the data in a order

which is ?

you don't stipulate how you arrived at your expected results -- so, we can guess Z-A, 0-9 but that may just be coincidence given sample results.

also, can you confirm you're using XL2007, and not XL2010 or later?

By "custom sort" I'm assuming you actually mean, return all records for given country in the order in which that country first appears in the list

If so, given XL2007, I would advise following:

Formula:

D13: "some text"

D14: =IF($D13="","",IFERROR(MOD(SMALL(INDEX(MATCH($G$3:$G$11&" ",$G$3:$G$11&" ",0)*10^7+ROW($G$3:$G$11)+9.99E+307*(N(+INDEX($J$3:$V$11,0,MATCH($B$2,$J$1:$V$1,0)))=0),0),ROWS(D$14:D14)),10^6),""))

copied down

modify ranges to suit

then

A14: =IF($D14="","",INDEX($G:$G,$D14))

B14: =IF($D14="","",INDEX(J:V,$D14,MATCH($B$2,$J$1:$V$1,0))) --> and copied to C14

then copy down

if you choose to not isolate the row # in D, once, you will end up repeating the same (potentially expensive calc) repeatedly, which is not a good idea.
(this is also why we have a header in row D13 - such that we can test prior result, and cease once we know that all results have been returned)

if the above doesn't generate the desired results in real-life, (i.e. it will replicate your expected results), post back with a more detailed sample - with multiple scenarios.

Thank you . Works fine as of now.