How to count # of original entries in long column
Posted by Joan L. on June 29, 2001 12:38 PM
I have spreadsheets with thousands of rows. One column has the invoice number. There are multiple entries for some invoices, sometimes as few as two and other times as many as 35. I often need to know how many unique invoices are on the spreadsheet. Is there a simple way to do it? Right now, I do an Advance Autofilter for the column which provides lines of unique entries in that column. The problem is that the only way to get a count of those is to paste the visible cells somewhere and then get a new count for the new column. Is there an more automated way to do this as this is an excercise I have to do daily!?
Posted by Aladin Akyurek on June 29, 2001 12:58 PM
Joan,
Are your invoices numeric or alfanumeric?
Aladin
Posted by Malc on June 29, 2001 1:23 PM
You could use a pivot table with the invoice number in the row field and one other column data in the data feild set to count make sure this has something in everycell.
Posted by Joan L. on June 29, 2001 5:25 PM
They are numeric only. And...I left something out that apparently matters. The files come to me "as is." I don't create them myself so I have no control over the initial set up--and there is no way I can get anyone to change the format as there are a minimum of 40 columns (and up to 100) depending on the project and I am only one of hundreds of users that the file goes to.
Posted by Aladin Akyurek on June 29, 2001 11:23 PM
Joan,
The following FREQUENCY formula [ a courtesy of Charles Pearson ] will do what you want:
=SUM(IF(FREQUENCY($A$1:$A$1000,$A$1:$A$1000)>0,1))
Adapt the range to your situation.
Aladin
============
Posted by Joan L. on June 30, 2001 8:51 AM
Thanks for taking the time to answer. I am going to try it Monday at work!
