List in a cell the common values in N other cells with excel
0
So if I have the following data
column1 column2 column3
1,2,3 2,3,4 3,4,5
1 1,3,4 2
2 3,2 2,3
Is it possible with excel/spreadsheet to create a fourth column that will list all the common entries in column 1,2 and 3? Or possible to find values that are present in at least two (or N) columns? This is actually what I am looking for. Here is an expected output
column1 column2 column3 column4
1,2,3 2,3,4 3,4,5 2,3
1 1,3,4 2 1
2 3,2 2,3 2,3
I have seen multiple answers on how to do conditional highlighting to find duplicates in column but this is not really what I'm trying to achieve.
excel google-sheets spreadsheet
asked Dec 28 '18 at 14:42
LBesLBes
2,24111232
|
show 1 more comment
0
So if I have the following data
column1 column2 column3
1,2,3 2,3,4 3,4,5
1 1,3,4 2
2 3,2 2,3
Is it possible with excel/spreadsheet to create a fourth column that will list all the common entries in column 1,2 and 3? Or possible to find values that are present in at least two (or N) columns? This is actually what I am looking for. Here is an expected output
column1 column2 column3 column4
1,2,3 2,3,4 3,4,5 2,3
1 1,3,4 2 1
2 3,2 2,3 2,3
I have seen multiple answers on how to do conditional highlighting to find duplicates in column but this is not really what I'm trying to achieve.
excel google-sheets spreadsheet
asked Dec 28 '18 at 14:42
LBesLBes
2,24111232
Some of your single values are followed by commas on the first column and the other columns are not. Which is the correct format? Can you please take a screen shot of some sample data and manually populate the 4th column with the expected output. Lastly,Excel <> Google-Sheetsand are prone to different possible solutions. Which one are you using?
– urdearboy
Dec 28 '18 at 14:44
1
@urdearboy I have edited to provide expected output :). I'm basically interested in getting the values that appear in at least N column (in my test case N=2, but could be greater). I'd prefer google spreadsheets.
– LBes
Dec 28 '18 at 14:48
"...1,2 and 3?" Do you mean "or"? I don't see a2incolumn3...
– BruceWayne
Dec 28 '18 at 15:14
@BruceWayne the rest before the expected output states: "Or possible to find values that are present in at least two (or N) columns? This is actually what I am looking for. Here is an expected output". So I am looking for a way to find values that are in at least N columns, with for now N=2
– LBes
Dec 28 '18 at 15:16
@LBes - D'oh! Sorry, didn't catch that. (I'll delete this comment shortly)
– BruceWayne
Dec 28 '18 at 15:18
|
show 1 more comment
0
0
0
So if I have the following data
column1 column2 column3
1,2,3 2,3,4 3,4,5
1 1,3,4 2
2 3,2 2,3
Is it possible with excel/spreadsheet to create a fourth column that will list all the common entries in column 1,2 and 3? Or possible to find values that are present in at least two (or N) columns? This is actually what I am looking for. Here is an expected output
column1 column2 column3 column4
1,2,3 2,3,4 3,4,5 2,3
1 1,3,4 2 1
2 3,2 2,3 2,3
I have seen multiple answers on how to do conditional highlighting to find duplicates in column but this is not really what I'm trying to achieve.
excel google-sheets spreadsheet
asked Dec 28 '18 at 14:42
LBesLBes
2,24111232
So if I have the following data
column1 column2 column3
1,2,3 2,3,4 3,4,5
1 1,3,4 2
2 3,2 2,3
Is it possible with excel/spreadsheet to create a fourth column that will list all the common entries in column 1,2 and 3? Or possible to find values that are present in at least two (or N) columns? This is actually what I am looking for. Here is an expected output
column1 column2 column3 column4
1,2,3 2,3,4 3,4,5 2,3
1 1,3,4 2 1
2 3,2 2,3 2,3
I have seen multiple answers on how to do conditional highlighting to find duplicates in column but this is not really what I'm trying to achieve.
excel google-sheets spreadsheet
excel google-sheets spreadsheet
asked Dec 28 '18 at 14:42
LBesLBes
2,24111232
asked Dec 28 '18 at 14:42
LBesLBes
2,24111232
edited Dec 28 '18 at 14:48
LBes
asked Dec 28 '18 at 14:42
LBesLBes
2,24111232
asked Dec 28 '18 at 14:42
LBesLBes
2,24111232
asked Dec 28 '18 at 14:42
LBesLBes
2,24111232
2,24111232
Some of your single values are followed by commas on the first column and the other columns are not. Which is the correct format? Can you please take a screen shot of some sample data and manually populate the 4th column with the expected output. Lastly,Excel <> Google-Sheetsand are prone to different possible solutions. Which one are you using?
– urdearboy
Dec 28 '18 at 14:44
1
@urdearboy I have edited to provide expected output :). I'm basically interested in getting the values that appear in at least N column (in my test case N=2, but could be greater). I'd prefer google spreadsheets.
– LBes
Dec 28 '18 at 14:48
"...1,2 and 3?" Do you mean "or"? I don't see a2incolumn3...
– BruceWayne
Dec 28 '18 at 15:14
@BruceWayne the rest before the expected output states: "Or possible to find values that are present in at least two (or N) columns? This is actually what I am looking for. Here is an expected output". So I am looking for a way to find values that are in at least N columns, with for now N=2
– LBes
Dec 28 '18 at 15:16
@LBes - D'oh! Sorry, didn't catch that. (I'll delete this comment shortly)
– BruceWayne
Dec 28 '18 at 15:18
|
show 1 more comment
Some of your single values are followed by commas on the first column and the other columns are not. Which is the correct format? Can you please take a screen shot of some sample data and manually populate the 4th column with the expected output. Lastly,Excel <> Google-Sheetsand are prone to different possible solutions. Which one are you using?
– urdearboy
Dec 28 '18 at 14:44
1
@urdearboy I have edited to provide expected output :). I'm basically interested in getting the values that appear in at least N column (in my test case N=2, but could be greater). I'd prefer google spreadsheets.
– LBes
Dec 28 '18 at 14:48
"...1,2 and 3?" Do you mean "or"? I don't see a2incolumn3...
– BruceWayne
Dec 28 '18 at 15:14
@BruceWayne the rest before the expected output states: "Or possible to find values that are present in at least two (or N) columns? This is actually what I am looking for. Here is an expected output". So I am looking for a way to find values that are in at least N columns, with for now N=2
– LBes
Dec 28 '18 at 15:16
@LBes - D'oh! Sorry, didn't catch that. (I'll delete this comment shortly)
– BruceWayne
Dec 28 '18 at 15:18
Some of your single values are followed by commas on the first column and the other columns are not. Which is the correct format? Can you please take a screen shot of some sample data and manually populate the 4th column with the expected output. Lastly,
Excel <> Google-Sheets and are prone to different possible solutions. Which one are you using?– urdearboy
Dec 28 '18 at 14:44
Some of your single values are followed by commas on the first column and the other columns are not. Which is the correct format? Can you please take a screen shot of some sample data and manually populate the 4th column with the expected output. Lastly,
Excel <> Google-Sheets and are prone to different possible solutions. Which one are you using?– urdearboy
Dec 28 '18 at 14:44
1
1
@urdearboy I have edited to provide expected output :). I'm basically interested in getting the values that appear in at least N column (in my test case N=2, but could be greater). I'd prefer google spreadsheets.
– LBes
Dec 28 '18 at 14:48
@urdearboy I have edited to provide expected output :). I'm basically interested in getting the values that appear in at least N column (in my test case N=2, but could be greater). I'd prefer google spreadsheets.
– LBes
Dec 28 '18 at 14:48
"...1,2 and 3?" Do you mean "or"? I don't see a
2 in column3...– BruceWayne
Dec 28 '18 at 15:14
"...1,2 and 3?" Do you mean "or"? I don't see a
2 in column3...– BruceWayne
Dec 28 '18 at 15:14
@BruceWayne the rest before the expected output states: "Or possible to find values that are present in at least two (or N) columns? This is actually what I am looking for. Here is an expected output". So I am looking for a way to find values that are in at least N columns, with for now N=2
– LBes
Dec 28 '18 at 15:16
@BruceWayne the rest before the expected output states: "Or possible to find values that are present in at least two (or N) columns? This is actually what I am looking for. Here is an expected output". So I am looking for a way to find values that are in at least N columns, with for now N=2
– LBes
Dec 28 '18 at 15:16
@LBes - D'oh! Sorry, didn't catch that. (I'll delete this comment shortly)
– BruceWayne
Dec 28 '18 at 15:18
@LBes - D'oh! Sorry, didn't catch that. (I'll delete this comment shortly)
– BruceWayne
Dec 28 '18 at 15:18
|
show 1 more comment
1 Answer
1
active
oldest
votes
1
Assuming your first row with data is row 1 (A1:C1) try
=join(", ", query(query(ArrayFormula(transpose(split(join(", ", A1:C1), ", "))&{"",""}), "Select Col1, Count(Col2) where Col1 is not null group by Col1"), "Select Col1 where Col2 > 1"))
or, if your locale requires the use of semicolons...
=join(", "; query(query(ArrayFormula(transpose(split(join(", "; A1:C1); ", "))&{""""}); "Select Col1, Count(Col2) where Col1 is not null group by Col1"); "Select Col1 where Col2 > 1"))
and fill down as far as needed.
(Change range if needed).
EXPLANATION
- Create one row with all the values of A1:C1 in their own cell. To do that, join() all cells and then split use the comma as delimiter.
- Transpose to turn this row into a column.
- Create an identical column (with {"",""} )
- Use a first query to create a table of the values and their respective count.
- Use a second query to filter out the values with a count < 2.
- Use Join() to get the results in one cell (separated with a comma).
answered Dec 28 '18 at 15:41
JPVJPV
10.9k21525
This seems complicated. Care to explain a bit? Also Spreadsheet returns an error when I tried just that
– LBes
Dec 28 '18 at 16:18
1
Don't know what your locale is, but try the second option in the updated answer.
– JPV
Dec 28 '18 at 16:28
that was indeed the problem. Thanks a lot
– LBes
Dec 28 '18 at 16:34
If your column are not adjacents, is that easy to modify? I know where I have to change it, but I just don't how to write instead of "A1:C1" "A1 and C1 and E1"
– LBes
Dec 28 '18 at 16:37
1
{A1C1E1}. I also added a brief explanation.
– JPV
Dec 28 '18 at 16:41
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
});
}
});
draft saved
draft discarded
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%2f53960250%2flist-in-a-cell-the-common-values-in-n-other-cells-with-excel%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
1
Assuming your first row with data is row 1 (A1:C1) try
=join(", ", query(query(ArrayFormula(transpose(split(join(", ", A1:C1), ", "))&{"",""}), "Select Col1, Count(Col2) where Col1 is not null group by Col1"), "Select Col1 where Col2 > 1"))
or, if your locale requires the use of semicolons...
=join(", "; query(query(ArrayFormula(transpose(split(join(", "; A1:C1); ", "))&{""""}); "Select Col1, Count(Col2) where Col1 is not null group by Col1"); "Select Col1 where Col2 > 1"))
and fill down as far as needed.
(Change range if needed).
EXPLANATION
- Create one row with all the values of A1:C1 in their own cell. To do that, join() all cells and then split use the comma as delimiter.
- Transpose to turn this row into a column.
- Create an identical column (with {"",""} )
- Use a first query to create a table of the values and their respective count.
- Use a second query to filter out the values with a count < 2.
- Use Join() to get the results in one cell (separated with a comma).
answered Dec 28 '18 at 15:41
JPVJPV
10.9k21525
This seems complicated. Care to explain a bit? Also Spreadsheet returns an error when I tried just that
– LBes
Dec 28 '18 at 16:18
1
Don't know what your locale is, but try the second option in the updated answer.
– JPV
Dec 28 '18 at 16:28
that was indeed the problem. Thanks a lot
– LBes
Dec 28 '18 at 16:34
If your column are not adjacents, is that easy to modify? I know where I have to change it, but I just don't how to write instead of "A1:C1" "A1 and C1 and E1"
– LBes
Dec 28 '18 at 16:37
1
{A1C1E1}. I also added a brief explanation.
– JPV
Dec 28 '18 at 16:41
add a comment |
1
Assuming your first row with data is row 1 (A1:C1) try
=join(", ", query(query(ArrayFormula(transpose(split(join(", ", A1:C1), ", "))&{"",""}), "Select Col1, Count(Col2) where Col1 is not null group by Col1"), "Select Col1 where Col2 > 1"))
or, if your locale requires the use of semicolons...
=join(", "; query(query(ArrayFormula(transpose(split(join(", "; A1:C1); ", "))&{""""}); "Select Col1, Count(Col2) where Col1 is not null group by Col1"); "Select Col1 where Col2 > 1"))
and fill down as far as needed.
(Change range if needed).
EXPLANATION
- Create one row with all the values of A1:C1 in their own cell. To do that, join() all cells and then split use the comma as delimiter.
- Transpose to turn this row into a column.
- Create an identical column (with {"",""} )
- Use a first query to create a table of the values and their respective count.
- Use a second query to filter out the values with a count < 2.
- Use Join() to get the results in one cell (separated with a comma).
answered Dec 28 '18 at 15:41
JPVJPV
10.9k21525
This seems complicated. Care to explain a bit? Also Spreadsheet returns an error when I tried just that
– LBes
Dec 28 '18 at 16:18
1
Don't know what your locale is, but try the second option in the updated answer.
– JPV
Dec 28 '18 at 16:28
that was indeed the problem. Thanks a lot
– LBes
Dec 28 '18 at 16:34
If your column are not adjacents, is that easy to modify? I know where I have to change it, but I just don't how to write instead of "A1:C1" "A1 and C1 and E1"
– LBes
Dec 28 '18 at 16:37
1
{A1C1E1}. I also added a brief explanation.
– JPV
Dec 28 '18 at 16:41
add a comment |
1
1
1
Assuming your first row with data is row 1 (A1:C1) try
=join(", ", query(query(ArrayFormula(transpose(split(join(", ", A1:C1), ", "))&{"",""}), "Select Col1, Count(Col2) where Col1 is not null group by Col1"), "Select Col1 where Col2 > 1"))
or, if your locale requires the use of semicolons...
=join(", "; query(query(ArrayFormula(transpose(split(join(", "; A1:C1); ", "))&{""""}); "Select Col1, Count(Col2) where Col1 is not null group by Col1"); "Select Col1 where Col2 > 1"))
and fill down as far as needed.
(Change range if needed).
EXPLANATION
- Create one row with all the values of A1:C1 in their own cell. To do that, join() all cells and then split use the comma as delimiter.
- Transpose to turn this row into a column.
- Create an identical column (with {"",""} )
- Use a first query to create a table of the values and their respective count.
- Use a second query to filter out the values with a count < 2.
- Use Join() to get the results in one cell (separated with a comma).
answered Dec 28 '18 at 15:41
JPVJPV
10.9k21525
Assuming your first row with data is row 1 (A1:C1) try
=join(", ", query(query(ArrayFormula(transpose(split(join(", ", A1:C1), ", "))&{"",""}), "Select Col1, Count(Col2) where Col1 is not null group by Col1"), "Select Col1 where Col2 > 1"))
or, if your locale requires the use of semicolons...
=join(", "; query(query(ArrayFormula(transpose(split(join(", "; A1:C1); ", "))&{""""}); "Select Col1, Count(Col2) where Col1 is not null group by Col1"); "Select Col1 where Col2 > 1"))
and fill down as far as needed.
(Change range if needed).
EXPLANATION
- Create one row with all the values of A1:C1 in their own cell. To do that, join() all cells and then split use the comma as delimiter.
- Transpose to turn this row into a column.
- Create an identical column (with {"",""} )
- Use a first query to create a table of the values and their respective count.
- Use a second query to filter out the values with a count < 2.
- Use Join() to get the results in one cell (separated with a comma).
answered Dec 28 '18 at 15:41
JPVJPV
10.9k21525
edited Dec 28 '18 at 16:40
answered Dec 28 '18 at 15:41
JPVJPV
10.9k21525
answered Dec 28 '18 at 15:41
JPVJPV
10.9k21525
answered Dec 28 '18 at 15:41
JPVJPV
10.9k21525
10.9k21525
This seems complicated. Care to explain a bit? Also Spreadsheet returns an error when I tried just that
– LBes
Dec 28 '18 at 16:18
1
Don't know what your locale is, but try the second option in the updated answer.
– JPV
Dec 28 '18 at 16:28
that was indeed the problem. Thanks a lot
– LBes
Dec 28 '18 at 16:34
If your column are not adjacents, is that easy to modify? I know where I have to change it, but I just don't how to write instead of "A1:C1" "A1 and C1 and E1"
– LBes
Dec 28 '18 at 16:37
1
{A1C1E1}. I also added a brief explanation.
– JPV
Dec 28 '18 at 16:41
add a comment |
This seems complicated. Care to explain a bit? Also Spreadsheet returns an error when I tried just that
– LBes
Dec 28 '18 at 16:18
1
Don't know what your locale is, but try the second option in the updated answer.
– JPV
Dec 28 '18 at 16:28
that was indeed the problem. Thanks a lot
– LBes
Dec 28 '18 at 16:34
If your column are not adjacents, is that easy to modify? I know where I have to change it, but I just don't how to write instead of "A1:C1" "A1 and C1 and E1"
– LBes
Dec 28 '18 at 16:37
1
{A1C1E1}. I also added a brief explanation.
– JPV
Dec 28 '18 at 16:41
This seems complicated. Care to explain a bit? Also Spreadsheet returns an error when I tried just that
– LBes
Dec 28 '18 at 16:18
This seems complicated. Care to explain a bit? Also Spreadsheet returns an error when I tried just that
– LBes
Dec 28 '18 at 16:18
1
1
Don't know what your locale is, but try the second option in the updated answer.
– JPV
Dec 28 '18 at 16:28
Don't know what your locale is, but try the second option in the updated answer.
– JPV
Dec 28 '18 at 16:28
that was indeed the problem. Thanks a lot
– LBes
Dec 28 '18 at 16:34
that was indeed the problem. Thanks a lot
– LBes
Dec 28 '18 at 16:34
If your column are not adjacents, is that easy to modify? I know where I have to change it, but I just don't how to write instead of "A1:C1" "A1 and C1 and E1"
– LBes
Dec 28 '18 at 16:37
If your column are not adjacents, is that easy to modify? I know where I have to change it, but I just don't how to write instead of "A1:C1" "A1 and C1 and E1"
– LBes
Dec 28 '18 at 16:37
1
1
{A1C1E1}. I also added a brief explanation.
– JPV
Dec 28 '18 at 16:41
{A1C1E1}. I also added a brief explanation.
– JPV
Dec 28 '18 at 16:41
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53960250%2flist-in-a-cell-the-common-values-in-n-other-cells-with-excel%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
Some of your single values are followed by commas on the first column and the other columns are not. Which is the correct format? Can you please take a screen shot of some sample data and manually populate the 4th column with the expected output. Lastly,
Excel <> Google-Sheetsand are prone to different possible solutions. Which one are you using?– urdearboy
Dec 28 '18 at 14:44
1
@urdearboy I have edited to provide expected output :). I'm basically interested in getting the values that appear in at least N column (in my test case N=2, but could be greater). I'd prefer google spreadsheets.
– LBes
Dec 28 '18 at 14:48
"...1,2 and 3?" Do you mean "or"? I don't see a
2incolumn3...– BruceWayne
Dec 28 '18 at 15:14
@BruceWayne the rest before the expected output states: "Or possible to find values that are present in at least two (or N) columns? This is actually what I am looking for. Here is an expected output". So I am looking for a way to find values that are in at least N columns, with for now N=2
– LBes
Dec 28 '18 at 15:16
@LBes - D'oh! Sorry, didn't catch that. (I'll delete this comment shortly)
– BruceWayne
Dec 28 '18 at 15:18