distinct lists

[dlythgoe]

Hi all,

I have a large list of names with many duplicates. Alongside these names in another column I have a revenue figure.

How do I first output in a separate list, the top 25 of those names distinctly ordered by rank of the sum of the revenue figures?

Many thanks!!

[sweep]

Hi,

have you tried a pivot table?

[dlythgoe]

Hi,

No - I won't be using a pivot table because this is more complex in the grand scheme of everything I'm doing due to presentation etc....

I can easily filter on the table using tools like this but I'm in a different situation.

So need to be able to return a distinct list of values off the back of a list of duplicates to start with!

Cheers

[dlythgoe]

Figured it out!

You can connect to your own data table as a data connection via ODBC.

Then run SELECT DISTINCT sql on it :D

Being able to run MS SQL on your data tables is immense power that has been around for ages!

[Kenneth Hobson]

If you don't mind, please post your code.

I have used that method using ADO but not as a data query. It is usually pretty fast.

Here is an example where I filled some listbox controls on a sheet.

Please Login or Register  to view this content.

Here is a Filter/Sort method. I have used this method to do my scratch work in a hidden sheet.

Please Login or Register  to view this content.

Another method is to use a Collection or Dictionary to get unique values and then sort.

Here is an array method.

Please Login or Register  to view this content.