Excel matching between combination of columns
I have the following data on an excel :
Sheet1 :
id effectiveDate rateValue CrTime
20 20130627 0.08 6/28/2013 23:05
20 20130626 0.1 6/28/2013 6:23
Sheet 2:
id effectiveDate rateValue CrTime
20 20130630 0.08 6/30/2013 18:14
20 20130628 0.08 6/28/2013 9:38
20 20130627 0.1 6/27/2013 18:14
I want another 1 column on Sheet 2 which should do the following match - If there is a corresponding match in Sheet 1 for the combo of id+effectiveDate, then output the value.
So expected output on Sheet 2
id effectiveDate rateValue CrTime Sheet1RateValue
20 20130630 0.08 6/30/2013 18:14 Absent
20 20130628 0.08 6/28/2013 9:38 0.08
20 20130627 0.1 6/27/2013 18:14 0.1
microsoft-excel vlookup
asked Jul 1 '13 at 5:11
AchowAchow
10111
migrated from stackoverflow.com Jul 1 '13 at 13:38
This question came from our site for professional and enthusiast programmers.
add a comment |
I have the following data on an excel :
Sheet1 :
id effectiveDate rateValue CrTime
20 20130627 0.08 6/28/2013 23:05
20 20130626 0.1 6/28/2013 6:23
Sheet 2:
id effectiveDate rateValue CrTime
20 20130630 0.08 6/30/2013 18:14
20 20130628 0.08 6/28/2013 9:38
20 20130627 0.1 6/27/2013 18:14
I want another 1 column on Sheet 2 which should do the following match - If there is a corresponding match in Sheet 1 for the combo of id+effectiveDate, then output the value.
So expected output on Sheet 2
id effectiveDate rateValue CrTime Sheet1RateValue
20 20130630 0.08 6/30/2013 18:14 Absent
20 20130628 0.08 6/28/2013 9:38 0.08
20 20130627 0.1 6/27/2013 18:14 0.1
microsoft-excel vlookup
asked Jul 1 '13 at 5:11
AchowAchow
10111
migrated from stackoverflow.com Jul 1 '13 at 13:38
This question came from our site for professional and enthusiast programmers.
1
What have you tried, and what problems did you have? Questions which are just a list of requirements tend to get closed...
– Tim Williams
Jul 1 '13 at 5:19
add a comment |
I have the following data on an excel :
Sheet1 :
id effectiveDate rateValue CrTime
20 20130627 0.08 6/28/2013 23:05
20 20130626 0.1 6/28/2013 6:23
Sheet 2:
id effectiveDate rateValue CrTime
20 20130630 0.08 6/30/2013 18:14
20 20130628 0.08 6/28/2013 9:38
20 20130627 0.1 6/27/2013 18:14
I want another 1 column on Sheet 2 which should do the following match - If there is a corresponding match in Sheet 1 for the combo of id+effectiveDate, then output the value.
So expected output on Sheet 2
id effectiveDate rateValue CrTime Sheet1RateValue
20 20130630 0.08 6/30/2013 18:14 Absent
20 20130628 0.08 6/28/2013 9:38 0.08
20 20130627 0.1 6/27/2013 18:14 0.1
microsoft-excel vlookup
asked Jul 1 '13 at 5:11
AchowAchow
10111
I have the following data on an excel :
Sheet1 :
id effectiveDate rateValue CrTime
20 20130627 0.08 6/28/2013 23:05
20 20130626 0.1 6/28/2013 6:23
Sheet 2:
id effectiveDate rateValue CrTime
20 20130630 0.08 6/30/2013 18:14
20 20130628 0.08 6/28/2013 9:38
20 20130627 0.1 6/27/2013 18:14
I want another 1 column on Sheet 2 which should do the following match - If there is a corresponding match in Sheet 1 for the combo of id+effectiveDate, then output the value.
So expected output on Sheet 2
id effectiveDate rateValue CrTime Sheet1RateValue
20 20130630 0.08 6/30/2013 18:14 Absent
20 20130628 0.08 6/28/2013 9:38 0.08
20 20130627 0.1 6/27/2013 18:14 0.1
microsoft-excel vlookup
microsoft-excel vlookup
asked Jul 1 '13 at 5:11
AchowAchow
10111
asked Jul 1 '13 at 5:11
AchowAchow
10111
asked Jul 1 '13 at 5:11
AchowAchow
10111
asked Jul 1 '13 at 5:11
AchowAchow
10111
asked Jul 1 '13 at 5:11
AchowAchow
10111
10111
migrated from stackoverflow.com Jul 1 '13 at 13:38
This question came from our site for professional and enthusiast programmers.
migrated from stackoverflow.com Jul 1 '13 at 13:38
This question came from our site for professional and enthusiast programmers.
1
What have you tried, and what problems did you have? Questions which are just a list of requirements tend to get closed...
– Tim Williams
Jul 1 '13 at 5:19
add a comment |
1
What have you tried, and what problems did you have? Questions which are just a list of requirements tend to get closed...
– Tim Williams
Jul 1 '13 at 5:19
1
1
What have you tried, and what problems did you have? Questions which are just a list of requirements tend to get closed...
– Tim Williams
Jul 1 '13 at 5:19
What have you tried, and what problems did you have? Questions which are just a list of requirements tend to get closed...
– Tim Williams
Jul 1 '13 at 5:19
add a comment |
1 Answer
1
active
oldest
votes
If there concatenate of id+effective date is unique, SUMIFS() can be used instead of VLOOKUP() here since we're dealing with numbers.
I assume that the data are in columns A through D and starting at row 2.
=IF(SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)=0,
"Absent",
SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)
)
Note that you named the second sheet Sheet 2 there instead of the default Sheet2 (the spaces does make a difference. Change the reference as necessary.
So, this formula will sum the rates for a given id+effective date. If there are two similar combination of id+effective date in Sheet1, then the formula will sum the two rates. That's the difference you'll get from a vlookup.
Otherwise, if you absolutely need vlookup, I would suggest creating two helper column, one for the reference and one for the lookup, the first in Sheet 2 and the second in Sheet1.
answered Jul 1 '13 at 6:35
JerryJerry
4,618827
the prob. is if we have a ID of 1 and an effectiveDate 20130630 and we have an ID 2 and effectiveDate 20130629, aren't they gonna give the same, incorrect results?
– Achow
Jul 2 '13 at 4:53
@anirbanchowdhury SUMIFS() is adding the rates of cells with the same ID and same effective date. In this case, ID1and EffDate20130630will give a different result than ID2and EffDate20130629.
– Jerry
Jul 2 '13 at 5:01
add a comment |
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%2f614166%2fexcel-matching-between-combination-of-columns%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
If there concatenate of id+effective date is unique, SUMIFS() can be used instead of VLOOKUP() here since we're dealing with numbers.
I assume that the data are in columns A through D and starting at row 2.
=IF(SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)=0,
"Absent",
SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)
)
Note that you named the second sheet Sheet 2 there instead of the default Sheet2 (the spaces does make a difference. Change the reference as necessary.
So, this formula will sum the rates for a given id+effective date. If there are two similar combination of id+effective date in Sheet1, then the formula will sum the two rates. That's the difference you'll get from a vlookup.
Otherwise, if you absolutely need vlookup, I would suggest creating two helper column, one for the reference and one for the lookup, the first in Sheet 2 and the second in Sheet1.
answered Jul 1 '13 at 6:35
JerryJerry
4,618827
the prob. is if we have a ID of 1 and an effectiveDate 20130630 and we have an ID 2 and effectiveDate 20130629, aren't they gonna give the same, incorrect results?
– Achow
Jul 2 '13 at 4:53
@anirbanchowdhury SUMIFS() is adding the rates of cells with the same ID and same effective date. In this case, ID1and EffDate20130630will give a different result than ID2and EffDate20130629.
– Jerry
Jul 2 '13 at 5:01
add a comment |
If there concatenate of id+effective date is unique, SUMIFS() can be used instead of VLOOKUP() here since we're dealing with numbers.
I assume that the data are in columns A through D and starting at row 2.
=IF(SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)=0,
"Absent",
SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)
)
Note that you named the second sheet Sheet 2 there instead of the default Sheet2 (the spaces does make a difference. Change the reference as necessary.
So, this formula will sum the rates for a given id+effective date. If there are two similar combination of id+effective date in Sheet1, then the formula will sum the two rates. That's the difference you'll get from a vlookup.
Otherwise, if you absolutely need vlookup, I would suggest creating two helper column, one for the reference and one for the lookup, the first in Sheet 2 and the second in Sheet1.
answered Jul 1 '13 at 6:35
JerryJerry
4,618827
the prob. is if we have a ID of 1 and an effectiveDate 20130630 and we have an ID 2 and effectiveDate 20130629, aren't they gonna give the same, incorrect results?
– Achow
Jul 2 '13 at 4:53
@anirbanchowdhury SUMIFS() is adding the rates of cells with the same ID and same effective date. In this case, ID1and EffDate20130630will give a different result than ID2and EffDate20130629.
– Jerry
Jul 2 '13 at 5:01
add a comment |
If there concatenate of id+effective date is unique, SUMIFS() can be used instead of VLOOKUP() here since we're dealing with numbers.
I assume that the data are in columns A through D and starting at row 2.
=IF(SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)=0,
"Absent",
SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)
)
Note that you named the second sheet Sheet 2 there instead of the default Sheet2 (the spaces does make a difference. Change the reference as necessary.
So, this formula will sum the rates for a given id+effective date. If there are two similar combination of id+effective date in Sheet1, then the formula will sum the two rates. That's the difference you'll get from a vlookup.
Otherwise, if you absolutely need vlookup, I would suggest creating two helper column, one for the reference and one for the lookup, the first in Sheet 2 and the second in Sheet1.
answered Jul 1 '13 at 6:35
JerryJerry
4,618827
If there concatenate of id+effective date is unique, SUMIFS() can be used instead of VLOOKUP() here since we're dealing with numbers.
I assume that the data are in columns A through D and starting at row 2.
=IF(SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)=0,
"Absent",
SUMIFS(Sheet1!C:C,Sheet1!A:A,'Sheet 2'!A2,Sheet1!B:B,'Sheet 2'!B2)
)
Note that you named the second sheet Sheet 2 there instead of the default Sheet2 (the spaces does make a difference. Change the reference as necessary.
So, this formula will sum the rates for a given id+effective date. If there are two similar combination of id+effective date in Sheet1, then the formula will sum the two rates. That's the difference you'll get from a vlookup.
Otherwise, if you absolutely need vlookup, I would suggest creating two helper column, one for the reference and one for the lookup, the first in Sheet 2 and the second in Sheet1.
answered Jul 1 '13 at 6:35
JerryJerry
4,618827
answered Jul 1 '13 at 6:35
JerryJerry
4,618827
answered Jul 1 '13 at 6:35
JerryJerry
4,618827
answered Jul 1 '13 at 6:35
JerryJerry
4,618827
4,618827
the prob. is if we have a ID of 1 and an effectiveDate 20130630 and we have an ID 2 and effectiveDate 20130629, aren't they gonna give the same, incorrect results?
– Achow
Jul 2 '13 at 4:53
@anirbanchowdhury SUMIFS() is adding the rates of cells with the same ID and same effective date. In this case, ID1and EffDate20130630will give a different result than ID2and EffDate20130629.
– Jerry
Jul 2 '13 at 5:01
add a comment |
the prob. is if we have a ID of 1 and an effectiveDate 20130630 and we have an ID 2 and effectiveDate 20130629, aren't they gonna give the same, incorrect results?
– Achow
Jul 2 '13 at 4:53
@anirbanchowdhury SUMIFS() is adding the rates of cells with the same ID and same effective date. In this case, ID1and EffDate20130630will give a different result than ID2and EffDate20130629.
– Jerry
Jul 2 '13 at 5:01
the prob. is if we have a ID of 1 and an effectiveDate 20130630 and we have an ID 2 and effectiveDate 20130629, aren't they gonna give the same, incorrect results?
– Achow
Jul 2 '13 at 4:53
the prob. is if we have a ID of 1 and an effectiveDate 20130630 and we have an ID 2 and effectiveDate 20130629, aren't they gonna give the same, incorrect results?
– Achow
Jul 2 '13 at 4:53
@anirbanchowdhury SUMIFS() is adding the rates of cells with the same ID and same effective date. In this case, ID
1 and EffDate 20130630 will give a different result than ID 2 and EffDate 20130629.– Jerry
Jul 2 '13 at 5:01
@anirbanchowdhury SUMIFS() is adding the rates of cells with the same ID and same effective date. In this case, ID
1 and EffDate 20130630 will give a different result than ID 2 and EffDate 20130629.– Jerry
Jul 2 '13 at 5:01
add a comment |
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%2f614166%2fexcel-matching-between-combination-of-columns%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
1
What have you tried, and what problems did you have? Questions which are just a list of requirements tend to get closed...
– Tim Williams
Jul 1 '13 at 5:19