Count Multiples in Column
 

Hello-

I have a very large file. In column "G" are company names sorted alpha. But
they can be repeated row after row.

I need to know which companies are listed 3 or more times.

Adam
Adam
Bob
Bob
Bob
Charlie
Doug
Doug
Doug
Doug

So, do I put a count in a new column "H"?
Adam 2
Adam 2
Bob 3
Bob 3
Bob 3
Charlie 1

Then I need a report show if count is greater than or equal to 3:
Bob 3
Doug 4

Is this possible?
Thanks in advance!

Count Multiples in Column
 

I'd either do this using the countif function or with a pivot table. I
think it would be easier with a pivot table. Check this out and if you have
questions, come back

http://www.cpearson.com/excel/pivots.htm

"DTTODGG" wrote:

Hello-

I have a very large file. In column "G" are company names sorted alpha. But
they can be repeated row after row.

I need to know which companies are listed 3 or more times.

Adam
Adam
Bob
Bob
Bob
Charlie
Doug
Doug
Doug
Doug

So, do I put a count in a new column "H"?
Adam 2
Adam 2
Bob 3
Bob 3
Bob 3
Charlie 1

Then I need a report show if count is greater than or equal to 3:
Bob 3
Doug 4

Is this possible?
Thanks in advance!

Count Multiples in Column
 

Thank-you for the pivot table suggestion.

I have created the pivot table, but there are MANY companies that have less
that 3 occurances. I don't know how to not show them on the pivot table.
Somehow, if the company is listed 3 or more times, I want them, if not, I
don't.

I can see using the pivot table after I have weeded out the little guys - or
find a way to eliminate them on the pivot table.

Thanks again.



"Barb Reinhardt" wrote:

I'd either do this using the countif function or with a pivot table. I
think it would be easier with a pivot table. Check this out and if you have
questions, come back

http://www.cpearson.com/excel/pivots.htm

"DTTODGG" wrote:

Hello-

I have a very large file. In column "G" are company names sorted alpha. But
they can be repeated row after row.

I need to know which companies are listed 3 or more times.

Adam
Adam
Bob
Bob
Bob
Charlie
Doug
Doug
Doug
Doug

So, do I put a count in a new column "H"?
Adam 2
Adam 2
Bob 3
Bob 3
Bob 3
Charlie 1

Then I need a report show if count is greater than or equal to 3:
Bob 3
Doug 4

Is this possible?
Thanks in advance!

Count Multiples in Column
 

You could sort the count in descending order to get the top ones. Maybe
someone else has a better suggestion.

"DTTODGG" wrote:

Thank-you for the pivot table suggestion.

I have created the pivot table, but there are MANY companies that have less
that 3 occurances. I don't know how to not show them on the pivot table.
Somehow, if the company is listed 3 or more times, I want them, if not, I
don't.

I can see using the pivot table after I have weeded out the little guys - or
find a way to eliminate them on the pivot table.

Thanks again.



"Barb Reinhardt" wrote:

I'd either do this using the countif function or with a pivot table. I
think it would be easier with a pivot table. Check this out and if you have
questions, come back

http://www.cpearson.com/excel/pivots.htm

"DTTODGG" wrote:

Hello-

I have a very large file. In column "G" are company names sorted alpha. But
they can be repeated row after row.

I need to know which companies are listed 3 or more times.

Adam
Adam
Bob
Bob
Bob
Charlie
Doug
Doug
Doug
Doug

So, do I put a count in a new column "H"?
Adam 2
Adam 2
Bob 3
Bob 3
Bob 3
Charlie 1

Then I need a report show if count is greater than or equal to 3:
Bob 3
Doug 4

Is this possible?
Thanks in advance!

Count Multiples in Column
 

I did sort the top ones, but the pivot table will not let me "delete" the
others.
Help!

"Barb Reinhardt" wrote:

You could sort the count in descending order to get the top ones. Maybe
someone else has a better suggestion.

"DTTODGG" wrote:

Thank-you for the pivot table suggestion.

I have created the pivot table, but there are MANY companies that have less
that 3 occurances. I don't know how to not show them on the pivot table.
Somehow, if the company is listed 3 or more times, I want them, if not, I
don't.

I can see using the pivot table after I have weeded out the little guys - or
find a way to eliminate them on the pivot table.

Thanks again.



"Barb Reinhardt" wrote:

I'd either do this using the countif function or with a pivot table. I
think it would be easier with a pivot table. Check this out and if you have
questions, come back

http://www.cpearson.com/excel/pivots.htm

