Card Number
XXXX5512
XXXX5512
XXXX4211
XXXX4510
XXXX6134
XXXX9035
XXXX6002
XXXX8653
XXXX7826
XXXX8819
XXXX8315
XXXX0274
I'm looking for a formula to generate the number of unique card numbers are in the column...I'm using this daily, simply pasting the new data in so if I want to keep it as automated as possible. Thanks for your help!
Dave thanks for the quick reply.
I want to be able to automate this so I want it to do all values in the row starting at Q24 no matter how many there are in the column. Some days, like yesterday there were 333, today there's 450. Is there a way to only count cells with a value in it...I tried to just do a safe range to 1000 and it's giving me #div/0! error.
Thanks a ton for your help!
You can change references to A2:A13 to A2:Index(A:A,COUNT(A:A)+1)
Not sure why you're getting a DIV/0 error, unless you didn't specify a range with data in the denominator of Dave's function (the COUNTIF part).
If I put data into Q24:Q60, and use the following formula it still works, even though 900+ rows are blank:
=SUMPRODUCT((Q24:Q1000<>"")/COUNTIF(Q24:Q1000,Q24:Q1000&""))
Here is another formula to try:
This is an ARRAY formula so press Ctrl+Shift+EnterCode:=SUM(IF(FREQUENCY(IF(LEN(A2:A1000)>0,MATCH(A2:A1000,A2:A1000,0),""),IF(LEN(A2:A1000)>0,MATCH(A2:A1000,A2:A1000,0),""))>0,1))
Dave, Paul, why the coercion of the countif range into text? It seems to work just as well without it on either all text, all numeric or mixed values.
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
Guys that worked perfectly....thanks so much!
How about this one, this one's challenging....is there a way to find how many unique people there are...ie match first and last name in (column y, z in this example) and see how many unique people there are.
I have faith you guys can somehow figure this one out, thanks in advanced!
A helper column with =Y1&Z1, then apply the same formula.
Did you try it with blanks...Originally Posted by teylyn
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Given your use of XL2008 you can use COUNTIFS rather than COUNTIF in the SUMPRODUCT
Pre XL2007 you would be best served using helpers as outlined else you would need to conduct a MATCH test to use the SUMPRODUCT approach else FREQUENCY Array.Code:=SUMPRODUCT(--(Y24:Y1000&Z24:Z1000<>""),1/COUNTIFS(Y24:Y1000,Y24:Y1000&"",Z24:Z1000,Z24:Z1000&""))
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I actually tried that, it gave me the same #div/0! error. Little more mechanical, the =Y1&Z1, worked fine though.
Is there a way to return a list of all the unique values in a column?
Advanced filter can give you that.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks