I am having problem in sorting text with numbers as described in below pic:

I am using Excel 2016, I am using below array formula in D2:
=IFERROR(SMALL(IF((COUNTIF($D$1:D1, $A$1:$A$10)=0)*ISNUMBER($A$1:$A$10), $A$1:$A$10, "A"), 1), INDEX($A$1:$A$10, MATCH(SMALL(IF(ISTEXT($A$1:$A$10)*(COUNTIF($D$1:D1, $A$1:$A$10)=0), COUNTIF($A$1:$A$10, "<"&$A$1:$A$10), ""), 1), IF(ISTEXT($A$1:$A$10), COUNTIF($A$1:$A$10, "<"&$A$1:$A$10), ""), 0)))
Best Regards,
S

I am using Excel 2016, I am using below array formula in D2:
=IFERROR(SMALL(IF((COUNTIF($D$1:D1, $A$1:$A$10)=0)*ISNUMBER($A$1:$A$10), $A$1:$A$10, "A"), 1), INDEX($A$1:$A$10, MATCH(SMALL(IF(ISTEXT($A$1:$A$10)*(COUNTIF($D$1:D1, $A$1:$A$10)=0), COUNTIF($A$1:$A$10, "<"&$A$1:$A$10), ""), 1), IF(ISTEXT($A$1:$A$10), COUNTIF($A$1:$A$10, "<"&$A$1:$A$10), ""), 0)))
Best Regards,
S
Attachments
Last edited: