Having trouble comparing multiple columns and rows in different worksheets in excel
I have limited knowledge of formulas in excel and would really appreciate help with this question:
I have two worksheets: Worksheet1 and Worksheet2. Worksheet A1 shows a list of all the samples that are in a lab, and Worksheet2 shows a list of only the samples in the lab that have been analyzed and have results. Column A in both worksheets is the lab #, but as just mentioned column A in Worksheet1 contains all the lab numbers of the samples in the lab, and column A in Worksheet2 contains the lab numbers of only the analyzed samples. Some samples have 2 analysis methods so they appear twice in column A in Worksheet2. Three types of analysis were performed: U/Pb, At/At, and K/Ar; these correspond to columns B, C, D in Worksheet1 respectively. Column B in Worksheet2 names the type of analysis performed. I need to fill out columns B, C and D in Worksheet1 the following way:
First of all, I need to compare column A in Worksheet1 to column A in Worksheet2; if the lab number value in A in Worksheet1 appears in A in Worksheet2, then look at column B in Worksheet2 to check what kind of analysis was done. If analysis U/Pb was done, fill the respective cell in Worksheet1 with YES or "(blank)" for that analysis type; i.e. if analysis U/Pb and K/Ar was done for sample 427, then YES should appear in column B and D, columns C should be blank. If analysis K/Ar was done for sample 482 then YES should appear in columns D and B and C should be blank.
I've included a screenshot of the worksheets:
microsoft-excel microsoft-excel-2010 conditional-formatting
edited Dec 10 '18 at 20:01
cybernetic.nomad
1,236112
asked Dec 10 '18 at 17:17
Pamela IM
1
add a comment |
I have limited knowledge of formulas in excel and would really appreciate help with this question:
I have two worksheets: Worksheet1 and Worksheet2. Worksheet A1 shows a list of all the samples that are in a lab, and Worksheet2 shows a list of only the samples in the lab that have been analyzed and have results. Column A in both worksheets is the lab #, but as just mentioned column A in Worksheet1 contains all the lab numbers of the samples in the lab, and column A in Worksheet2 contains the lab numbers of only the analyzed samples. Some samples have 2 analysis methods so they appear twice in column A in Worksheet2. Three types of analysis were performed: U/Pb, At/At, and K/Ar; these correspond to columns B, C, D in Worksheet1 respectively. Column B in Worksheet2 names the type of analysis performed. I need to fill out columns B, C and D in Worksheet1 the following way:
First of all, I need to compare column A in Worksheet1 to column A in Worksheet2; if the lab number value in A in Worksheet1 appears in A in Worksheet2, then look at column B in Worksheet2 to check what kind of analysis was done. If analysis U/Pb was done, fill the respective cell in Worksheet1 with YES or "(blank)" for that analysis type; i.e. if analysis U/Pb and K/Ar was done for sample 427, then YES should appear in column B and D, columns C should be blank. If analysis K/Ar was done for sample 482 then YES should appear in columns D and B and C should be blank.
I've included a screenshot of the worksheets:
microsoft-excel microsoft-excel-2010 conditional-formatting
edited Dec 10 '18 at 20:01
cybernetic.nomad
1,236112
asked Dec 10 '18 at 17:17
Pamela IM
1
add a comment |
I have limited knowledge of formulas in excel and would really appreciate help with this question:
I have two worksheets: Worksheet1 and Worksheet2. Worksheet A1 shows a list of all the samples that are in a lab, and Worksheet2 shows a list of only the samples in the lab that have been analyzed and have results. Column A in both worksheets is the lab #, but as just mentioned column A in Worksheet1 contains all the lab numbers of the samples in the lab, and column A in Worksheet2 contains the lab numbers of only the analyzed samples. Some samples have 2 analysis methods so they appear twice in column A in Worksheet2. Three types of analysis were performed: U/Pb, At/At, and K/Ar; these correspond to columns B, C, D in Worksheet1 respectively. Column B in Worksheet2 names the type of analysis performed. I need to fill out columns B, C and D in Worksheet1 the following way:
First of all, I need to compare column A in Worksheet1 to column A in Worksheet2; if the lab number value in A in Worksheet1 appears in A in Worksheet2, then look at column B in Worksheet2 to check what kind of analysis was done. If analysis U/Pb was done, fill the respective cell in Worksheet1 with YES or "(blank)" for that analysis type; i.e. if analysis U/Pb and K/Ar was done for sample 427, then YES should appear in column B and D, columns C should be blank. If analysis K/Ar was done for sample 482 then YES should appear in columns D and B and C should be blank.
I've included a screenshot of the worksheets:
microsoft-excel microsoft-excel-2010 conditional-formatting
edited Dec 10 '18 at 20:01
cybernetic.nomad
1,236112
asked Dec 10 '18 at 17:17
Pamela IM
1
I have limited knowledge of formulas in excel and would really appreciate help with this question:
I have two worksheets: Worksheet1 and Worksheet2. Worksheet A1 shows a list of all the samples that are in a lab, and Worksheet2 shows a list of only the samples in the lab that have been analyzed and have results. Column A in both worksheets is the lab #, but as just mentioned column A in Worksheet1 contains all the lab numbers of the samples in the lab, and column A in Worksheet2 contains the lab numbers of only the analyzed samples. Some samples have 2 analysis methods so they appear twice in column A in Worksheet2. Three types of analysis were performed: U/Pb, At/At, and K/Ar; these correspond to columns B, C, D in Worksheet1 respectively. Column B in Worksheet2 names the type of analysis performed. I need to fill out columns B, C and D in Worksheet1 the following way:
First of all, I need to compare column A in Worksheet1 to column A in Worksheet2; if the lab number value in A in Worksheet1 appears in A in Worksheet2, then look at column B in Worksheet2 to check what kind of analysis was done. If analysis U/Pb was done, fill the respective cell in Worksheet1 with YES or "(blank)" for that analysis type; i.e. if analysis U/Pb and K/Ar was done for sample 427, then YES should appear in column B and D, columns C should be blank. If analysis K/Ar was done for sample 482 then YES should appear in columns D and B and C should be blank.
I've included a screenshot of the worksheets:
microsoft-excel microsoft-excel-2010 conditional-formatting
microsoft-excel microsoft-excel-2010 conditional-formatting
edited Dec 10 '18 at 20:01
cybernetic.nomad
1,236112
asked Dec 10 '18 at 17:17
Pamela IM
1
edited Dec 10 '18 at 20:01
cybernetic.nomad
1,236112
asked Dec 10 '18 at 17:17
Pamela IM
1
edited Dec 10 '18 at 20:01
cybernetic.nomad
1,236112
edited Dec 10 '18 at 20:01
cybernetic.nomad
1,236112
edited Dec 10 '18 at 20:01
cybernetic.nomad
1,236112
1,236112
asked Dec 10 '18 at 17:17
Pamela IM
1
asked Dec 10 '18 at 17:17
Pamela IM
1
asked Dec 10 '18 at 17:17
Pamela IM
1
1
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Fill in sheet A1 cell B2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="U/Pb","FALSE"),"YES","")
Cell C2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="Ar/Ar","FALSE"),"YES","")
Cell D2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="K/Ar","FALSE"),"YES","")
Copy formulas down your sheet. I guessed at Ar/Ar (At/At?) following the same pattern as the other two. Please mark as answer if this does the trick for you.
answered Dec 10 '18 at 18:07
Brian
643
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%2f1382383%2fhaving-trouble-comparing-multiple-columns-and-rows-in-different-worksheets-in-ex%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
Fill in sheet A1 cell B2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="U/Pb","FALSE"),"YES","")
Cell C2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="Ar/Ar","FALSE"),"YES","")
Cell D2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="K/Ar","FALSE"),"YES","")
Copy formulas down your sheet. I guessed at Ar/Ar (At/At?) following the same pattern as the other two. Please mark as answer if this does the trick for you.
answered Dec 10 '18 at 18:07
Brian
643
add a comment |
Fill in sheet A1 cell B2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="U/Pb","FALSE"),"YES","")
Cell C2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="Ar/Ar","FALSE"),"YES","")
Cell D2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="K/Ar","FALSE"),"YES","")
Copy formulas down your sheet. I guessed at Ar/Ar (At/At?) following the same pattern as the other two. Please mark as answer if this does the trick for you.
answered Dec 10 '18 at 18:07
Brian
643
add a comment |
Fill in sheet A1 cell B2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="U/Pb","FALSE"),"YES","")
Cell C2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="Ar/Ar","FALSE"),"YES","")
Cell D2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="K/Ar","FALSE"),"YES","")
Copy formulas down your sheet. I guessed at Ar/Ar (At/At?) following the same pattern as the other two. Please mark as answer if this does the trick for you.
answered Dec 10 '18 at 18:07
Brian
643
Fill in sheet A1 cell B2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="U/Pb","FALSE"),"YES","")
Cell C2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="Ar/Ar","FALSE"),"YES","")
Cell D2 with:
=IF(IFERROR(VLOOKUP($A2,'A2'!$A:$B,2,FALSE)="K/Ar","FALSE"),"YES","")
Copy formulas down your sheet. I guessed at Ar/Ar (At/At?) following the same pattern as the other two. Please mark as answer if this does the trick for you.
answered Dec 10 '18 at 18:07
Brian
643
edited Dec 10 '18 at 18:27
answered Dec 10 '18 at 18:07
Brian
643
answered Dec 10 '18 at 18:07
Brian
643
answered Dec 10 '18 at 18:07
Brian
643
643
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f1382383%2fhaving-trouble-comparing-multiple-columns-and-rows-in-different-worksheets-in-ex%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
