Excel: Finding difference between 2 columns when rows are not matched
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
microsoft-excel
asked Jan 7 at 23:22
charucharu
1033
add a comment |
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
microsoft-excel
asked Jan 7 at 23:22
charucharu
1033
add a comment |
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
microsoft-excel
asked Jan 7 at 23:22
charucharu
1033
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
microsoft-excel
microsoft-excel
asked Jan 7 at 23:22
charucharu
1033
asked Jan 7 at 23:22
charucharu
1033
edited Jan 7 at 23:34
charu
asked Jan 7 at 23:22
charucharu
1033
asked Jan 7 at 23:22
charucharu
1033
asked Jan 7 at 23:22
charucharu
1033
1033
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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
answered Jan 7 at 23:39
BrianBrian
3475
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
|
show 4 more comments
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1391708%2fexcel-finding-difference-between-2-columns-when-rows-are-not-matched%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Jan 7 at 23:39
BrianBrian
3475
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
|
show 4 more comments
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
answered Jan 7 at 23:39
BrianBrian
3475
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
|
show 4 more comments
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
answered Jan 7 at 23:39
BrianBrian
3475
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
answered Jan 7 at 23:39
BrianBrian
3475
edited Jan 8 at 21:55
answered Jan 7 at 23:39
BrianBrian
3475
answered Jan 7 at 23:39
BrianBrian
3475
answered Jan 7 at 23:39
BrianBrian
3475
3475
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
|
show 4 more comments
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
For some reason after i sort on F, it then immediately unsorts after a split second
– charu
Jan 8 at 2:59
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
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, 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
@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
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
@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
|
show 4 more comments
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1391708%2fexcel-finding-difference-between-2-columns-when-rows-are-not-matched%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
