Excel Counting unique values in Excel, based on criteria.

[will.b]

Hello everyone - i have been working with this for a while now, and I haven't made the kind of progress that I would like. I'm in Excel 2007, and i'm looking for a way to (using cell logic) to count a list of unique values in a column, based on a column with a month key (like "200702" means Feb 2007). I want to stick with cell logic, so array equations are fine. So let me pose my question to you:

Let's say I work at a car repair center. When I'm documenting my work, I write down the license plate number (which can be ANY sequence of letters and numbers, and CANNOT be predicted) and the number of things that I work on in the car (represented by each row), and the month I work on it in. Some cars may only have one problem, while others may have two or three (so that would lead to duplicate rows with the same license plate number). So the question is - In January 2007 (200701), how many cars did I work on? It's very easy to count how many total issues that we dealt with, but not unique license plate numbers.


RTS1338 200701
RTS1338 200701
RTS1338 200701
ARE9296 200701
177J14R 200701
8172RRT 200702
7629PLU 200701
7629PLU 200701
177GHY2 200702


So the correct answer would be 4 cars, with 7 issues in 200701. So far, I have an array equation that finds the total unique values, but not with the additional criteria in months applied to it.

{=SUM(1/IF(A2:A14="",1,(COUNTIF(A2:A14,A2:A14)))-COUNTBLANK(A2:$A14))}

And keep in mind, my data set is ~50,000 rows - so I can't guess on what the license plate number will be. I have to have Excel see it and say, "OK - given this number 200701, I need to look at the cells next to these values and total the unique values that I find".


Any help would be greatly appreciated. I wish FREQUENCY would work, but it seems to dislike text.

 

[Stoneboysteve]

will.b
All you have to do is create a pivot table. Select your data, insert a pivot table. Use the months as the row labels and use the licence as the values. I used license in the row labels for additional breakdown. I did one from your sample in half a minute.

From your sample, my pivot table shows the number of services per car, total services by month, along with the unique cars per month. I attached a PDF.

You can also make a pivot report by double clicking on the data. Use care not to change data in the pivot report.

StoneboySteve

 

[will.b]

I'm sorry, I forgot to mention that. I must do it for every month, which does not seem so bad when faced with using a pivot table. However, the data set I am applying it to not only must take into account 12 months at a time(it's going in a self-generating graph, which is easy - 200701,200702, etc) but it is a function of other user inputs, which can be a very large number with varying results. In order for it to be time-efficient, i'm essentially attempting to mimic a pivot table sum, but without a user physically touching it every time. My "holy grail" is getting Excel to pivot for me, without using VBA. So I need Excel to see, "OK - you're looking for 200701, how many cars were worked on for Electrical Issues?" I was hoping that if I could at least get it to work sorting monthly, one additional criteria would be easy to add.


Thank you for your help!

 

[Stoneboysteve]

If you can create a pivot table each month in a few minutes it seems better than coding to things that can change each month.

Please post your success here so we can all benefit from your results.

Stoneboysteve

 

[Kevin@Radstock]

Hi will.b

Have a look at the attached file. Count Unique sheet tab is a formula approach and a pivot table on the other sheet. Would this be of any use!

Kevin

 

[will.b]

Thanks for your help everyone - still plugging away at it.

Stoneboysteve- The problem with my data set is, yes, it only changes once a month. However, the unique values that I will be counting must be represented in dozens of different areas. I currently do exactly as you say, by using pivot tables and manually selecting everything. I'm attempting to get it down with code, so that lots of charts and graphs will self-generate for all of these different areas. Also, the numbers going into the pivot tables require me to get into each pivot table and refresh, as well as manually thin out undesirables. The current way can also produce a substantial amount of errors due to human contact (I'm not the only one using it, so I would prefer to have the data not touched at all).

Kevin - i am unable to open your ZIP file while at work, I will check on it once I get home.

Also, if I do figure it out I'll make sure to let everyone know. I'm attempting to solve it so that it develops a method that can work for everyone, not just my exact instance