Tjyylli

Return to UK after being refused entry years previously

How to manage monthly salary

Can a rogue use sneak attack with weapons that have the thrown property even if they are not thrown?

Multiply Two Integer Polynomials

Did Section 31 appear in Star Trek: The Next Generation?

How to type this arrow in math mode?

Can you compress metal and what would be the consequences?

Do these rules for Critical Successes and Critical Failures seem Fair?

"as much details as you can remember"

What tool would a Roman-age civilization have for the breaking of silver and other metals into dust?

What are the motivations for publishing new editions of an existing textbook, beyond new discoveries in a field?

How to support a colleague who finds meetings extremely tiring?

Origin of "cooter" meaning "vagina"

Resizing object distorts it (Illustrator CC 2018)

Are spiders unable to hurt humans, especially very small spiders?

What to do when moving next to a bird sanctuary with a loosely-domesticated cat?

Why hard-Brexiteers don't insist on a hard border to prevent illegal immigration after Brexit?

Can we generate random numbers using irrational numbers like π and e?

Why did Acorn's A3000 have red function keys?

Is a "Democratic" Oligarchy-Style System Possible?

How to answer pointed "are you quitting" questioning when I don't want them to suspect

For what reasons would an animal species NOT cross a *horizontal* land bridge?

Right tool to dig six foot holes?

Time travel alters history but people keep saying nothing's changed

Find Duplicates Outside of Selection/Highlight in the Same Column - Excel

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

1

I want to Sort a column,
then select a section of that column,
and then Un-sort the column
and let Excel find me all duplicates throughout that column that corresponds to any of the cells I selected before and preferably select them.

In short, I need Excel to find me any duplicates of any cell inside my selection, but OUTSIDE of my selection in the same column.

Example:

(34 SELECTED)    

(73 SELECTED)    

23    

86    

234    

21    

73    

853    

34    

11    

OUTCOME:

(34 SELECTED)    

(73 SELECTED)    

23    

86    

234    

21    

(73 SELECTED)    

853    

(34 SELECTED)    

11

Can someone help me?

microsoft-excel

share|improve this question

edited yesterday

Akina

1,38329

asked yesterday

JamesJames

62

New contributor

James is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  In your Outcome 34 & 73 appears twice since you are looking for Duplicates, also other values are unique,, I think you need to edit the post to clear your need !!
  
  – Rajesh S
  yesterday
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hey Rajesh, thanks for getting back to me. Yes, this is exactly what I'm looking for; I want to find duplicate cells of the cells I have selected. In the example, I have selected 34 an 73, so I want to select ALL 34 and 73's in the whole column. I hope this clarifies it.
  
  – James
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What you are looking for is possible using Auto/Advance Filter also, VBA (Macro) can be used also ,, confirm can you handle VBA ?
  
  – Rajesh S
  yesterday
  
  
  

  
  
  
  

add a comment | 

1

I want to Sort a column,
then select a section of that column,
and then Un-sort the column
and let Excel find me all duplicates throughout that column that corresponds to any of the cells I selected before and preferably select them.

In short, I need Excel to find me any duplicates of any cell inside my selection, but OUTSIDE of my selection in the same column.

Example:

(34 SELECTED)    

(73 SELECTED)    

23    

86    

234    

21    

73    

853    

34    

11    

OUTCOME:

(34 SELECTED)    

(73 SELECTED)    

23    

86    

234    

21    

(73 SELECTED)    

853    

(34 SELECTED)    

11

Can someone help me?

microsoft-excel

share|improve this question

edited yesterday

Akina

1,38329

asked yesterday

JamesJames

62

New contributor

James is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  In your Outcome 34 & 73 appears twice since you are looking for Duplicates, also other values are unique,, I think you need to edit the post to clear your need !!
  
  – Rajesh S
  yesterday
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hey Rajesh, thanks for getting back to me. Yes, this is exactly what I'm looking for; I want to find duplicate cells of the cells I have selected. In the example, I have selected 34 an 73, so I want to select ALL 34 and 73's in the whole column. I hope this clarifies it.
  
  – James
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What you are looking for is possible using Auto/Advance Filter also, VBA (Macro) can be used also ,, confirm can you handle VBA ?
  
  – Rajesh S
  yesterday
  
  
  

  
  
  
  

add a comment | 

I want to Sort a column,
then select a section of that column,
and then Un-sort the column
and let Excel find me all duplicates throughout that column that corresponds to any of the cells I selected before and preferably select them.

In short, I need Excel to find me any duplicates of any cell inside my selection, but OUTSIDE of my selection in the same column.

Example:

(34 SELECTED)    

(73 SELECTED)    

23    

