I have to produce some reports on a work team's performance over a specified time period. The data is easily extracted from the SQL work database as a .csv file which I import into Excel. Each job has a job number which is recorded in a column in the .csv. My problem is that each contact with a job is recorded in a row in the .csv file, so that for instance job no 7453 may appear on four or five rows. I thought there would be a function which would tell me how many unique job numbers there are, but if there is I can't find it. As it is, I have a .csv file which runs to 2,500-odd rows but probably only refers to four or five hundred jobs.
I'm feeling like a complete numpty with this, and would be grateful for any help offered.
Last edited by Moondog; 03-18-2010 at 07:57 AM.
To count unique values in a column use something like:
=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
If you're looking for a list of those unique numbers, rather than a count, highlight the list, go to Advanced Filter, select Copy to Another Location, pick the location, then check Unique records only.
Thank you both so much! I've tried both those solutions and they work brilliantly! NBVC's formula solves my immediate problem, and darkyam's enables me to add extra value to the document I'm producing. I'm very grateful to you both.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks