Tjyylli

Why isn't there a non-conducting core wire for high-frequency coil applications

Can I string the D&D Starter Set campaign into another module, keeping the same characters?

How can animals be objects of ethics without being subjects as well?

How to avoid being sexist when trying to employ someone to function in a very sexist environment?

Pronunciation of umlaut vowels in the history of German

Table formatting top left corner caption

Why would space fleets be aligned?

Incorporating research and background: How much is too much?

Could a phylactery of a lich be a mirror or does it have to be a box?

Difference between `vector<int> v;` and `vector<int> v = vector<int>();`

Publishing research using outdated methods

How should I handle players who ignore the session zero agreement?

Intern applicant asking for compensation equivalent to that of permanent employee

Porting Linux to another platform requirements

Why is working on the same position for more than 15 years not a red flag?

Why are the books in the Game of Thrones citadel library shelved spine inwards?

What is the purpose of easy combat scenarios that don't need resource expenditure?

Early credit roll before the end of the film

My cat mixes up the floors in my building. How can I help him?

Does paint affect EMI ability of enclosure?

Can a person refuse a presidential pardon?

Dilemma of explaining to interviewer that he is the reason for declining second interview

Why Normality assumption in linear regression

Why zero tolerance on nudity in space?

Excel: Count number of times a value appears twice and only twice in a column

Microsoft Excel - Count the number of values each name has in a single columnMicrosoft Excel value lookup, return result in an undefined number of row aboveGet distinct count based on different parametersExcel - Find if a number appears in another sheetEXCEL: I want to count how many times a certain word appears in a column if it's in the same row if another word appearsExcel how to restart the number count if A1:Z1 if A<B, B<C, C<D, count only if greater. Reset if notHow to sum only values in one column that have a unique value in another column?Count distinct on multiple criteria using FREQUENCY()Formatting Columns in Excel so that Only the First New Value AppearsExcel: Number of occurrences in a table

0

I have a dataset of 100,000 rows. It is set up in such a way that Column A contains a group name, and then repeats the group name for the number of unique members of that group. I am trying to get a count of how many times a value appears twice and only twice.

• A value will never appear only once - there will always be "group name" immediately followed by however many members are in that group, in individual rows. So for any distinct entity, there are always at least 2 rows.


• More often than not, a value will appear 3 or more times.


• If the value appears 3 or more times, I do not want to include any of those rows in the count. I'm really looking for the number of times a distinct pair appears.

microsoft-excel worksheet-function microsoft-excel-2010

share|improve this question

asked Feb 27 '18 at 22:43

Shiva HarrisShiva Harris

11

bumped to the homepage by Community♦ 7 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

0

I have a dataset of 100,000 rows. It is set up in such a way that Column A contains a group name, and then repeats the group name for the number of unique members of that group. I am trying to get a count of how many times a value appears twice and only twice.

• A value will never appear only once - there will always be "group name" immediately followed by however many members are in that group, in individual rows. So for any distinct entity, there are always at least 2 rows.


• More often than not, a value will appear 3 or more times.


• If the value appears 3 or more times, I do not want to include any of those rows in the count. I'm really looking for the number of times a distinct pair appears.

microsoft-excel worksheet-function microsoft-excel-2010

share|improve this question

asked Feb 27 '18 at 22:43

Shiva HarrisShiva Harris

11

bumped to the homepage by Community♦ 7 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

I have a dataset of 100,000 rows. It is set up in such a way that Column A contains a group name, and then repeats the group name for the number of unique members of that group. I am trying to get a count of how many times a value appears twice and only twice.

• A value will never appear only once - there will always be "group name" immediately followed by however many members are in that group, in individual rows. So for any distinct entity, there are always at least 2 rows.


• More often than not, a value will appear 3 or more times.


• If the value appears 3 or more times, I do not want to include any of those rows in the count. I'm really looking for the number of times a distinct pair appears.

microsoft-excel worksheet-function microsoft-excel-2010

share|improve this question

asked Feb 27 '18 at 22:43

Shiva HarrisShiva Harris

11

share|improve this question

asked Feb 27 '18 at 22:43

Shiva HarrisShiva Harris

11

share|improve this question

asked Feb 27 '18 at 22:43

Shiva HarrisShiva Harris

11

asked Feb 27 '18 at 22:43

Shiva HarrisShiva Harris

11

asked Feb 27 '18 at 22:43

Shiva HarrisShiva Harris

11

2 Answers
2

active

oldest

votes

0

Assuming your data is in A1:A100000

1) Copy all the unique values in a separate column (using Data -> advanced filter) -- I'll use column C for my example.

2) Put the following formula: =Countif(A$1:A$100000, C1) in D1, then fill each cell in D for every Unique value in C.

3) In another cell use the follwing formula: =Countif(D1:D??, 2) (where ?? is the last row of columns C and D)

share|improve this answer

answered Feb 28 '18 at 1:16

cybernetic.nomadcybernetic.nomad

1,775312

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think this approach was similar to the previous answer, but I saw that one first. Thanks for answering!
  
  – Shiva Harris
  Feb 28 '18 at 18:22
  

  
  
  
  

add a comment | 

0

Copy the following formula down in column b:

=COUNTIF($A$1:$A$100000,A1)=2