86    

234    

21    

73    

853    

34    

11    

OUTCOME:

(34 SELECTED)    

(73 SELECTED)    

23    

86    

234    

21    

(73 SELECTED)    

853    

(34 SELECTED)    

11

Can someone help me?

microsoft-excel

share|improve this question

edited yesterday

Akina

1,38329

asked yesterday

JamesJames

62

New contributor

James is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

(34 SELECTED)    

(73 SELECTED)    

23    

86    

234    

21    

73    

853    

34    

11    

(34 SELECTED)    

(73 SELECTED)    

23    

86    

234    

21    

(73 SELECTED)    

853    

(34 SELECTED)    

11

share|improve this question

edited yesterday

Akina

1,38329

asked yesterday

JamesJames

62

New contributor

James is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited yesterday

Akina

1,38329

asked yesterday

JamesJames

62

New contributor

James is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited yesterday

Akina

1,38329

edited yesterday

Akina

1,38329

asked yesterday

JamesJames

62

New contributor

James is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked yesterday

JamesJames

62

2 Answers
2

active

oldest

votes

1

Sub SelectBySelection()

Dim tempArray()

Dim oneCell As Range

Dim i As Integer

Dim tempStr As String

Dim tempSelection As String



If Selection.Areas.Count > 1 Then Exit Sub

tempArray = Selection.Value

For i = LBound(tempArray, 1) To UBound(tempArray, 1)

    tempStr = tempStr & Chr(0) & tempArray(i, 1)

Next

tempStr = tempStr & Chr(0)

For Each oneCell In Range(Selection.EntireColumn.End(xlUp), Selection.EntireColumn.End(xlDown))

    If InStr(tempStr, Chr(0) & oneCell.Value & Chr(0)) Then

        tempSelection = tempSelection & "," & oneCell.Address

    End If

Next

Range(Mid(tempSelection, 2)).Select

End Sub

share|improve this answer

edited yesterday

answered yesterday

AkinaAkina

1,38329

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ,, this code is doing nothing,, OP wants to display Duplicates !!
  
  – Rajesh S
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RajeshS This code selects all cells within a column which have the value equal to any value within starting selection - i.e. it selects all duplicates. In any case, the result is exactly what OP shows. Select first two cells, run the code - you will obtain 1,2,7 and 9 cells selected.
  
  – Akina
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've already tried it ,, then written comments,, finding no effect,, is this Standard module or a Module ?
  
  – Rajesh S
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RajeshS of course, Create - Module. All operations are performed on active sheet.
  
  – Akina
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've use as Module too,, but no effect!!
  
  – Rajesh S
  yesterday
  

  
  
  
  

 | 
show 2 more comments

0

You can use AutoFilter

1. select 34 & 73 in AutoFilter


2. mark each cell with CTRL + single Mouse klick


3. select all in AutoFilter

share|improve this answer

edited yesterday

bummi

1,49031422

answered yesterday

alecxsalecxs

464

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
});


}
});

James is a new contributor. Be nice, and check out our Code of Conduct.

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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1423203%2ffind-duplicates-outside-of-selection-highlight-in-the-same-column-excel%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1

Sub SelectBySelection()

Dim tempArray()

Dim oneCell As Range

Dim i As Integer

Dim tempStr As String

Dim tempSelection As String



If Selection.Areas.Count > 1 Then Exit Sub

tempArray = Selection.Value

For i = LBound(tempArray, 1) To UBound(tempArray, 1)

    tempStr = tempStr & Chr(0) & tempArray(i, 1)

Next

tempStr = tempStr & Chr(0)

For Each oneCell In Range(Selection.EntireColumn.End(xlUp), Selection.EntireColumn.End(xlDown))

    If InStr(tempStr, Chr(0) & oneCell.Value & Chr(0)) Then

        tempSelection = tempSelection & "," & oneCell.Address

    End If

Next

Range(Mid(tempSelection, 2)).Select

End Sub

share|improve this answer

edited yesterday

answered yesterday

AkinaAkina

1,38329

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ,, this code is doing nothing,, OP wants to display Duplicates !!
  
  – Rajesh S
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RajeshS This code selects all cells within a column which have the value equal to any value within starting selection - i.e. it selects all duplicates. In any case, the result is exactly what OP shows. Select first two cells, run the code - you will obtain 1,2,7 and 9 cells selected.
  
  – Akina
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've already tried it ,, then written comments,, finding no effect,, is this Standard module or a Module ?
  
  – Rajesh S
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RajeshS of course, Create - Module. All operations are performed on active sheet.
  
  – Akina
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've use as Module too,, but no effect!!
  
  – Rajesh S
  yesterday
  

  
  
  
  

 | 
