Reconciling differences when data repeats in a sheet
In the example data, say we have Company 1 and Company 2. Under each company is the state in which employees work, followed by the name of the employee(s) which work(s) in that state. In the column next to this, we have the payroll, by Company, state and employee(s), each.
This data is pulled from two different sources, which provide different payroll data. I am trying to reconcile these differences, to see exactly where payroll differences are. I've been guided to use =IFERROR(VLOOKUP(H1,A:B,2,FALSE),"")the problem with this formula is say if a name or state repeats, the payroll of the first instance of that name is pulled, instead of the payroll for the specific company that person works at. Here, this instance is "eric," which appears both under Company 1 and Company 2.
Is there any way to make this formula, or any formula, pull from the new instance of the name, or erase its "memory" of the first instance of the name or state to pull the new instance?
microsoft-excel worksheet-function microsoft-excel-2016
edited Jan 14 at 13:40
Ahmed Ashour
1,3401715
asked Jan 14 at 13:11
user983902user983902
12
add a comment |
In the example data, say we have Company 1 and Company 2. Under each company is the state in which employees work, followed by the name of the employee(s) which work(s) in that state. In the column next to this, we have the payroll, by Company, state and employee(s), each.
This data is pulled from two different sources, which provide different payroll data. I am trying to reconcile these differences, to see exactly where payroll differences are. I've been guided to use =IFERROR(VLOOKUP(H1,A:B,2,FALSE),"")the problem with this formula is say if a name or state repeats, the payroll of the first instance of that name is pulled, instead of the payroll for the specific company that person works at. Here, this instance is "eric," which appears both under Company 1 and Company 2.
Is there any way to make this formula, or any formula, pull from the new instance of the name, or erase its "memory" of the first instance of the name or state to pull the new instance?
microsoft-excel worksheet-function microsoft-excel-2016
edited Jan 14 at 13:40
Ahmed Ashour
1,3401715
asked Jan 14 at 13:11
user983902user983902
12
1
What have you tried? Searching around I found this page and this SO thread
– BruceWayne
Jan 14 at 15:01
Possible duplicate of Combining mismatched data with specific criteria
– jrichall
Jan 14 at 19:53
add a comment |
In the example data, say we have Company 1 and Company 2. Under each company is the state in which employees work, followed by the name of the employee(s) which work(s) in that state. In the column next to this, we have the payroll, by Company, state and employee(s), each.
This data is pulled from two different sources, which provide different payroll data. I am trying to reconcile these differences, to see exactly where payroll differences are. I've been guided to use =IFERROR(VLOOKUP(H1,A:B,2,FALSE),"")the problem with this formula is say if a name or state repeats, the payroll of the first instance of that name is pulled, instead of the payroll for the specific company that person works at. Here, this instance is "eric," which appears both under Company 1 and Company 2.
Is there any way to make this formula, or any formula, pull from the new instance of the name, or erase its "memory" of the first instance of the name or state to pull the new instance?
microsoft-excel worksheet-function microsoft-excel-2016
edited Jan 14 at 13:40
Ahmed Ashour
1,3401715
asked Jan 14 at 13:11
user983902user983902
12
In the example data, say we have Company 1 and Company 2. Under each company is the state in which employees work, followed by the name of the employee(s) which work(s) in that state. In the column next to this, we have the payroll, by Company, state and employee(s), each.
This data is pulled from two different sources, which provide different payroll data. I am trying to reconcile these differences, to see exactly where payroll differences are. I've been guided to use =IFERROR(VLOOKUP(H1,A:B,2,FALSE),"")the problem with this formula is say if a name or state repeats, the payroll of the first instance of that name is pulled, instead of the payroll for the specific company that person works at. Here, this instance is "eric," which appears both under Company 1 and Company 2.
Is there any way to make this formula, or any formula, pull from the new instance of the name, or erase its "memory" of the first instance of the name or state to pull the new instance?
microsoft-excel worksheet-function microsoft-excel-2016
microsoft-excel worksheet-function microsoft-excel-2016
edited Jan 14 at 13:40
Ahmed Ashour
1,3401715
asked Jan 14 at 13:11
user983902user983902
12
edited Jan 14 at 13:40
Ahmed Ashour
1,3401715
asked Jan 14 at 13:11
user983902user983902
12
edited Jan 14 at 13:40
Ahmed Ashour
1,3401715
edited Jan 14 at 13:40
Ahmed Ashour
1,3401715
edited Jan 14 at 13:40
Ahmed Ashour
1,3401715
1,3401715
asked Jan 14 at 13:11
user983902user983902
12
asked Jan 14 at 13:11
user983902user983902
12
asked Jan 14 at 13:11
user983902user983902
12
12
1
What have you tried? Searching around I found this page and this SO thread
– BruceWayne
Jan 14 at 15:01
Possible duplicate of Combining mismatched data with specific criteria
– jrichall
Jan 14 at 19:53
add a comment |
1
What have you tried? Searching around I found this page and this SO thread
– BruceWayne
Jan 14 at 15:01
Possible duplicate of Combining mismatched data with specific criteria
– jrichall
Jan 14 at 19:53
1
1
What have you tried? Searching around I found this page and this SO thread
– BruceWayne
Jan 14 at 15:01
What have you tried? Searching around I found this page and this SO thread
– BruceWayne
Jan 14 at 15:01
Possible duplicate of Combining mismatched data with specific criteria
– jrichall
Jan 14 at 19:53
Possible duplicate of Combining mismatched data with specific criteria
– jrichall
Jan 14 at 19:53
add a comment |
0
active
oldest
votes
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%2f1394119%2freconciling-differences-when-data-repeats-in-a-sheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f1394119%2freconciling-differences-when-data-repeats-in-a-sheet%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? Searching around I found this page and this SO thread
– BruceWayne
Jan 14 at 15:01
Possible duplicate of Combining mismatched data with specific criteria
– jrichall
Jan 14 at 19:53