1

I am using Excel 2013 on Windows 7.

I have 2 columns of data like this:

Name Value
aaa  1
bbb  123
ccc  81
...  ...

I need to sort this data into specific order that looks like random so I cannot use basic criteria like alphabet to sort it. Number of lines is quite high so it does not make sense to do the ordering manually.

There are no duplicates in the Name column.

I tried using Custom list of Excel sorting tool but apparently my list is too long to fit into it. (The list gets cut off at some point.)

I have third column with the desired order for data:

Desired order
bbb
aaa
ccc
...

How can I sort the data I have into order given by the third row?

1
  • 1
    Your names are already in the right order, just use vlookup() to get the value.
    – Kyle
    Jan 20, 2016 at 17:49

1 Answer 1

3

Similar to what @Kyle mentioned in his comment, you should add a column that looks up the Name in the Desired order list. I recommend using MATCH instead of VLOOKUP, though.

If your data is in A:B and the order list is over in Z:Z, then the formula for C2 would be:

=MATCH($A2,$Z:$Z,0)

If a Name is not in the list, it'll return an #N/A error. You probably want to see that since you want things in a specific order that appears random instead of an actual random order. Otherwise, we could shove all those to the end:

=IFERROR(MATCH($A2,$Z:$Z,0),COUNTA($Z:$Z)+1)

... or stick them somewhere in the middle:

=IFERROR(MATCH($A2,$Z:$Z,0),RANDBETWEEN(1,COUNTA($Z:$Z)))

No matter which route you pick to get your list, you then sort based on that new column of numbers to get the order you want.

1
  • I found this function easier to manage than Vlookup. Jan 21, 2016 at 6:56

You must log in to answer this question.

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