Can Excel sort on correspondence to another column?

Answer 1

There's no practical way to do this in 1 step with a formula exactly as you want. You CAN do it, but it would be so much obfuscated INDEX/ROW stuff that it's not readable. Are you able to separate it into multiple steps, or do transformations after excel? Depending on what your context is, different solutions will be more practical than others.

Assuming your sheet is like:

 A       B
---     ---
 A       Q
 B       E
 C       B
 D       W
 E       A

I can do it in 3 added columns:

1. Find matching values, and rows to skip, with the formula (put in C2) ="0"&IFERROR(VLOOKUP(A2,$B:$B,1,FALSE),"").
2. Find non-matches with the formula (put in D2) =IF(ISNA(VLOOKUP(B2,$A:$A,1,FALSE)),"1"&B2,"")
3. Merge them together, column C before column D, with formula in E2: =IF(ROW(E2)<=COUNTA(C:C),C2,INDEX(D:D,ROW(E2)-COUNTA(C:C)+1)). Note that the +1 is because I assume there's a column header.

This results in:

 A       B       C        D       E
---     ---     ---      ---     ---
 A       Q       0A       1Q      0A
 B       E       0B               0B
 C       B       0                0
 D       W       0        1W      0
 E       A       0E               0E
                                  1Q


                                  1W

Now you can copy/paste as text column E somewhere, sort it, and remove the 1st char. Adding the 0 and 1 allows column E to be sorted in this way. If you don't do it this way, you won't be able to discriminate between "removing blanks" and the desirable blank rows.

That's a lot of steps to do this; you may consider writing a macro to do it if you need to automate this more.