How can I count how many times different texts repeat in a column?
Hey,
I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
How can I count how many times different texts repeat in a column?
Try this:
=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- Biff Microsoft Excel MVP "pjr" wrote in message ... Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
How can I count how many times different texts repeat in a column?
Try this:
=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "pjr" wrote in message ... Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
How can I count how many times different texts repeat in a column?
Hi
Select the column "Data" "Filter" "Advanced Filter" Check "Unique records only" OK In the bottom of the screen you will see the number of Unique records. To show all records again Data Filter Show All Regards, Per "pjr" skrev i meddelelsen ... Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
How can I count how many times different texts repeat in a col
When I copied this over to the cell, I was told t hat there were not enough
arguments in the function "T. Valko" wrote: Try this: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- Biff Microsoft Excel MVP "pjr" wrote in message ... Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
How can I count how many times different texts repeat in a col
This did exactly what I'm looking to do, but I need it to be done in a cell,
as a function, on a separate worksheet. "Per Jessen" wrote: Hi Select the column "Data" "Filter" "Advanced Filter" Check "Unique records only" OK In the bottom of the screen you will see the number of Unique records. To show all records again Data Filter Show All Regards, Per "pjr" skrev i meddelelsen ... Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
How can I count how many times different texts repeat in a col
In that case, you have mis-copied Biff's formula.
Are you missing the comma or does your system require a semi-colon, not a comma? Gord Dibben MS Excel MVP On Tue, 15 Apr 2008 08:48:01 -0700, pjr wrote: When I copied this over to the cell, I was told t hat there were not enough arguments in the function "T. Valko" wrote: Try this: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- Biff Microsoft Excel MVP "pjr" wrote in message ... Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
How can I count how many times different texts repeat in a col
you are right. I got it to work now. Thank you very much. One more thing,
though: I need to incorporate a couple of criteria into this formula. In a separate column is written whether or not these corporations are Non Profit or For Profit. And again in another is listed what program they are applying for. The non profit or for profit status is listed as text, but the program listings are shown as numerical requests in seperate columns (i.e. if they are applying for SAIL, then in the Sail column is a $ amount; for HOME, then in the HOME column there is a $ amount...). How do I build on this formula to specify how many For Profit corporations there are? and then how do I specify how many For Profit corporations there are applying for each program? Thank you for the help, pjr "Gord Dibben" wrote: In that case, you have mis-copied Biff's formula. Are you missing the comma or does your system require a semi-colon, not a comma? Gord Dibben MS Excel MVP On Tue, 15 Apr 2008 08:48:01 -0700, pjr wrote: When I copied this over to the cell, I was told t hat there were not enough arguments in the function "T. Valko" wrote: Try this: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- Biff Microsoft Excel MVP "pjr" wrote in message ... Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
How can I count how many times different texts repeat in a col
Hi. I had the same problemas pjr. Tried This formula and it worked for a
small subset of my data. When I try it on the full 70 000 entries my computer locks up. Is there a more efficient way of doing this? Cheers, Jai "T. Valko" wrote: Try this: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- Biff Microsoft Excel MVP "pjr" wrote in message ... Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
How can I count how many times different texts repeat in a col
If you can download and install the Morefunc.xll add-in from:
http://xcell05.free.fr/morefunc/english/index.htm There is a function, COUNTDIFF, that is significantly faster than the standard: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) I haven't tested it yet in Excel 2007 on really big ranges, 65,000 rows, but in my (working) version of Excel (2002) it calculates 65,000 rows without a blip! -- Biff Microsoft Excel MVP "Jai" wrote in message ... Hi. I had the same problemas pjr. Tried This formula and it worked for a small subset of my data. When I try it on the full 70 000 entries my computer locks up. Is there a more efficient way of doing this? Cheers, Jai "T. Valko" wrote: Try this: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- Biff Microsoft Excel MVP "pjr" wrote in message ... Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
How can I count how many times different texts repeat in a col
Thanks
"T. Valko" wrote: If you can download and install the Morefunc.xll add-in from: http://xcell05.free.fr/morefunc/english/index.htm There is a function, COUNTDIFF, that is significantly faster than the standard: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) I haven't tested it yet in Excel 2007 on really big ranges, 65,000 rows, but in my (working) version of Excel (2002) it calculates 65,000 rows without a blip! -- Biff Microsoft Excel MVP "Jai" wrote in message ... Hi. I had the same problemas pjr. Tried This formula and it worked for a small subset of my data. When I try it on the full 70 000 entries my computer locks up. Is there a more efficient way of doing this? Cheers, Jai "T. Valko" wrote: Try this: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- Biff Microsoft Excel MVP "pjr" wrote in message ... Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
How can I count how many times different texts repeat in a col
You're welcome!
-- Biff Microsoft Excel MVP "Jai" wrote in message ... Thanks "T. Valko" wrote: If you can download and install the Morefunc.xll add-in from: http://xcell05.free.fr/morefunc/english/index.htm There is a function, COUNTDIFF, that is significantly faster than the standard: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) I haven't tested it yet in Excel 2007 on really big ranges, 65,000 rows, but in my (working) version of Excel (2002) it calculates 65,000 rows without a blip! -- Biff Microsoft Excel MVP "Jai" wrote in message ... Hi. I had the same problemas pjr. Tried This formula and it worked for a small subset of my data. When I try it on the full 70 000 entries my computer locks up. Is there a more efficient way of doing this? Cheers, Jai "T. Valko" wrote: Try this: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- Biff Microsoft Excel MVP "pjr" wrote in message ... Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
How can I count how many times different texts repeat in a col
I download and installed the add-in. The COUNTDIFF function works for 2003
but doesn't seem to be in 2007 at all. I'm looking under the formula tab as well as typing =COUNTDIFF into the formula bar with no success. Am I just missing something obvious here? Thanks for your help so far. "T. Valko" wrote: If you can download and install the Morefunc.xll add-in from: http://xcell05.free.fr/morefunc/english/index.htm There is a function, COUNTDIFF, that is significantly faster than the standard: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) I haven't tested it yet in Excel 2007 on really big ranges, 65,000 rows, but in my (working) version of Excel (2002) it calculates 65,000 rows without a blip! -- Biff Microsoft Excel MVP "Jai" wrote in message ... Hi. I had the same problemas pjr. Tried This formula and it worked for a small subset of my data. When I try it on the full 70 000 entries my computer locks up. Is there a more efficient way of doing this? Cheers, Jai "T. Valko" wrote: Try this: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- Biff Microsoft Excel MVP "pjr" wrote in message ... Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
How can I count how many times different texts repeat in a col
Are you sure the add-in is installed in Excel 2007?
If you goto the Formulas tab there should be a group called Morefunc. This is the eqivalent of the Insert Function button on the formula bar. You can also use the Insert Function button on the formula bar. From the category drop down select Morefunc. If you type in the formula and got a #NAME? error then that means the add-in isn't installed. -- Biff Microsoft Excel MVP "Jai" wrote in message ... I download and installed the add-in. The COUNTDIFF function works for 2003 but doesn't seem to be in 2007 at all. I'm looking under the formula tab as well as typing =COUNTDIFF into the formula bar with no success. Am I just missing something obvious here? Thanks for your help so far. "T. Valko" wrote: If you can download and install the Morefunc.xll add-in from: http://xcell05.free.fr/morefunc/english/index.htm There is a function, COUNTDIFF, that is significantly faster than the standard: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) I haven't tested it yet in Excel 2007 on really big ranges, 65,000 rows, but in my (working) version of Excel (2002) it calculates 65,000 rows without a blip! -- Biff Microsoft Excel MVP "Jai" wrote in message ... Hi. I had the same problemas pjr. Tried This formula and it worked for a small subset of my data. When I try it on the full 70 000 entries my computer locks up. Is there a more efficient way of doing this? Cheers, Jai "T. Valko" wrote: Try this: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- Biff Microsoft Excel MVP "pjr" wrote in message ... Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. |
All times are GMT +1. The time now is 03:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com