This will identify all pairs. It will however show both entries in the pair. What I normally do in these cases (even though it breaks the data providence) is copy this filtered list to another sheet, and remove duplicates.

If you want to do this in a repeatable way that maintains providence, then I'd recommend using an unique list type array formula after performing the count

Option B, is to use a pivot table, placing your values in column A on the rows, count(A) in the values, and filtering rows on values where count=2

share|improve this answer

answered Feb 28 '18 at 4:20

RowanCRowanC

1413

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think that did the trick! Thank you!
  
  – Shiva Harris
  Feb 28 '18 at 18:21
  

  
  
  
  

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


}
});

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%2f1298969%2fexcel-count-number-of-times-a-value-appears-twice-and-only-twice-in-a-column%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Assuming your data is in A1:A100000

1) Copy all the unique values in a separate column (using Data -> advanced filter) -- I'll use column C for my example.

2) Put the following formula: =Countif(A$1:A$100000, C1) in D1, then fill each cell in D for every Unique value in C.

3) In another cell use the follwing formula: =Countif(D1:D??, 2) (where ?? is the last row of columns C and D)

share|improve this answer

answered Feb 28 '18 at 1:16

cybernetic.nomadcybernetic.nomad

1,775312

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think this approach was similar to the previous answer, but I saw that one first. Thanks for answering!
  
  – Shiva Harris
  Feb 28 '18 at 18:22
  

  
  
  
  

add a comment | 

0

Assuming your data is in A1:A100000

1) Copy all the unique values in a separate column (using Data -> advanced filter) -- I'll use column C for my example.

2) Put the following formula: =Countif(A$1:A$100000, C1) in D1, then fill each cell in D for every Unique value in C.

3) In another cell use the follwing formula: =Countif(D1:D??, 2) (where ?? is the last row of columns C and D)

share|improve this answer

answered Feb 28 '18 at 1:16

cybernetic.nomadcybernetic.nomad

1,775312

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think this approach was similar to the previous answer, but I saw that one first. Thanks for answering!
  
  – Shiva Harris
  Feb 28 '18 at 18:22
  

  
  
  
  

add a comment | 

Assuming your data is in A1:A100000

1) Copy all the unique values in a separate column (using Data -> advanced filter) -- I'll use column C for my example.

2) Put the following formula: =Countif(A$1:A$100000, C1) in D1, then fill each cell in D for every Unique value in C.

3) In another cell use the follwing formula: =Countif(D1:D??, 2) (where ?? is the last row of columns C and D)

share|improve this answer

answered Feb 28 '18 at 1:16

cybernetic.nomadcybernetic.nomad

1,775312

share|improve this answer

answered Feb 28 '18 at 1:16

cybernetic.nomadcybernetic.nomad

1,775312

answered Feb 28 '18 at 1:16

cybernetic.nomadcybernetic.nomad

1,775312

answered Feb 28 '18 at 1:16

cybernetic.nomadcybernetic.nomad

1,775312

0

Copy the following formula down in column b:

=COUNTIF($A$1:$A$100000,A1)=2

This will identify all pairs. It will however show both entries in the pair. What I normally do in these cases (even though it breaks the data providence) is copy this filtered list to another sheet, and remove duplicates.

If you want to do this in a repeatable way that maintains providence, then I'd recommend using an unique list type array formula after performing the count

Option B, is to use a pivot table, placing your values in column A on the rows, count(A) in the values, and filtering rows on values where count=2

share|improve this answer

answered Feb 28 '18 at 4:20

RowanCRowanC

1413

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think that did the trick! Thank you!
  
  – Shiva Harris
  Feb 28 '18 at 18:21
  

  
  
  
  

add a comment | 

0

Copy the following formula down in column b:

=COUNTIF($A$1:$A$100000,A1)=2

This will identify all pairs. It will however show both entries in the pair. What I normally do in these cases (even though it breaks the data providence) is copy this filtered list to another sheet, and remove duplicates.

If you want to do this in a repeatable way that maintains providence, then I'd recommend using an unique list type array formula after performing the count

Option B, is to use a pivot table, placing your values in column A on the rows, count(A) in the values, and filtering rows on values where count=2

share|improve this answer

answered Feb 28 '18 at 4:20

RowanCRowanC

1413

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think that did the trick! Thank you!
  
  – Shiva Harris
  Feb 28 '18 at 18:21
  

  
  
  
  

add a comment | 

Copy the following formula down in column b:

=COUNTIF($A$1:$A$100000,A1)=2

This will identify all pairs. It will however show both entries in the pair. What I normally do in these cases (even though it breaks the data providence) is copy this filtered list to another sheet, and remove duplicates.

If you want to do this in a repeatable way that maintains providence, then I'd recommend using an unique list type array formula after performing the count

Option B, is to use a pivot table, placing your values in column A on the rows, count(A) in the values, and filtering rows on values where count=2

share|improve this answer

answered Feb 28 '18 at 4:20

RowanCRowanC

1413

=COUNTIF($A$1:$A$100000,A1)=2

share|improve this answer

answered Feb 28 '18 at 4:20

RowanCRowanC

1413

answered Feb 28 '18 at 4:20

RowanCRowanC

1413

answered Feb 28 '18 at 4:20

RowanCRowanC

1413