-1

I would like to order two columns off another two columns.

Consider the image:

Excel Image

I would like to order column C and D based off column J and K. For example, the order should go something like John Brew, Bob Alan etc.

I would like to directly manipulate the data in the red box and ensure all the other "INFO 1,2,3,4" reflect the change in order.

Desired results:

Excel Image Desired Result

Any names that are not matched, simply go at the bottom.

Improve this question
1
  • I don’t understand what you want. Can you explain what you want? … … … … … … … … … … … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
    – Scott - Слава Україні
    Commented Nov 24, 2021 at 5:49

3 Answers 3

Reset to default
0

In the table on the right, add a column to join the names together, eg in column L:

=$J2&$K2

In your main table, add a column to find which name is being referred to. In column G (or wherever suits you), enter:

=IFERROR(MATCH($C2&$D2,$G$2:$G$999,0),9999)

So for every name that is found, you now know what row it is on, and for names with no match they have the value 9999 (or some number you choose which is always higher than the number of possible names).

Now sort your original data on column G.

Improve this answer
-1

Give everybody Firstname ID and LastName ID (your oreder list), then add ID columns also in your data table. Then VLOOKUP the ID values, you get what you want!

Improve this answer
2
  • Can you clarify what you mean?
    – Edu Py
    Commented Nov 23, 2021 at 8:40
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Nov 23, 2021 at 9:06
-1

Do you have Microsoft 365 Apps?

If yes, you could use Xlookup function to find the data contained in right table. Such as =XLOOKUP($I2&$J2,$C$2:$C$7&$D$2:$D$7,$A$2:$F$7), drag down to get other data.

enter image description here

Then you could use array formula to find the data that not included in the table on the right. Such as =INDEX(A$1:A$7,SMALL(IF(COUNTIF($I$2:$S$6,$C$2:$C$7)*COUNTIF($J$2:$J$6,$D$2:$D$7)=0,ROW(C$2:$C$7),"false"),ROW(A1))), enter this formula under the result above.

enter image description here

Improve this answer
3
  • Hi Emily, is it possible to directly manipulate the table rather than create a new one? The reason why I'm asking is because my original table is is very very long and the ordered list of names is also very long.
    – Edu Py
    Commented Nov 23, 2021 at 19:09
  • So in short, directly manipulate the array A2:F7 using I2:J6.
    – Edu Py
    Commented Nov 23, 2021 at 19:14
  • @Edu Py, Sorry for my poor English, if you have better formulas, I am glad that you share with me.
    – Emily
    Commented Nov 24, 2021 at 10:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .