Try this array formula** :

Data in the range A2:B15
Assuming no empty cells in column B

D2:Dn = 1001, 1003, 1004 etc

Array entered** in E2 and copied down as needed:

=SUM(IF(FREQUENCY(IF(A$2:A$15=D2,B$2:B$15),B$2:B$1 5),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.

--
Biff
Microsoft Excel MVP


"Juan Marin" wrote in message
...
Hello everyone,

I have a large set of data that has in one column, the company id
which repeats on consecutive rows for each year in the dataset. Then
in the next column, there's the ID of each segment within the company
(a number). I need to count for each company code, how many segments
(unique numbers) are there. The data for a small sample looks like
this:

COMP-ID SEG-ID
1001 1
1001 1
1001 1
1003 1
1003 1
1003 1
1003 1
1004 3
1004 4
1004 5
1004 3
1004 4
1004 5
1004 3

in this case I would need to generate a list like this

1001 1
1003 1
1004 3

which means that company 1001 has 1 segment, 1003 has 1 segment and
company 1004 has 3 segments (segments 3, 4 and 5).

I hope anybody can shed some light on how to tackle this one because I
haven't been able to figure something out. Thanks in advance,

Juan Marin