# unique count help

1. ## unique count help

hi there:
I need help with these for my work. I have attached the file.
So for values based on 'column A' and 'column B', i need unique count in 'column C'.
I couldnt figure out a way for this. I can easily do it in pivot. But i need a formula for it.
So lets say for John he has 3 cause and 2 unique cause. I want 2 for John in 'column C' but only in one row for John. Not for all John's row.
In the attached file I have given an example, how I really want it!

I also have posted a possible solution. But that solution will post the value in every row instead of only the 1st row for 'column A' value

I really appreciate anyone helping me with this. Thanks in Advance.  Register To Reply

2. ## Re: unique count help

Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: unique count help

 A B C D E 1 Claimant cause Unique List unique cause 2 tom Unknown don 1 3 tom Minor John 2 4 tom High/CAT Impairment mike 2 5 John Unknown tom 3 6 John Major (hospitalization) 7 John Unknown 8 don Minor 9 don Minor 10 don Minor 11 mike Unknown 12 mike Minor

D2=IFERROR(INDEX(A\$2:A\$100,MATCH(0,INDEX(COUNTIF(A\$2:A\$100,"<"&A\$2:A\$100)-SUMPRODUCT(COUNTIF(\$D\$1:D1,A\$2:A\$100)),),0)),"")

copy down

E2=IF(\$D2<>"",SUM(IF(FREQUENCY(IF(\$A\$2:\$A\$100=\$D2,IF(\$B\$2:\$B\$100<>"",MATCH(\$B\$2:\$B\$12,\$B\$2:\$B\$100,0))),ROW(\$B\$2:\$B\$100)-ROW(\$B\$2)+1),1)),"")

Control+shift+enter

copy down  Register To Reply

4. ## Re: unique count help

BMV
Thank you so much. but i am getting formula error. is there something wrong it there?
carcalla
Thats really cool what you have done. But i dont want unique list. I still want all rows.  Register To Reply

5. ## Re: unique count help

Sorry, it's array formula
Ctrl+Shift+Enter in stand of Enter, and ";" could be replaced to ","  Register To Reply

6. ## Re: unique count help

C2=IF(COUNTIF(\$A\$2:\$A2,\$A2)=1,SUM(IF(FREQUENCY(IF(\$A\$2:\$A\$100=\$A2,IF(\$B\$2:\$B\$100<>"",MATCH(\$B\$2:\$B\$100,\$B\$2:\$B\$100,0))),ROW(\$B\$2:\$B\$100)-ROW(\$B\$2)+1),1)),"")

control+shift+enter
copy down  Register To Reply

7. ## Re: unique count help

superb BMV. thanks
Carcalla its putting the value in every row. BUt thank you so much for responding.  Register To Reply

8. ## Re: unique count help

I change my formula in post #6  Register To Reply

9. ## Re: unique count help

Results in columns G and H beginning in G2.

G2: =A2
H2: =COUNT(1/FREQUENCY(INDEX(MATCH(B\$2:B\$12,B\$2:B\$12,0)*(A\$2:A\$12=G2),0),ROW(A\$2:A\$12)-2))-1

G3: =INDEX(A\$2:A\$12,MATCH(0,COUNTIF(G\$2:G2,A\$2:A\$12),0))
H3: =COUNT(1/FREQUENCY(INDEX(MATCH(B\$2:B\$12,B\$2:B\$12,0)*(A\$2:A\$12=G3),0),ROW(A\$2:A\$12)-2))-1

These formulas shouldn't require array formula entry. They will return errors when the data has been exhausted.  Register To Reply

10. ## Re: unique count help

hi there
any way i can keep the 'column C' dynamic.
I mean if i add new value in column a and b, column c will be auto updated. i tried adding new value in column 13. but that made 'column c' going crazy for both BMv's and Carcalla's formula.
Is there a solution to that?  Register To Reply

11. ## Re: unique count help

If there's never be anything below the table beginning in A1,

G2: =A2
H2: =COUNT(1/FREQUENCY(MATCH(B\$2:INDEX(B:B,COUNTA(B:B)),B\$2:INDEX(B:B,COUNTA(B:B)),0)*(A\$2:INDEX(A:A,COUNTA(B:B))=G2),ROW(\$1:\$12)-1))-1

G3: =IFERROR(INDEX(A\$2:INDEX(A:A,COUNTA(B:B)),MATCH(0,COUNTIF(G\$2:G2,A\$2:INDEX(A:A,COUNTA(B:B))),0)),"")
H3: =IF(G3<>"",COUNT(1/FREQUENCY(MATCH(B\$2:INDEX(B:B,COUNTA(B:B)),B\$2:INDEX(B:B,COUNTA(B:B)),0)*(A\$2:INDEX(A:A,COUNTA(B:B))=G3),ROW(\$1:\$12)-1))-1)

Select G3:H3 and fill down as far as needed to accommodate expected future entries in cols A and B.  Register To Reply

12. ## Re: unique count help

C2=IF(COUNTIF(\$A\$2:\$A2,\$A2)=1,SUM(IF(FREQUENCY(IF(\$A\$2:\$A\$100=\$A2,IF(\$B\$2:\$B\$100<>"",MATCH(\$B\$2:\$B\$100,\$B\$2:\$B\$100,0))),ROW(\$B\$2:\$B\$100)-ROW(\$B\$2)+1),1)),"")

CONTROL+Shift+enter

copy down  Register To Reply

13. ## Re: unique count help

Good morning.
sanybhai, I have marked changing by red
Formula:  `Please Login or Register  to view this content.`  Register To Reply

14. ## Re: unique count help

Hey CARACALLA THanks.
Hey BMV thanks. when i copy and paste your formula i i get formula error. I am sure your formula is right. I am doing something wrong. Would you able to send me this in an excel file?  Register To Reply

15. ## Re: unique count help Originally Posted by sanybhai Hey CARACALLA THanks.
Hey BMV thanks. when i copy and paste your formula i i get formula error. I am sure your formula is right. I am doing something wrong.
Just replace the semi-colons (";") with commas (","), should read like this: ``Please Login or Register  to view this content.``
Tim   Register To Reply

16. ## Re: unique count help Originally Posted by harrisonland Just replace the semi-colons (";") with commas (","), should read like this:
Yes. It's regional settings problem. I hoped the changes I have marked by red could be done in the formula.  Register To Reply

17. ## Re: unique count help

Haha, I assumed it would be... Wouldn't make sense otherwise!

Tim  Register To Reply

18. ## Re: unique count help

guys any alternate to this problem beside array function. I have about 7000 rows and growing.
Array function is taking ever to calculate.
any other formula based solution! Thanks for all your help. learning a lot   Register To Reply

19. ## Re: unique count help

then helper column have to be created
=--(COUNTIFS(A\$1:A2,A2,B\$1:B2,B2)=1)
and
=IF(COUNTIF(A\$1:A2,A2)=1,SUMIF(\$A:\$A,A2,\$E:\$E),"")
where E- helper.  Register To Reply

20. ## Re: unique count help

Hey BMV
Can you attach it in a excel file plz  Register To Reply

21. ## Re: unique count help

Yes, I can. See previous post. And 2nd formula was corrected . There was 2 typos. ";" in stand of "," and \$A\$:\$A\$ - \$A:\$A.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 