Find all duplicates except the first one, then get values from a different column
A B
X1 16
X2 17
X2 18
X2 19
X3 21
I have two columns as above.
If there are duplicates in Column A (here the duplicate value is "X2"), then I need to insert in another sheet:
- value "18" - i.e. second value of duplicates
- value "19" - i.e. subsequent value(s)
How can I find duplicates in column A, and then for all duplicates except the first one from each group, get the values from a different column?
I have tried with many IF, FIND formulas, but nothing seems to work.
Any suggestions?
microsoft-excel microsoft-excel-2010 duplicate
edited Nov 23 '16 at 8:42
MJH
1,02741018
asked Nov 22 '16 at 11:16
user596129user596129
1415
add a comment |
A B
X1 16
X2 17
X2 18
X2 19
X3 21
I have two columns as above.
If there are duplicates in Column A (here the duplicate value is "X2"), then I need to insert in another sheet:
- value "18" - i.e. second value of duplicates
- value "19" - i.e. subsequent value(s)
How can I find duplicates in column A, and then for all duplicates except the first one from each group, get the values from a different column?
I have tried with many IF, FIND formulas, but nothing seems to work.
Any suggestions?
microsoft-excel microsoft-excel-2010 duplicate
edited Nov 23 '16 at 8:42
MJH
1,02741018
asked Nov 22 '16 at 11:16
user596129user596129
1415
add a comment |
A B
X1 16
X2 17
X2 18
X2 19
X3 21
I have two columns as above.
If there are duplicates in Column A (here the duplicate value is "X2"), then I need to insert in another sheet:
- value "18" - i.e. second value of duplicates
- value "19" - i.e. subsequent value(s)
How can I find duplicates in column A, and then for all duplicates except the first one from each group, get the values from a different column?
I have tried with many IF, FIND formulas, but nothing seems to work.
Any suggestions?
microsoft-excel microsoft-excel-2010 duplicate
edited Nov 23 '16 at 8:42
MJH
1,02741018
asked Nov 22 '16 at 11:16
user596129user596129
1415
A B
X1 16
X2 17
X2 18
X2 19
X3 21
I have two columns as above.
If there are duplicates in Column A (here the duplicate value is "X2"), then I need to insert in another sheet:
- value "18" - i.e. second value of duplicates
- value "19" - i.e. subsequent value(s)
How can I find duplicates in column A, and then for all duplicates except the first one from each group, get the values from a different column?
I have tried with many IF, FIND formulas, but nothing seems to work.
Any suggestions?
microsoft-excel microsoft-excel-2010 duplicate
microsoft-excel microsoft-excel-2010 duplicate
edited Nov 23 '16 at 8:42
MJH
1,02741018
asked Nov 22 '16 at 11:16
user596129user596129
1415
edited Nov 23 '16 at 8:42
MJH
1,02741018
asked Nov 22 '16 at 11:16
user596129user596129
1415
edited Nov 23 '16 at 8:42
MJH
1,02741018
edited Nov 23 '16 at 8:42
MJH
1,02741018
edited Nov 23 '16 at 8:42
MJH
1,02741018
1,02741018
asked Nov 22 '16 at 11:16
user596129user596129
1415
asked Nov 22 '16 at 11:16
user596129user596129
1415
asked Nov 22 '16 at 11:16
user596129user596129
1415
1415
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Let's say your values are in cells A2:B6. You can accomplish this by performing the following steps:
Paste the following formula into cell
C2(and also provide a column header, if desired). This formula will returnTRUEfor all duplicates that are not the first instance, and it will returnFALSEotherwise:=COUNTIF($A$2:$A2, A2)>1
Formula-copy cell
C2down through cellC6
Select cells
A1:C6, click the "Data" ribbon, then click the "Filter" icon
Click the filter dropdown that now exists in cell
C1, and de-select theFALSEcheckbox
Select the values in column
Bthat are still visible, and copy/paste to the new sheet
answered Nov 23 '16 at 6:32
MJHMJH
1,02741018
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%2f1148517%2ffind-all-duplicates-except-the-first-one-then-get-values-from-a-different-colum%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
Let's say your values are in cells A2:B6. You can accomplish this by performing the following steps:
Paste the following formula into cell
C2(and also provide a column header, if desired). This formula will returnTRUEfor all duplicates that are not the first instance, and it will returnFALSEotherwise:=COUNTIF($A$2:$A2, A2)>1
Formula-copy cell
C2down through cellC6
Select cells
A1:C6, click the "Data" ribbon, then click the "Filter" icon
Click the filter dropdown that now exists in cell
C1, and de-select theFALSEcheckbox
Select the values in column
Bthat are still visible, and copy/paste to the new sheet
answered Nov 23 '16 at 6:32
MJHMJH
1,02741018
add a comment |
Let's say your values are in cells A2:B6. You can accomplish this by performing the following steps:
Paste the following formula into cell
C2(and also provide a column header, if desired). This formula will returnTRUEfor all duplicates that are not the first instance, and it will returnFALSEotherwise:=COUNTIF($A$2:$A2, A2)>1
Formula-copy cell
C2down through cellC6
Select cells
A1:C6, click the "Data" ribbon, then click the "Filter" icon
Click the filter dropdown that now exists in cell
C1, and de-select theFALSEcheckbox
Select the values in column
Bthat are still visible, and copy/paste to the new sheet
answered Nov 23 '16 at 6:32
MJHMJH
1,02741018
add a comment |
Let's say your values are in cells A2:B6. You can accomplish this by performing the following steps:
Paste the following formula into cell
C2(and also provide a column header, if desired). This formula will returnTRUEfor all duplicates that are not the first instance, and it will returnFALSEotherwise:=COUNTIF($A$2:$A2, A2)>1
Formula-copy cell
C2down through cellC6
Select cells
A1:C6, click the "Data" ribbon, then click the "Filter" icon
Click the filter dropdown that now exists in cell
C1, and de-select theFALSEcheckbox
Select the values in column
Bthat are still visible, and copy/paste to the new sheet
answered Nov 23 '16 at 6:32
MJHMJH
1,02741018
Let's say your values are in cells A2:B6. You can accomplish this by performing the following steps:
Paste the following formula into cell
C2(and also provide a column header, if desired). This formula will returnTRUEfor all duplicates that are not the first instance, and it will returnFALSEotherwise:=COUNTIF($A$2:$A2, A2)>1
Formula-copy cell
C2down through cellC6
Select cells
A1:C6, click the "Data" ribbon, then click the "Filter" icon
Click the filter dropdown that now exists in cell
C1, and de-select theFALSEcheckbox
Select the values in column
Bthat are still visible, and copy/paste to the new sheet
answered Nov 23 '16 at 6:32
MJHMJH
1,02741018
answered Nov 23 '16 at 6:32
MJHMJH
1,02741018
answered Nov 23 '16 at 6:32
MJHMJH
1,02741018
answered Nov 23 '16 at 6:32
MJHMJH
1,02741018
1,02741018
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.
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%2f1148517%2ffind-all-duplicates-except-the-first-one-then-get-values-from-a-different-colum%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
