Finding Top 5 String (Text) Values in Excel 2007

[Spenello]

Using Excel 2007. Spreadsheet is created from SQL script run in TOAD as an Excel Instance. Have existing spreadsheet with drug names. Need to find the top 5 drugs used from column. Have tried MATCH, COUNTIF, VLOOKUP, MODE and others.

The problem is that there are thousands of drug names and every time the sprewadsheet is created, the names will vary. So, hard coding formulas is not possible.

Excel seems to work very well with numbers but not text. Any ideas for a formula is appreciated.

 

[Andrzejek]

Spreadsheet is created from SQL script run in TOAD as an Excel Instance"

I would modify/create new SQL to give me the TOP 5 reords needed.

Have fun.

---- Andy

 

[Spenello]

That would probably be a good workaround except user wants to see all results.

 

[SkipVought]

Hi,

Alternatively, use a PivotTable to use the Top10 feature.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Skip would use Pivot Tables in Excel because he knows Excel and its features. I would do it in SQL because I feel comfortable doing it that way. You can have 2 worksheets in Excel: one with all data, and one with top 5 records.

Two different ways to skin the cat.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

The nice thing about using a PivotTable or some other teshnique in Excel, is that you still have ALL the data to work with for reporting and analysis AND you or your users can easily change the TOP X criteria on the fly, which might be a wee more involved via a query.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Spreadsheet is created from SQL script run in TOAD

So here's a workbook that is created periodically.

Assumedly, you analyse this data on a regular basis. If it were me, I'd want to have a workbook that belongs to me or my organization that has all my analysis tools, my reporting tabs, my formatting etc. In order to accomplish that, I would IMPORT the data, either from the "Spreadsheet is created from SQL script run in TOAD" or (and this is would be my objective) grab the data via a query that the TOAD script gets. But you'd have to have access to those data source(s) I happen to be able to get to a lot of my company's data sources.

But either way, I'd run this from MY WORKBOOK!!!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}