With this amount of data, perhaps you could use the
remove duplicates Excel 07 command or advanced filter for
unique values and then use a PivotTable or formula?

Otherwise try the Import Data command
(under Data Import External Data in XL02 menus)

Select Excel files, locate the current file and click Open.
Choose the Data range eg Sheet1 (or a named range) then OK.
Now Choose Edit Query, Command Type: SQL and Command Text:

SELECT [COMP-ID],COUNT(*) AS [Segs] FROM
(SELECT DISTINCT * FROM [Sheet1$]) GROUP BY [COMP-ID]

Click OK, select the output cell and click OK again.

For 65536 rows and around 10000 COMP-IDs this takes only a
second to run in tests, other solutions froze the application.


"Juan Marin" wrote:

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