Count Multiples in Column

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!
 
G

Guest

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.
 
G

Guest

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

Guest

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

Guest

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.
 
G

Guest

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


---
 
G

Guest

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>

---
 
E

Epinn

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

Roger Govier

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>

---
 
E

Epinn

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

Epinn

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy Non-Consecutive Rows and Paste in a Seperate Worksheet 11
Grouping 1
COUNTIF not including something in both columns 2
Count column b if column a equal cell value 7
count if 1
IF column contains certain text return value of corresponding row 7
lookup in a matrix 2
multiple column sort in a pivot table 6

Top