Get the counting of something to get unique info

G

gmore

Hello,

Is it possible to obtain the number of unique occurences by some ways if I
have the information as follows on three columns:

No City Postal Code
1234 BRAMPTON GGG
3412 TORONTO HHH
3412 TORONTO HHH
1452 YORK UUU
5623 LONDON YYY

I would like to have two results that looks like this:
Number of suppliers for Postal Code HHH = 1 (not 2)
Number of suppliers = 4 (not 5)

Thanks,
gmore
 
B

Bob Phillips

Count all uniques

=SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&""))

All unique HHH

=COUNT(1/FREQUENCY(IF(C2:C20="HHH",IF(C2:C20<>"",A2:A20)),IF(C2:C20="HHH",IF(A2:A20<>"",A2:A20))))

which is an array formula, committed with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
G

gmore

Thanks Bob,

I forgot to mention that it is possible to have more than one city for a
given supplier. So, as example, 3412 should be counted as two entities in
this example:

No City Postal Code
1234 BRAMPTON GGG
3412 MONTREAL JJJ
3412 TORONTO HHH
3412 TORONTO HHH
1782 MISSISSAUGA HHH
1452 YORK UUU
5623 LONDON YYY

How would do you reajust your formulas for both results?
I would like to have two results that looks like this:
1. Number of suppliers with Postal Code HHH = 2 (not 3)
2. Number of suppliers = 6 (not 7)

Thanks, I appreciate your help!
gmore
 
R

Ron Coderre

Try something like this:

With
Your posted data structure in A1:a10

And
D1: (the postal code to match)

Then
This regular formula returns the count of unique "No" and "City"
combinations with that Postal Code:
E1:
=SUMPRODUCT(--(FREQUENCY((C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0),(C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0))*(C1:C11=D1)>0))

and this regular formula returns the count of unique "No" and "City"
combinations
F1:
=SUMPRODUCT(--(FREQUENCY(MATCH(A2:A10&B2:B10&"_",A2:A10&B2:B10&"_",0),MATCH(A2:A10&B2:B10&"_",A2:A10&B2:B10&"_",0))*(ROW(A1:A10)>1)>0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
G

gmore

Thanks! That's really cool!

Ron Coderre said:
Try something like this:

With
Your posted data structure in A1:a10

And
D1: (the postal code to match)

Then
This regular formula returns the count of unique "No" and "City"
combinations with that Postal Code:
E1:
=SUMPRODUCT(--(FREQUENCY((C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0),(C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0))*(C1:C11=D1)>0))

and this regular formula returns the count of unique "No" and "City"
combinations
F1:
=SUMPRODUCT(--(FREQUENCY(MATCH(A2:A10&B2:B10&"_",A2:A10&B2:B10&"_",0),MATCH(A2:A10&B2:B10&"_",A2:A10&B2:B10&"_",0))*(ROW(A1:A10)>1)>0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP
Click to expand...
 
K

KL

Ron Coderre said:
=SUMPRODUCT(--(FREQUENCY((C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0),(C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0))*(C1:C11=D1)>0))
Click to expand...

Hi Ron,

Why not just:

=SUMPRODUCT(--(FREQUENCY(MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0),MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0))*(C1:C11=D1)>0))

Regards,

KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36
 
R

Ron Coderre

Thank you so much for the feedback. It's much appreciated.....I'm just glad I
could help.

***********
Regards,
Ron

XL2002, WinXP
 
R

Ron Coderre

Thanks, KL

I was experimenting with various numbers/text/blanks scenarios and I didn't
fine tune my formula enough after I got it to work. Thanks for jumping in and
cleaning it up.

***********
Regards,
Ron

XL2002, WinXP


KL said:
Ron Coderre said:
=SUMPRODUCT(--(FREQUENCY((C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0),(C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0))*(C1:C11=D1)>0))
Click to expand...

Hi Ron,

Why not just:

=SUMPRODUCT(--(FREQUENCY(MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0),MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0))*(C1:C11=D1)>0))

Regards,

KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36
Click to expand...
 
D

Domenic

For the number of unique suppliers/city with postal code 'HHH', try the
following formula which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(C2:C8="HHH",MATCH(A2:A8&B2:B8,A2:A8&B2:B8,0)),ROW(C2
:C8)-ROW(C2)+1),1))

To exclude rows where Column C equals HHH and the corresponding values
in Column A and B are blank, try the following instead...

=SUM(IF(FREQUENCY(IF(C2:C8="HHH",IF(A2:A8&B2:B8<>"",MATCH(A2:A8&B2:B8,A2:
A8&B2:B8,0))),ROW(C2:C8)-ROW(C2)+1),1))

Hope this helps!
 
Top