Dynamic Sorting

try

Formula:

Please Login or Register  to view this content.

- formula start in B2
- your data has header (A1) or start in A2
- range $A$2:$A$20 should be bigger than existing data in col. A
- formula should be entered by Control+Shift+Enter
- drag formula down as far as you need.
Hint:
- You can change range $A$2:$A$20 to Name (Name Manager) and it will be easier to use
- change range $A$2:$A$20 suitably to your need
- after all change range of your original data to the Excel Table*** (in column A) and do the same with data in column B independently.

Hope it will help

***Excel Table: Select data in column==>Menu: Insert==>Table

Here's another method...

Create Named Range

• Ctrl + F3 (Opens Name Manager)
• New
• Name: List
• Refers to: =Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))
• OK

In B2 copied down as far as you need...

=IFERROR(INDEX(List,MATCH(SMALL(COUNTIF(List,"<"&List),ROW(1:1)),COUNTIF(List,"<"&List),0)),"")

IMPORTANT

• This is an array formula
• Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
• If entered correctly, the formula will be enclosed in {brackets}
• Do not enter the {brackets} manually