| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 --- |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 --- |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 --- |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 --- |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 --- |
|
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Only count columns if the column next to it is not blank | Excel Discussion (Misc queries) | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
| match and count words | Excel Worksheet Functions | |||
Linear Mode
