Count unique occurrences

[quick_question]

I'm trying to count the number of times a unique number appears in column A.

I would like to be able reference a specific cell with a Material #, and count the number of different Vendor Numbers that appear.

The cell that I am referencing as the criteria is on a different worksheet as the data that I am sifting through.

There are more than 500 rows of data, if that matters.

example:

C2 = Material #
Worksheet 1

A B C
Vendor Number Vendor Name Material #
1 Blue 1
2 Green 2
3 Red 3
1 Blue 4
etc.
Worksheet 2


As a separate question, I need to count the number of materials that coincide with a particular Vendor #.

Thank you for your help.

 

[jeffreybrown]

Hello & Welcome to the Board,

Take a look at a pivot table which can easily summarize all of your data.

http://www.pivot-table.com/

 

[quick_question]

Not that I'm not a fan of pivot tables...but I was looking for a formula if possible.

This is part of a report, so I'd like to keep it as clean as possible.

Thanks again for your help.

 

[crook_101]

Hi,

Could you post some of your data as cannot really tell/work out what you are after.....

Pivot tables could be the answer if you then use =GETPIVOTDATA(...... just a thought for you, but if you after a formulaic result, please explain a bit more

Ian

 

[quick_question]

Unfortunately not...It's proprietary information, but the layout is as follows:


Columns:
A(Vendor #), B (Vendor Name), C (Material #)

Rows of data respective to the column titles.

A--------B--------C

V #-----VN-------M#

1--------w--------9
2--------x--------8
3--------y--------7
4--------z--------9
1--------t--------5



Sorry, I tried to space the columns out, I haven't figured out how to enter the information in with spacing as if in different columns.

I'm trying to use the best formula that will count the number of vendors that correspond to a Material #.

So with this example, the formula should return 2 different vendors for material # 9.

I would also like to be able to do the reverse; use a formula that would count the number of different Material #'s that correspond to a particular Vendor #.

With this example there would return 2 different Material #'s for Vendor # 1.

All I need is the count.

Hopefully this is more clear...and I'd prefer not to use a pivot table if possible.

Thank you again.

 

[T. Valko]

Unfortunately not...It's proprietary information, but the layout is as follows:


Columns:
A(Vendor #), B (Vendor Name), C (Material #)

Rows of data respective to the column titles.

A--------B--------C

V #-----VN-------M#

1--------w--------9
2--------x--------8
3--------y--------7
4--------z--------9
1--------t--------5



Sorry, I tried to space the columns out, I haven't figured out how to enter the information in with spacing as if in different columns.

I'm trying to use the best formula that will count the number of vendors that correspond to a Material #.

So with this example, the formula should return 2 different vendors for material # 9.

I would also like to be able to do the reverse; use a formula that would count the number of different Material #'s that correspond to a particular Vendor #.

With this example there would return 2 different Material #'s for Vendor # 1.

All I need is the count.

Hopefully this is more clear...and I'd prefer not to use a pivot table if possible.

Thank you again.

Are the V#s and the Mat#s really numbers? Do any start with leading 0s?

 

[Aladin Akyurek]

D2: 9

E2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$11<>"",IF($C$2:$C$11=D2,
   MATCH("~"&$A$2:$A$11,$A$2:$A$11&"",0))),
    ROW($A$2:$A$11)-ROW($A$2)+1),1))

will yield a distinct count of vendor id's who sell material with id = 9.

D3: 1

E3, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($C$2:$C$11<>"",IF($A$2:$A$11=D3,
    MATCH("~"&$C$2:$C$11,$C$2:$C$11&"",0))),
     ROW($C$2:$C$11)-ROW($C$2)+1),1))

will yield a distinct count of material id's sold by a vendor.

 

[quick_question]

Yes they are both real numbers, none start with 0 and range from 5 to 8 digits each.

 

[T. Valko]

Yes they are both real numbers, none start with 0 and range from 5 to 8 digits each.

OK, try these array formulas**.

Count of unique vendor numbers that correspond to a specific mat number...

E2 = some mat number like 9

Array entered**:

=SUM(IF(FREQUENCY(IF(C2:C6=E2,A2:A6),A2:A6),1))

Count of unique mat numbers that correspond to a specific vendor number...

E3 = some vendor number like 1

Array entered**:

=SUM(IF(FREQUENCY(IF(A2:A6=E3,C2:C6),C2:C6),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[quick_question]

Thank you, that's perfect.

Thank you to everyone who responded, tremendous help and quick responses...could not have asked for more.

Thank you again.