How to count how often two different values appear with the same value in their neighbouring cell in EXCEL?
--- I HAVE REFORMULATED MY QUESTION TO MAKE IT EASIER TO UNDERSTAND. Please find the new post below! ---
I have a spreadsheet with recipe titles in column A and ingredients in column B.
[![input matrix[1]](https://i.stack.imgur.com/ZPdkl.png)
I want to create a Matrix with all ingredients as rows & columns that shows how many times 2 ingredients occur in the same recipe. Sadly my limited knowledge about VLOOKUP, COUNTIFS etc. doesn't help me here!
I would be very happy if you could help me how to build a formula (VBA or normal) for this problem! Thank you very much!
---- EDIT ---
To those who have already answered, thank you!
After having received feedback on how to improve my question here is a new approach:
In the picture, you see my input matrix on the left: Names of recipes and ingredients that are the recipes.
On the right, you see the output that I wish for: a grid of all ingredients with the count of how many times 2 ingredients appear in the same recipe.
Input & Output matrices
The command I am looking for should be something like: "For every time ingredient-A (e.g. Gin) appears in a recipe check which other ingredients are in this recipe (e.g. Tonic Water & Cucumber). Then add +1 in the Matrix where Gin and Tonic Water / Gin and Cucumber meet.
I hope this clarifies my question, but don't hesitate to tell me if it's still somehow incomprehensible. THANK YOU!
excel vba excel-vba
asked Nov 18 '18 at 17:10
djndjn
22
add a comment |
--- I HAVE REFORMULATED MY QUESTION TO MAKE IT EASIER TO UNDERSTAND. Please find the new post below! ---
I have a spreadsheet with recipe titles in column A and ingredients in column B.
[
I want to create a Matrix with all ingredients as rows & columns that shows how many times 2 ingredients occur in the same recipe. Sadly my limited knowledge about VLOOKUP, COUNTIFS etc. doesn't help me here!
I would be very happy if you could help me how to build a formula (VBA or normal) for this problem! Thank you very much!
---- EDIT ---
To those who have already answered, thank you!
After having received feedback on how to improve my question here is a new approach:
In the picture, you see my input matrix on the left: Names of recipes and ingredients that are the recipes.
On the right, you see the output that I wish for: a grid of all ingredients with the count of how many times 2 ingredients appear in the same recipe.
Input & Output matrices
The command I am looking for should be something like: "For every time ingredient-A (e.g. Gin) appears in a recipe check which other ingredients are in this recipe (e.g. Tonic Water & Cucumber). Then add +1 in the Matrix where Gin and Tonic Water / Gin and Cucumber meet.
I hope this clarifies my question, but don't hesitate to tell me if it's still somehow incomprehensible. THANK YOU!
excel vba excel-vba
asked Nov 18 '18 at 17:10
djndjn
22
4
I think you have to improve your post in two ways. First you need to clarify what the expected ouput is because as far as I understand your question then no ingredient is used twice in your example data per recipe. Second you have to show what you have tried so far as could you help me ist not a question, see here.
– Storax
Nov 18 '18 at 17:43
So your output should be for exampleVodka - steirerblut, Moscow muleorVodka - 2?
– Andreas
Nov 18 '18 at 20:49
Thank you for your feedback! This was my first question and I still try to learn how to ask effectively.
– djn
Nov 19 '18 at 9:20
Thank you @Andreas. I now edited my question in order to make it more comprehensible. Thank you for your patience I notice that it's harder to ask a good question than I thought :)
– djn
Nov 19 '18 at 14:31
add a comment |
--- I HAVE REFORMULATED MY QUESTION TO MAKE IT EASIER TO UNDERSTAND. Please find the new post below! ---
I have a spreadsheet with recipe titles in column A and ingredients in column B.
[
I want to create a Matrix with all ingredients as rows & columns that shows how many times 2 ingredients occur in the same recipe. Sadly my limited knowledge about VLOOKUP, COUNTIFS etc. doesn't help me here!
I would be very happy if you could help me how to build a formula (VBA or normal) for this problem! Thank you very much!
---- EDIT ---
To those who have already answered, thank you!
After having received feedback on how to improve my question here is a new approach:
In the picture, you see my input matrix on the left: Names of recipes and ingredients that are the recipes.
On the right, you see the output that I wish for: a grid of all ingredients with the count of how many times 2 ingredients appear in the same recipe.
Input & Output matrices
The command I am looking for should be something like: "For every time ingredient-A (e.g. Gin) appears in a recipe check which other ingredients are in this recipe (e.g. Tonic Water & Cucumber). Then add +1 in the Matrix where Gin and Tonic Water / Gin and Cucumber meet.
I hope this clarifies my question, but don't hesitate to tell me if it's still somehow incomprehensible. THANK YOU!
excel vba excel-vba
asked Nov 18 '18 at 17:10
djndjn
22
--- I HAVE REFORMULATED MY QUESTION TO MAKE IT EASIER TO UNDERSTAND. Please find the new post below! ---
I have a spreadsheet with recipe titles in column A and ingredients in column B.
[
I want to create a Matrix with all ingredients as rows & columns that shows how many times 2 ingredients occur in the same recipe. Sadly my limited knowledge about VLOOKUP, COUNTIFS etc. doesn't help me here!
I would be very happy if you could help me how to build a formula (VBA or normal) for this problem! Thank you very much!
---- EDIT ---
To those who have already answered, thank you!
After having received feedback on how to improve my question here is a new approach:
In the picture, you see my input matrix on the left: Names of recipes and ingredients that are the recipes.
On the right, you see the output that I wish for: a grid of all ingredients with the count of how many times 2 ingredients appear in the same recipe.
Input & Output matrices
The command I am looking for should be something like: "For every time ingredient-A (e.g. Gin) appears in a recipe check which other ingredients are in this recipe (e.g. Tonic Water & Cucumber). Then add +1 in the Matrix where Gin and Tonic Water / Gin and Cucumber meet.
I hope this clarifies my question, but don't hesitate to tell me if it's still somehow incomprehensible. THANK YOU!
excel vba excel-vba
excel vba excel-vba
asked Nov 18 '18 at 17:10
djndjn
22
asked Nov 18 '18 at 17:10
djndjn
22
edited Nov 19 '18 at 9:41
djn
asked Nov 18 '18 at 17:10
djndjn
22
asked Nov 18 '18 at 17:10
djndjn
22
asked Nov 18 '18 at 17:10
djndjn
22
22
4
I think you have to improve your post in two ways. First you need to clarify what the expected ouput is because as far as I understand your question then no ingredient is used twice in your example data per recipe. Second you have to show what you have tried so far as could you help me ist not a question, see here.
– Storax
Nov 18 '18 at 17:43
So your output should be for exampleVodka - steirerblut, Moscow muleorVodka - 2?
– Andreas
Nov 18 '18 at 20:49
Thank you for your feedback! This was my first question and I still try to learn how to ask effectively.
– djn
Nov 19 '18 at 9:20
Thank you @Andreas. I now edited my question in order to make it more comprehensible. Thank you for your patience I notice that it's harder to ask a good question than I thought :)
– djn
Nov 19 '18 at 14:31
add a comment |
4
I think you have to improve your post in two ways. First you need to clarify what the expected ouput is because as far as I understand your question then no ingredient is used twice in your example data per recipe. Second you have to show what you have tried so far as could you help me ist not a question, see here.
– Storax
Nov 18 '18 at 17:43
So your output should be for exampleVodka - steirerblut, Moscow muleorVodka - 2?
– Andreas
Nov 18 '18 at 20:49
Thank you for your feedback! This was my first question and I still try to learn how to ask effectively.
– djn
Nov 19 '18 at 9:20
Thank you @Andreas. I now edited my question in order to make it more comprehensible. Thank you for your patience I notice that it's harder to ask a good question than I thought :)
– djn
Nov 19 '18 at 14:31
4
4
I think you have to improve your post in two ways. First you need to clarify what the expected ouput is because as far as I understand your question then no ingredient is used twice in your example data per recipe. Second you have to show what you have tried so far as could you help me ist not a question, see here.
– Storax
Nov 18 '18 at 17:43
I think you have to improve your post in two ways. First you need to clarify what the expected ouput is because as far as I understand your question then no ingredient is used twice in your example data per recipe. Second you have to show what you have tried so far as could you help me ist not a question, see here.
– Storax
Nov 18 '18 at 17:43
So your output should be for example
Vodka - steirerblut, Moscow mule or Vodka - 2?– Andreas
Nov 18 '18 at 20:49
So your output should be for example
Vodka - steirerblut, Moscow mule or Vodka - 2?– Andreas
Nov 18 '18 at 20:49
Thank you for your feedback! This was my first question and I still try to learn how to ask effectively.
– djn
Nov 19 '18 at 9:20
Thank you for your feedback! This was my first question and I still try to learn how to ask effectively.
– djn
Nov 19 '18 at 9:20
Thank you @Andreas. I now edited my question in order to make it more comprehensible. Thank you for your patience I notice that it's harder to ask a good question than I thought :)
– djn
Nov 19 '18 at 14:31
Thank you @Andreas. I now edited my question in order to make it more comprehensible. Thank you for your patience I notice that it's harder to ask a good question than I thought :)
– djn
Nov 19 '18 at 14:31
add a comment |
1 Answer
1
active
oldest
votes
Your input does not actually show any examples of ingredients appearing twice within a recipe, but assuming you meant something like in my example below, you can simply use a pivot table:
answered Nov 18 '18 at 22:12
Michal RosaMichal Rosa
1,3191814
thank you for your answer. I think I have not formulated my question properly yesterday. I now edited my question. Is this comprehensible?
– djn
Nov 19 '18 at 10:15
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53363447%2fhow-to-count-how-often-two-different-values-appear-with-the-same-value-in-their%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
Your input does not actually show any examples of ingredients appearing twice within a recipe, but assuming you meant something like in my example below, you can simply use a pivot table:
answered Nov 18 '18 at 22:12
Michal RosaMichal Rosa
1,3191814
thank you for your answer. I think I have not formulated my question properly yesterday. I now edited my question. Is this comprehensible?
– djn
Nov 19 '18 at 10:15
add a comment |
Your input does not actually show any examples of ingredients appearing twice within a recipe, but assuming you meant something like in my example below, you can simply use a pivot table:
answered Nov 18 '18 at 22:12
Michal RosaMichal Rosa
1,3191814
thank you for your answer. I think I have not formulated my question properly yesterday. I now edited my question. Is this comprehensible?
– djn
Nov 19 '18 at 10:15
add a comment |
Your input does not actually show any examples of ingredients appearing twice within a recipe, but assuming you meant something like in my example below, you can simply use a pivot table:
answered Nov 18 '18 at 22:12
Michal RosaMichal Rosa
1,3191814
Your input does not actually show any examples of ingredients appearing twice within a recipe, but assuming you meant something like in my example below, you can simply use a pivot table:
answered Nov 18 '18 at 22:12
Michal RosaMichal Rosa
1,3191814
answered Nov 18 '18 at 22:12
Michal RosaMichal Rosa
1,3191814
answered Nov 18 '18 at 22:12
Michal RosaMichal Rosa
1,3191814
answered Nov 18 '18 at 22:12
Michal RosaMichal Rosa
1,3191814
1,3191814
thank you for your answer. I think I have not formulated my question properly yesterday. I now edited my question. Is this comprehensible?
– djn
Nov 19 '18 at 10:15
add a comment |
thank you for your answer. I think I have not formulated my question properly yesterday. I now edited my question. Is this comprehensible?
– djn
Nov 19 '18 at 10:15
thank you for your answer. I think I have not formulated my question properly yesterday. I now edited my question. Is this comprehensible?
– djn
Nov 19 '18 at 10:15
thank you for your answer. I think I have not formulated my question properly yesterday. I now edited my question. Is this comprehensible?
– djn
Nov 19 '18 at 10:15
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53363447%2fhow-to-count-how-often-two-different-values-appear-with-the-same-value-in-their%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
4
I think you have to improve your post in two ways. First you need to clarify what the expected ouput is because as far as I understand your question then no ingredient is used twice in your example data per recipe. Second you have to show what you have tried so far as could you help me ist not a question, see here.
– Storax
Nov 18 '18 at 17:43
So your output should be for example
Vodka - steirerblut, Moscow muleorVodka - 2?– Andreas
Nov 18 '18 at 20:49
Thank you for your feedback! This was my first question and I still try to learn how to ask effectively.
– djn
Nov 19 '18 at 9:20
Thank you @Andreas. I now edited my question in order to make it more comprehensible. Thank you for your patience I notice that it's harder to ask a good question than I thought :)
– djn
Nov 19 '18 at 14:31