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
Posted by: Paul Vermeulen <paul.vermeulen@vulcantech.com.au>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
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
----------------------------------
Tidak ada komentar:
Posting Komentar