Excel table (list) that fills in specific values if present
I have a specific task which I do every week for few years now and it takes a lot of time to do that manually. I hope something can be automated here.
Every week I gest a list like this (in random order):
The list contains let's call them 'names' (col A) and 'numbers' (col B) in random order, every time different. And here is a difficult part: I need to take numbers and place them into another pre-designed CALCULATIONS excel sheet (with all the calculations behind the scenes). Here is how it looks like (example):
I can't change order of 'names' in Calculation sheet. I just manually copy in numbers every week and there are hundreds of them. Is there any way I can improve this process to get this done quicker? Also - sometimes I get 'name' which is not on the list. I will need to add it later, but that's a different story and can't be done automatically because there are many things behind that).
At the end, I need to know which 'names' are not on the list so I can get them implemented. I guess there is a way of sorting my problem out using tables / lists in excel, but I ran out of ideas how to do that.
Thank you for your help.
microsoft-excel
asked Dec 17 '18 at 17:09
Piotr CiszewskiPiotr Ciszewski
185111
add a comment |
I have a specific task which I do every week for few years now and it takes a lot of time to do that manually. I hope something can be automated here.
Every week I gest a list like this (in random order):
The list contains let's call them 'names' (col A) and 'numbers' (col B) in random order, every time different. And here is a difficult part: I need to take numbers and place them into another pre-designed CALCULATIONS excel sheet (with all the calculations behind the scenes). Here is how it looks like (example):
I can't change order of 'names' in Calculation sheet. I just manually copy in numbers every week and there are hundreds of them. Is there any way I can improve this process to get this done quicker? Also - sometimes I get 'name' which is not on the list. I will need to add it later, but that's a different story and can't be done automatically because there are many things behind that).
At the end, I need to know which 'names' are not on the list so I can get them implemented. I guess there is a way of sorting my problem out using tables / lists in excel, but I ran out of ideas how to do that.
Thank you for your help.
microsoft-excel
asked Dec 17 '18 at 17:09
Piotr CiszewskiPiotr Ciszewski
185111
What have you tried?
– Eric F
Dec 17 '18 at 17:11
I am trying to play with VLOOKUP function. I guess it should be something like opposite to VLOOKUP to get this sorted
– Piotr Ciszewski
Dec 17 '18 at 17:18
add a comment |
I have a specific task which I do every week for few years now and it takes a lot of time to do that manually. I hope something can be automated here.
Every week I gest a list like this (in random order):
The list contains let's call them 'names' (col A) and 'numbers' (col B) in random order, every time different. And here is a difficult part: I need to take numbers and place them into another pre-designed CALCULATIONS excel sheet (with all the calculations behind the scenes). Here is how it looks like (example):
I can't change order of 'names' in Calculation sheet. I just manually copy in numbers every week and there are hundreds of them. Is there any way I can improve this process to get this done quicker? Also - sometimes I get 'name' which is not on the list. I will need to add it later, but that's a different story and can't be done automatically because there are many things behind that).
At the end, I need to know which 'names' are not on the list so I can get them implemented. I guess there is a way of sorting my problem out using tables / lists in excel, but I ran out of ideas how to do that.
Thank you for your help.
microsoft-excel
asked Dec 17 '18 at 17:09
Piotr CiszewskiPiotr Ciszewski
185111
I have a specific task which I do every week for few years now and it takes a lot of time to do that manually. I hope something can be automated here.
Every week I gest a list like this (in random order):
The list contains let's call them 'names' (col A) and 'numbers' (col B) in random order, every time different. And here is a difficult part: I need to take numbers and place them into another pre-designed CALCULATIONS excel sheet (with all the calculations behind the scenes). Here is how it looks like (example):
I can't change order of 'names' in Calculation sheet. I just manually copy in numbers every week and there are hundreds of them. Is there any way I can improve this process to get this done quicker? Also - sometimes I get 'name' which is not on the list. I will need to add it later, but that's a different story and can't be done automatically because there are many things behind that).
At the end, I need to know which 'names' are not on the list so I can get them implemented. I guess there is a way of sorting my problem out using tables / lists in excel, but I ran out of ideas how to do that.
Thank you for your help.
microsoft-excel
microsoft-excel
asked Dec 17 '18 at 17:09
Piotr CiszewskiPiotr Ciszewski
185111
asked Dec 17 '18 at 17:09
Piotr CiszewskiPiotr Ciszewski
185111
asked Dec 17 '18 at 17:09
Piotr CiszewskiPiotr Ciszewski
185111
asked Dec 17 '18 at 17:09
Piotr CiszewskiPiotr Ciszewski
185111
asked Dec 17 '18 at 17:09
Piotr CiszewskiPiotr Ciszewski
185111
185111
What have you tried?
– Eric F
Dec 17 '18 at 17:11
I am trying to play with VLOOKUP function. I guess it should be something like opposite to VLOOKUP to get this sorted
– Piotr Ciszewski
Dec 17 '18 at 17:18
add a comment |
What have you tried?
– Eric F
Dec 17 '18 at 17:11
I am trying to play with VLOOKUP function. I guess it should be something like opposite to VLOOKUP to get this sorted
– Piotr Ciszewski
Dec 17 '18 at 17:18
What have you tried?
– Eric F
Dec 17 '18 at 17:11
What have you tried?
– Eric F
Dec 17 '18 at 17:11
I am trying to play with VLOOKUP function. I guess it should be something like opposite to VLOOKUP to get this sorted
– Piotr Ciszewski
Dec 17 '18 at 17:18
I am trying to play with VLOOKUP function. I guess it should be something like opposite to VLOOKUP to get this sorted
– Piotr Ciszewski
Dec 17 '18 at 17:18
add a comment |
2 Answers
2
active
oldest
votes
You can use Index/Match. The below is on one sheet, but you should be able to use multiple sheets, just fix the references:
=INDEX($B$1:$B$5,MATCH(D1,$A$1:$A$5,0))
Enter, then drag over.
Then, wrap in IFERROR([formula],"") to hide the #N/A returns when there's no matching value found.
Edit: If you want a VLOOKUP(), then this would work too:
=VLOOKUP(D1,$A$1:$B$5,2,FALSE)
To highlight names not on the list, use Conditional Formatting and set the FILL to whatever you want:
Formula: =COUNTIF($D$1:$M$1,$A1)=0
Applies To: $A$1:$B$5
(Using A1:B5 will highlight both column A and B. I just did A1:A5 in that screenshot)
answered Dec 17 '18 at 17:21
BruceWayneBruceWayne
1,8301721
Thank you, I've just managed to get this working... almost. What is really important is highlighting on the fist sheet 'names' that are not on the CALCULATION (second) sheet, as I don't want to miss them. Is there a way to do that as well? So in this occasion - "Kim" is not on the list
– Piotr Ciszewski
Dec 17 '18 at 17:30
1
@PiotrCiszewski - See edit.
– BruceWayne
Dec 17 '18 at 17:38
Great! Thank you @BruceWayne
– Piotr Ciszewski
Dec 17 '18 at 17:40
( @PiotrCiszewski - If this works for you, you can mark it as The Answer by clicking the check mark left of the post)
– BruceWayne
Dec 17 '18 at 21:16
add a comment |
I've applied iferror + vlookup function.
=IFERROR(VLOOKUP(D1;$A$1:$B$30;2;0);0)
answered Dec 17 '18 at 17:36
Piotr CiszewskiPiotr Ciszewski
185111
(This should be a new question, or at least an Edit in your OP (not an Answer))
– BruceWayne
Dec 17 '18 at 17:38
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%2f1385309%2fexcel-table-list-that-fills-in-specific-values-if-present%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use Index/Match. The below is on one sheet, but you should be able to use multiple sheets, just fix the references:
=INDEX($B$1:$B$5,MATCH(D1,$A$1:$A$5,0))
Enter, then drag over.
Then, wrap in IFERROR([formula],"") to hide the #N/A returns when there's no matching value found.
Edit: If you want a VLOOKUP(), then this would work too:
=VLOOKUP(D1,$A$1:$B$5,2,FALSE)
To highlight names not on the list, use Conditional Formatting and set the FILL to whatever you want:
Formula: =COUNTIF($D$1:$M$1,$A1)=0
Applies To: $A$1:$B$5
(Using A1:B5 will highlight both column A and B. I just did A1:A5 in that screenshot)
answered Dec 17 '18 at 17:21
BruceWayneBruceWayne
1,8301721
Thank you, I've just managed to get this working... almost. What is really important is highlighting on the fist sheet 'names' that are not on the CALCULATION (second) sheet, as I don't want to miss them. Is there a way to do that as well? So in this occasion - "Kim" is not on the list
– Piotr Ciszewski
Dec 17 '18 at 17:30
1
@PiotrCiszewski - See edit.
– BruceWayne
Dec 17 '18 at 17:38
Great! Thank you @BruceWayne
– Piotr Ciszewski
Dec 17 '18 at 17:40
( @PiotrCiszewski - If this works for you, you can mark it as The Answer by clicking the check mark left of the post)
– BruceWayne
Dec 17 '18 at 21:16
add a comment |
You can use Index/Match. The below is on one sheet, but you should be able to use multiple sheets, just fix the references:
=INDEX($B$1:$B$5,MATCH(D1,$A$1:$A$5,0))
Enter, then drag over.
Then, wrap in IFERROR([formula],"") to hide the #N/A returns when there's no matching value found.
Edit: If you want a VLOOKUP(), then this would work too:
=VLOOKUP(D1,$A$1:$B$5,2,FALSE)
To highlight names not on the list, use Conditional Formatting and set the FILL to whatever you want:
Formula: =COUNTIF($D$1:$M$1,$A1)=0
Applies To: $A$1:$B$5
(Using A1:B5 will highlight both column A and B. I just did A1:A5 in that screenshot)
answered Dec 17 '18 at 17:21
BruceWayneBruceWayne
1,8301721
Thank you, I've just managed to get this working... almost. What is really important is highlighting on the fist sheet 'names' that are not on the CALCULATION (second) sheet, as I don't want to miss them. Is there a way to do that as well? So in this occasion - "Kim" is not on the list
– Piotr Ciszewski
Dec 17 '18 at 17:30
1
@PiotrCiszewski - See edit.
– BruceWayne
Dec 17 '18 at 17:38
Great! Thank you @BruceWayne
– Piotr Ciszewski
Dec 17 '18 at 17:40
( @PiotrCiszewski - If this works for you, you can mark it as The Answer by clicking the check mark left of the post)
– BruceWayne
Dec 17 '18 at 21:16
add a comment |
You can use Index/Match. The below is on one sheet, but you should be able to use multiple sheets, just fix the references:
=INDEX($B$1:$B$5,MATCH(D1,$A$1:$A$5,0))
Enter, then drag over.
Then, wrap in IFERROR([formula],"") to hide the #N/A returns when there's no matching value found.
Edit: If you want a VLOOKUP(), then this would work too:
=VLOOKUP(D1,$A$1:$B$5,2,FALSE)
To highlight names not on the list, use Conditional Formatting and set the FILL to whatever you want:
Formula: =COUNTIF($D$1:$M$1,$A1)=0
Applies To: $A$1:$B$5
(Using A1:B5 will highlight both column A and B. I just did A1:A5 in that screenshot)
answered Dec 17 '18 at 17:21
BruceWayneBruceWayne
1,8301721
You can use Index/Match. The below is on one sheet, but you should be able to use multiple sheets, just fix the references:
=INDEX($B$1:$B$5,MATCH(D1,$A$1:$A$5,0))
Enter, then drag over.
Then, wrap in IFERROR([formula],"") to hide the #N/A returns when there's no matching value found.
Edit: If you want a VLOOKUP(), then this would work too:
=VLOOKUP(D1,$A$1:$B$5,2,FALSE)
To highlight names not on the list, use Conditional Formatting and set the FILL to whatever you want:
Formula: =COUNTIF($D$1:$M$1,$A1)=0
Applies To: $A$1:$B$5
(Using A1:B5 will highlight both column A and B. I just did A1:A5 in that screenshot)
answered Dec 17 '18 at 17:21
BruceWayneBruceWayne
1,8301721
edited Dec 17 '18 at 17:38
answered Dec 17 '18 at 17:21
BruceWayneBruceWayne
1,8301721
answered Dec 17 '18 at 17:21
BruceWayneBruceWayne
1,8301721
answered Dec 17 '18 at 17:21
BruceWayneBruceWayne
1,8301721
1,8301721
Thank you, I've just managed to get this working... almost. What is really important is highlighting on the fist sheet 'names' that are not on the CALCULATION (second) sheet, as I don't want to miss them. Is there a way to do that as well? So in this occasion - "Kim" is not on the list
– Piotr Ciszewski
Dec 17 '18 at 17:30
1
@PiotrCiszewski - See edit.
– BruceWayne
Dec 17 '18 at 17:38
Great! Thank you @BruceWayne
– Piotr Ciszewski
Dec 17 '18 at 17:40
( @PiotrCiszewski - If this works for you, you can mark it as The Answer by clicking the check mark left of the post)
– BruceWayne
Dec 17 '18 at 21:16
add a comment |
Thank you, I've just managed to get this working... almost. What is really important is highlighting on the fist sheet 'names' that are not on the CALCULATION (second) sheet, as I don't want to miss them. Is there a way to do that as well? So in this occasion - "Kim" is not on the list
– Piotr Ciszewski
Dec 17 '18 at 17:30
1
@PiotrCiszewski - See edit.
– BruceWayne
Dec 17 '18 at 17:38
Great! Thank you @BruceWayne
– Piotr Ciszewski
Dec 17 '18 at 17:40
( @PiotrCiszewski - If this works for you, you can mark it as The Answer by clicking the check mark left of the post)
– BruceWayne
Dec 17 '18 at 21:16
Thank you, I've just managed to get this working... almost. What is really important is highlighting on the fist sheet 'names' that are not on the CALCULATION (second) sheet, as I don't want to miss them. Is there a way to do that as well? So in this occasion - "Kim" is not on the list
– Piotr Ciszewski
Dec 17 '18 at 17:30
Thank you, I've just managed to get this working... almost. What is really important is highlighting on the fist sheet 'names' that are not on the CALCULATION (second) sheet, as I don't want to miss them. Is there a way to do that as well? So in this occasion - "Kim" is not on the list
– Piotr Ciszewski
Dec 17 '18 at 17:30
1
1
@PiotrCiszewski - See edit.
– BruceWayne
Dec 17 '18 at 17:38
@PiotrCiszewski - See edit.
– BruceWayne
Dec 17 '18 at 17:38
Great! Thank you @BruceWayne
– Piotr Ciszewski
Dec 17 '18 at 17:40
Great! Thank you @BruceWayne
– Piotr Ciszewski
Dec 17 '18 at 17:40
( @PiotrCiszewski - If this works for you, you can mark it as The Answer by clicking the check mark left of the post)
– BruceWayne
Dec 17 '18 at 21:16
( @PiotrCiszewski - If this works for you, you can mark it as The Answer by clicking the check mark left of the post)
– BruceWayne
Dec 17 '18 at 21:16
add a comment |
I've applied iferror + vlookup function.
=IFERROR(VLOOKUP(D1;$A$1:$B$30;2;0);0)
answered Dec 17 '18 at 17:36
Piotr CiszewskiPiotr Ciszewski
185111
(This should be a new question, or at least an Edit in your OP (not an Answer))
– BruceWayne
Dec 17 '18 at 17:38
add a comment |
I've applied iferror + vlookup function.
=IFERROR(VLOOKUP(D1;$A$1:$B$30;2;0);0)
answered Dec 17 '18 at 17:36
Piotr CiszewskiPiotr Ciszewski
185111
(This should be a new question, or at least an Edit in your OP (not an Answer))
– BruceWayne
Dec 17 '18 at 17:38
add a comment |
I've applied iferror + vlookup function.
=IFERROR(VLOOKUP(D1;$A$1:$B$30;2;0);0)
answered Dec 17 '18 at 17:36
Piotr CiszewskiPiotr Ciszewski
185111
I've applied iferror + vlookup function.
=IFERROR(VLOOKUP(D1;$A$1:$B$30;2;0);0)
answered Dec 17 '18 at 17:36
Piotr CiszewskiPiotr Ciszewski
185111
edited Dec 17 '18 at 22:14
answered Dec 17 '18 at 17:36
Piotr CiszewskiPiotr Ciszewski
185111
answered Dec 17 '18 at 17:36
Piotr CiszewskiPiotr Ciszewski
185111
answered Dec 17 '18 at 17:36
Piotr CiszewskiPiotr Ciszewski
185111
185111
(This should be a new question, or at least an Edit in your OP (not an Answer))
– BruceWayne
Dec 17 '18 at 17:38
add a comment |
(This should be a new question, or at least an Edit in your OP (not an Answer))
– BruceWayne
Dec 17 '18 at 17:38
(This should be a new question, or at least an Edit in your OP (not an Answer))
– BruceWayne
Dec 17 '18 at 17:38
(This should be a new question, or at least an Edit in your OP (not an Answer))
– BruceWayne
Dec 17 '18 at 17:38
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%2f1385309%2fexcel-table-list-that-fills-in-specific-values-if-present%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
What have you tried?
– Eric F
Dec 17 '18 at 17:11
I am trying to play with VLOOKUP function. I guess it should be something like opposite to VLOOKUP to get this sorted
– Piotr Ciszewski
Dec 17 '18 at 17:18