EXCEL FEED: Re: [ExcelVBA] Excel Vlookup, If, Large???

Jumat, 16 Januari 2015

Re: [ExcelVBA] Excel Vlookup, If, Large???

Thank to all of you. I will try these suggestions and let you know how it works.

Sharron

From: "Dr John C Bullas john.bullas@gmail.com [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: ExcelVBA@yahoogroups.com
Sent: Friday, January 16, 2015 4:55 AM
Subject: Re: [ExcelVBA] Excel Vlookup, If, Large???

can you use RANK() to filter the data if you have a rating for each of the vehicles RANK will rate the result values

On 16 January 2015 at 04:59, Monique Conley mconley1105@yahoo.com [ExcelVBA] <ExcelVBA@yahoogroups.com> wrote:

I would do a Vlookup in sheet1 that pulls in the scores from sheet2 and then I would concontanate the class and score fields, then sort and group by the concomtanated field.

Sent from Yahoo Mail for iPhone

At Jan 15, 2015, 9:54:23 PM, Paul Vermeulen paul.vermeulen@vulcantech.com.au [ExcelVBA]<'ExcelVBA@yahoogroups.com'> wrote:

Sharron

I would create an intermediate step by combining the two tables into one table on Sheet 2. After your total column create a class column, and use =vlookup(ID, Sheet 1 Data range, 4, False) to populate. You can now sort the expanded Sheet 2 by Total Score and then by Class.

To report the three top scores on sheet 3 you can now use a pivottable on the enlarged single data table, then after you have set up the pivottable, you can arrange the data part from largest to smallest; or you can loop through each ID (14 in total), find the first occurance in Sheet 2 (after sorting), report the three lines, next ID. Just remember to check that there are indeed three or more from each class of vehicle.

Hope this helps, and keep us informed of your progress.

Paul

From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: Friday, 16 January 2015 7:40 AM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Excel Vlookup, If, Large???

I have a workbook with three sheets respectively.

Sheet 1:

ID Name Vehicle Class

Sheet 2

ID Score A Score B Score C Score D Score E Total

On Sheet 3, I would like to show the top 3 scores in each Class (WS1) and the ID for each of the Top 3.

There are 14 classes (numeric 1 - 14), but 350 IDs with their scores Totaled (WS2).

I'm lost and I know there has to be a simple solution. I tried a Pivot Table, but that wasn't too helpful.

Any help is appreciated.

Sharron

--

===========================
http://uk.linkedin.com/in/drjohnbullas

__._,_.___

Posted by: Sharron Puryear <sheizageek@yahoo.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group