"DTTODGG" wrote:

Hello-

I have a very large file. In column "G" are company names sorted alpha. But
they can be repeated row after row.

I need to know which companies are listed 3 or more times.

Adam
Adam
Bob
Bob
Bob
Charlie
Doug
Doug
Doug
Doug

So, do I put a count in a new column "H"?
Adam 2
Adam 2
Bob 3
Bob 3
Bob 3
Charlie 1

Then I need a report show if count is greater than or equal to 3:
Bob 3
Doug 4

Is this possible?
Thanks in advance!

Count Multiples in Column
 

I'm thinking you may need some VBA to do the rest of what you want. You may
want to post this in the PROGRAMMING group to see what they say.

"DTTODGG" wrote:

I did sort the top ones, but the pivot table will not let me "delete" the
others.
Help!

"Barb Reinhardt" wrote:

You could sort the count in descending order to get the top ones. Maybe
someone else has a better suggestion.

"DTTODGG" wrote:

Thank-you for the pivot table suggestion.

I have created the pivot table, but there are MANY companies that have less
that 3 occurances. I don't know how to not show them on the pivot table.
Somehow, if the company is listed 3 or more times, I want them, if not, I
don't.

I can see using the pivot table after I have weeded out the little guys - or
find a way to eliminate them on the pivot table.

Thanks again.



"Barb Reinhardt" wrote:

I'd either do this using the countif function or with a pivot table. I
think it would be easier with a pivot table. Check this out and if you have
questions, come back

http://www.cpearson.com/excel/pivots.htm

"DTTODGG" wrote:

Hello-

I have a very large file. In column "G" are company names sorted alpha. But
they can be repeated row after row.

I need to know which companies are listed 3 or more times.

Adam
Adam
Bob
Bob
Bob
Charlie
Doug
Doug
Doug
Doug

So, do I put a count in a new column "H"?
Adam 2
Adam 2
Bob 3
Bob 3
Bob 3
Charlie 1

Then I need a report show if count is greater than or equal to 3:
Bob 3
Doug 4

Is this possible?
Thanks in advance!

Count Multiples in Column
 

Ref your original post, try this simple non-array formulas play ..

Assuming source data running in G1 down,

Adam
Adam
Bob
Bob
Bob
Charlie
Doug
Doug
Doug
Doug
etc

Put in H1:
=IF(COUNTIF($G$1:G1,G1)=3,ROW(),"")

In I1:
=IF(ROW(G1)COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H: H,ROW(G1)),H:H,0)))

In J1:
=IF(I1="","",COUNTIF(G:G,I1))

Then just select H1:J1, fill down to the last row of data in col G. Hide
away col H. Cols I and J will return the required results, all neatly bunched
at the top. For the sample data, you'd get:

Bob 3
Doug 4

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Count Multiples in Column
 

In I1:
=IF(ROW(G1)COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H: H,ROW(G1)),H:H,0)))

No problem with the above as-is, but it should have read as:
=IF(ROW(A1)COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H: H,ROW(A1)),H:H,0)))

I'd normally use ROW(A1) out of convention <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Count Multiples in Column
 

Max,

I am glad you show up as I want to learn how to do this as well. I will digest your formulas later. I see SMALL ( ) in your formula and I hope I can figure it out this time.

Can I just share with everyone what I have done to achieve the results? I am a beginner and the following is what I can manage without pulling too many hairs. Please correct me if I am wrong or help me enhance my method.

I put an *array formula* in column H.

=IF(COUNTIF(G:G,G1)=3,1,0)

I can use dynamic range if I am more advanced.

Then I do AutoFilter to pick out the "1".

Then I do DataSubtotal and sum up by name.

Then I "collapse" the data to just show the subtotals.

Some people cannot or don't like using AutoFilter or Data menu, and I am sure your way is the way. I wonder if there is anything in between. I am going to think some more as to how I can sum by name after AutoFilter. I want to use a formula instead of DataSubtotal.

Please feel free to comment.

Epinn

"Max" wrote in message ...
In I1:
=IF(ROW(G1)COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H: H,ROW(G1)),H:H,0)))

No problem with the above as-is, but it should have read as:
=IF(ROW(A1)COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H: H,ROW(A1)),H:H,0)))

I'd normally use ROW(A1) out of convention <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Count Multiples in Column
 

HI

I had a header row at the top of my data.
Change the formula to
=IF(COUNTIF($G$2:G2,G2)<COUNTIF($G$2:$G$11,G2),"", COUNTIF($G$2:G2,G2))
and copy down
Then on column G header
DataFilterAutofilterCustom =3