show 2 more comments

1

Sub SelectBySelection()

Dim tempArray()

Dim oneCell As Range

Dim i As Integer

Dim tempStr As String

Dim tempSelection As String



If Selection.Areas.Count > 1 Then Exit Sub

tempArray = Selection.Value

For i = LBound(tempArray, 1) To UBound(tempArray, 1)

    tempStr = tempStr & Chr(0) & tempArray(i, 1)

Next

tempStr = tempStr & Chr(0)

For Each oneCell In Range(Selection.EntireColumn.End(xlUp), Selection.EntireColumn.End(xlDown))

    If InStr(tempStr, Chr(0) & oneCell.Value & Chr(0)) Then

        tempSelection = tempSelection & "," & oneCell.Address

    End If

Next

Range(Mid(tempSelection, 2)).Select

End Sub

share|improve this answer

edited yesterday

answered yesterday

AkinaAkina

1,38329

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ,, this code is doing nothing,, OP wants to display Duplicates !!
  
  – Rajesh S
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RajeshS This code selects all cells within a column which have the value equal to any value within starting selection - i.e. it selects all duplicates. In any case, the result is exactly what OP shows. Select first two cells, run the code - you will obtain 1,2,7 and 9 cells selected.
  
  – Akina
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've already tried it ,, then written comments,, finding no effect,, is this Standard module or a Module ?
  
  – Rajesh S
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RajeshS of course, Create - Module. All operations are performed on active sheet.
  
  – Akina
  yesterday
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've use as Module too,, but no effect!!
  
  – Rajesh S
  yesterday
  

  
  
  
  

 | 
show 2 more comments

Sub SelectBySelection()

Dim tempArray()

Dim oneCell As Range

Dim i As Integer

Dim tempStr As String

Dim tempSelection As String



If Selection.Areas.Count > 1 Then Exit Sub

tempArray = Selection.Value

For i = LBound(tempArray, 1) To UBound(tempArray, 1)

    tempStr = tempStr & Chr(0) & tempArray(i, 1)

Next

tempStr = tempStr & Chr(0)

For Each oneCell In Range(Selection.EntireColumn.End(xlUp), Selection.EntireColumn.End(xlDown))

    If InStr(tempStr, Chr(0) & oneCell.Value & Chr(0)) Then

        tempSelection = tempSelection & "," & oneCell.Address

    End If

Next

Range(Mid(tempSelection, 2)).Select

End Sub

share|improve this answer

edited yesterday

answered yesterday

AkinaAkina

1,38329

Sub SelectBySelection()

Dim tempArray()

Dim oneCell As Range

Dim i As Integer

Dim tempStr As String

Dim tempSelection As String



If Selection.Areas.Count > 1 Then Exit Sub

tempArray = Selection.Value

For i = LBound(tempArray, 1) To UBound(tempArray, 1)

    tempStr = tempStr & Chr(0) & tempArray(i, 1)

Next

tempStr = tempStr & Chr(0)

For Each oneCell In Range(Selection.EntireColumn.End(xlUp), Selection.EntireColumn.End(xlDown))

    If InStr(tempStr, Chr(0) & oneCell.Value & Chr(0)) Then

        tempSelection = tempSelection & "," & oneCell.Address

    End If

Next

Range(Mid(tempSelection, 2)).Select

End Sub

share|improve this answer

edited yesterday

answered yesterday

AkinaAkina

1,38329

answered yesterday

AkinaAkina

1,38329

answered yesterday

AkinaAkina

1,38329

0

You can use AutoFilter

1. select 34 & 73 in AutoFilter


2. mark each cell with CTRL + single Mouse klick


3. select all in AutoFilter

share|improve this answer

edited yesterday

bummi

1,49031422

answered yesterday

alecxsalecxs

464

add a comment | 

0

You can use AutoFilter

1. select 34 & 73 in AutoFilter


2. mark each cell with CTRL + single Mouse klick


3. select all in AutoFilter

share|improve this answer

edited yesterday

bummi

1,49031422

answered yesterday

alecxsalecxs

464

add a comment | 

You can use AutoFilter

1. select 34 & 73 in AutoFilter


2. mark each cell with CTRL + single Mouse klick


3. select all in AutoFilter

share|improve this answer

edited yesterday

bummi

1,49031422

answered yesterday

alecxsalecxs

464

share|improve this answer

edited yesterday

bummi

1,49031422

answered yesterday

alecxsalecxs

464

edited yesterday

bummi

1,49031422

edited yesterday

bummi

1,49031422

answered yesterday

alecxsalecxs

464

answered yesterday

alecxsalecxs

464