0

Column A: 1000 unique names

Column B: A number corresponding with each name in A

Column C: Same 1000 names as A (different order than A, since values in D have changed)

Column D: A number for each name from C (numbers have changed from B)

At the moment, A/B and C/D are sorted from largest to smallest.

I want to create column E with the same 1000 names, but sorted by the difference between the values corresponding with each name from columns B and D. That Difference will be in column F.

Some values have changed a lot, so a name at the top of the column A may be way down the list in column C.

What is the easiest way to do this?

Thank you

2

column E:

(copy of column A)

column F:

=vlookup(E1,A:B,2,false)-vlookup(E1,C:D,2,false)

Then sort E:F on F.

Extra notes by fixer1234

  • For some reason after i sort on F, it then immediately unsorts after a split second – charu Jan 8 at 2:59
  • back to the same order as column A – charu Jan 8 at 3:05
  • @charu, it sounds like you missed the step about copy/paste-special values. Col F should contain nothing but numbers, so there's no basis for the sort to change to anything else. If they still contain formulas, the formulas will update. – fixer1234 Jan 8 at 7:21
  • @charu, if you want to sort it, you'll have to highlight both column E&F, and paste as values in G&H, then sort H. If no point in directly sorting column F since the content is formulas (the same for all column F cell).. not values (that we normally sort with..). – p._phidot_ Jan 8 at 7:48
  • 3
    @charu, If it's resorting, it sounds like col E is still showing =A1. No matter how you sort E and F, E will still point to the same order as A. Instead of =A1, just copy and paste the col A values in col E to get the list there. Then in col F use =vlookup(E1,A:B,2,false)-vlookup(E1,C:D,2,false). Then sort E:F on F. If the values in B or D change, you will need to resort E:F because sorts don't automatically update. Otherwise, you will need VBA to automatically resort. – fixer1234 Jan 8 at 20:54

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.