--
Regards

Roger Govier


"Epinn" wrote in message
...
Max,

I am glad you show up as I want to learn how to do this as well. I will
digest your formulas later. I see SMALL ( ) in your formula and I hope
I can figure it out this time.

Can I just share with everyone what I have done to achieve the results?
I am a beginner and the following is what I can manage without pulling
too many hairs. Please correct me if I am wrong or help me enhance my
method.

I put an *array formula* in column H.

=IF(COUNTIF(G:G,G1)=3,1,0)

I can use dynamic range if I am more advanced.

Then I do AutoFilter to pick out the "1".

Then I do DataSubtotal and sum up by name.

Then I "collapse" the data to just show the subtotals.

Some people cannot or don't like using AutoFilter or Data menu, and I am
sure your way is the way. I wonder if there is anything in between. I
am going to think some more as to how I can sum by name after
AutoFilter. I want to use a formula instead of DataSubtotal.

Please feel free to comment.

Epinn

"Max" wrote in message
...
In I1:
=IF(ROW(G1)COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H: H,ROW(G1)),H:H,0)))

No problem with the above as-is, but it should have read as:
=IF(ROW(A1)COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H: H,ROW(A1)),H:H,0)))

I'd normally use ROW(A1) out of convention <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Count Multiples in Column
 

This is what I have been trying to achieve but not sure how. Thanks a lot!

Hopefully, COUNTIF is not hard on the system as we have to do COUNTIF at least twice for each cell. The poster mentioned "a very large file."

E PIN N (odd and even)

"Roger Govier" wrote in message ...
HI

I had a header row at the top of my data.
Change the formula to
=IF(COUNTIF($G$2:G2,G2)<COUNTIF($G$2:$G$11,G2),"", COUNTIF($G$2:G2,G2))
and copy down
Then on column G header
DataFilterAutofilterCustom =3

--
Regards

Roger Govier


"Epinn" wrote in message
...
Max,

I am glad you show up as I want to learn how to do this as well. I will
digest your formulas later. I see SMALL ( ) in your formula and I hope
I can figure it out this time.

Can I just share with everyone what I have done to achieve the results?
I am a beginner and the following is what I can manage without pulling
too many hairs. Please correct me if I am wrong or help me enhance my
method.

I put an *array formula* in column H.

=IF(COUNTIF(G:G,G1)=3,1,0)

I can use dynamic range if I am more advanced.

Then I do AutoFilter to pick out the "1".

Then I do DataSubtotal and sum up by name.

Then I "collapse" the data to just show the subtotals.

Some people cannot or don't like using AutoFilter or Data menu, and I am
sure your way is the way. I wonder if there is anything in between. I
am going to think some more as to how I can sum by name after
AutoFilter. I want to use a formula instead of DataSubtotal.

Please feel free to comment.

Epinn

"Max" wrote in message
...
In I1:
=IF(ROW(G1)COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H: H,ROW(G1)),H:H,0)))

No problem with the above as-is, but it should have read as:
=IF(ROW(A1)COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H: H,ROW(A1)),H:H,0)))

I'd normally use ROW(A1) out of convention <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Count Multiples in Column
 

Note: It is not really necessary to have an array formula; a regular formula will do.

"Epinn" wrote in message ...
Max,

I am glad you show up as I want to learn how to do this as well. I will digest your formulas later. I see SMALL ( ) in your formula and I hope I can figure it out this time.

Can I just share with everyone what I have done to achieve the results? I am a beginner and the following is what I can manage without pulling too many hairs. Please correct me if I am wrong or help me enhance my method.

I put an *array formula* in column H.

=IF(COUNTIF(G:G,G1)=3,1,0)

I can use dynamic range if I am more advanced.

Then I do AutoFilter to pick out the "1".

Then I do DataSubtotal and sum up by name.

Then I "collapse" the data to just show the subtotals.

Some people cannot or don't like using AutoFilter or Data menu, and I am sure your way is the way. I wonder if there is anything in between. I am going to think some more as to how I can sum by name after AutoFilter. I want to use a formula instead of DataSubtotal.

Please feel free to comment.

Epinn

"Max" wrote in message ...
In I1:
=IF(ROW(G1)COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H: H,ROW(G1)),H:H,0)))

No problem with the above as-is, but it should have read as:
=IF(ROW(A1)COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H: H,ROW(A1)),H:H,0)))

I'd normally use ROW(A1) out of convention <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---