On Excel, I have a list of items on column A that's ordered, and a list of corresponding items on column B that's unordered. Column A's items each contain a keyword that's to be matched to their corresponding item in column B. How do I reorder column B's items so that they match the order in column A?
My current data:
| Column A | Column B |
|---|---|
| Table | 3_Chair |
| Chair | 2_Eraser |
| Pen | 4_Table |
| Eraser | 1_Pen |
Desired output:
| Column A | Column B |
|---|---|
| Table | 4_Table |
| Chair | 3_Chair |
| Pen | 1_Pen |
| Eraser | 2_Eraser |
CodePudding user response：
You can simply sort that second column, but you need to select it first (don't select the title), like in this simple example (also mind the choice I made in the question):
Result:
CodePudding user response：
With ms365, try:
Formula in D1:
=LET(x,A1:A4,y,B1:B4,HSTACK(x,SORTBY(y,MATCH(TEXTAFTER(y,"_"),x,0))))