Sorting all Rows by Matching Two Columns

UPDATE: Evidently a custom sort works fine if one uses WORDS in Column A and B. Instead I am using Numbers formatted as text, thus the Custom sort does NOT work. Is formatting numbers as Text the same as them BEING text? If not, how does one make a 5-digit number into TEXT for purposes of sorts such as this? Is that possible?

The Custom Sort Order does solve my problem if Column A said Apple, Bear, Tree and Column B said Tree, Apple, Bear – I could sort Column B in Column A order, but if Column A is 3-1-2 and Column B is 2-1-3, the Custom Sort will not work with those numbers even IF formatted as text. That is the core issue. The custom sort otherwise (with words) would work fine!

This seems harder than it should be!

You have a spreadsheet with say 6 columns and 100 rows.

Column A has a unique 5 digit key for each record in perfect row (driving) order.
Column B has the same keys, but they are not in the same row order.

Just want to match Column B’s Key to Column A’s Key and shuffle the rows into that same order.

Column A has 2-1-3
Column B has 3-1-2

Just want to make column B (and all the other columns in the same row) match up in the same order in Column A. Don’t want to change Column A - just want to order all the rows in sync with it, but using Column B as the Key.

So in the end:

Column A has 2-1-3
Column B has 2-1-3

Match rows using key in Column B to align with values in Stationary Column A.

I tried a Custom Sort using Column A as custom Sort list. When I do the sort, it does not seem to order the rows in that custom order.

So stuck!

Just use addition column with simple formula like as =MATCH(B2;A:A;0)


Woe…This is awesome!!! Would absolutely work…! The custom sort would too, but it won’t do so with my 5 digit numbers, just words, even though my numbers are formatted as TEXT. Is there a way to convert the numbers to text so that it views them as words – the custom sort would then work perfectly, though your solution would also be awesome!!! Thank you so much!!!

What about find .+ (dot-plus) and replace it to '& (apostrophe-ampersand) with regular expression?

Thank you John! That might also work! Will try at some point. For now…your 1st solution worked as well as converting the whole column to Text before attempting the custom sort. Thank you so much for amazing screen recording and awesome solution!!! Really amazed! Thank you!!!

Thank you All!

The solution posed by JohnSun was awesome!!!

Another solution was to convert the column of numbers to Text using these instruction:

…and then I was able to Use Column A as a Custom Sort Range…and this made it possible to Sort Column B against Column A. The core problem was that Column A and B were Numbers and the Custom Sort only works with Text.

FORMATTING as Text is insufficient!

Must follow these instructions: How do you convert numbers to text?

Thank you all!!

If the answer solves your question please tick the :heavy_check_mark:.