Count Multiples in Column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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.
 
You could sort the count in descending order to get the top ones. Maybe
someone else has a better suggestion.
 
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.
 
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
Click to expand...
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
Click to expand...


---
 
In I1:
=IF(ROW(G1)>COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H:H,ROW(G1)),H:H,0)))
Click to expand...

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)))
Click to expand...

I'd normally use ROW(A1) out of convention <g>

---
 
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 Data>Subtotal 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 Data>Subtotal.

Please feel free to comment.

Epinn

Max said:
In I1:
=IF(ROW(G1)>COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H:H,ROW(G1)),H:H,0)))
Click to expand...

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)))
Click to expand...

I'd normally use ROW(A1) out of convention <g>
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
 
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
Data>Filter>Autofilter>Custom> >=3

--
Regards

Roger Govier


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 Data>Subtotal 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 Data>Subtotal.

Please feel free to comment.

Epinn

Max said:
In I1:
=IF(ROW(G1)>COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H:H,ROW(G1)),H:H,0)))
Click to expand...

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)))
Click to expand...

I'd normally use ROW(A1) out of convention <g>

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

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
Data>Filter>Autofilter>Custom> >=3

--
Regards

Roger Govier


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 Data>Subtotal 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 Data>Subtotal.

Please feel free to comment.

Epinn

Max said:
In I1:
=IF(ROW(G1)>COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H:H,ROW(G1)),H:H,0)))
Click to expand...

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)))
Click to expand...

I'd normally use ROW(A1) out of convention <g>
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
 
Note: It is not really necessary to have an array formula; a regular formula will do.

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 Data>Subtotal 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 Data>Subtotal.

Please feel free to comment.

Epinn

Max said:
In I1:
=IF(ROW(G1)>COUNT(H:H),"",INDEX(G:G,MATCH(SMALL(H:H,ROW(G1)),H:H,0)))
Click to expand...

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)))
Click to expand...

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