Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Top 5 Expense Items with Comments
I have a list of expenses in one tab of my workbook from an employee's
expense reports. I am trying to find the top 5 expenses for that person to be summarized on a summary tab. I have used the LARGE function to find the top 5 dollar amounts, but don't know how to pull the expense category, date and description into the summary. The detail could be in any order. Any thoughts or suggestions would be appreciated. Ron |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Top 5 Expense Items with Comments
Hopefully there are no "ties"
Use VLOOKUP: say the expenses are in column A with catagory, date, and description in B,C,D. =VLOOKUP(LARGE(A:A,1),A1:D100,2,FALSE) for col B =VLOOKUP(LARGE(A:A,1),A1:D100,3,FALSE) for col C =VLOOKUP(LARGE(A:A,1),A1:D100,4,FALSE) for col D then for the next largest: =VLOOKUP(LARGE(A:A,2),A1:D100,2,FALSE) for col B etc. -- Gary''s Student - gsnu200814 "ruffnro" wrote: I have a list of expenses in one tab of my workbook from an employee's expense reports. I am trying to find the top 5 expenses for that person to be summarized on a summary tab. I have used the LARGE function to find the top 5 dollar amounts, but don't know how to pull the expense category, date and description into the summary. The detail could be in any order. Any thoughts or suggestions would be appreciated. Ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Top 5 Expense Items with Comments
That is a great suggestion, the problem is that I can and do have ties. Then
this brings back the comments on the first expense item for all of the ties. Are there any other thoughts on how to do this. Thanks for your help "Gary"s Student"!! "Gary''s Student" wrote: Hopefully there are no "ties" Use VLOOKUP: say the expenses are in column A with catagory, date, and description in B,C,D. =VLOOKUP(LARGE(A:A,1),A1:D100,2,FALSE) for col B =VLOOKUP(LARGE(A:A,1),A1:D100,3,FALSE) for col C =VLOOKUP(LARGE(A:A,1),A1:D100,4,FALSE) for col D then for the next largest: =VLOOKUP(LARGE(A:A,2),A1:D100,2,FALSE) for col B etc. -- Gary''s Student - gsnu200814 "ruffnro" wrote: I have a list of expenses in one tab of my workbook from an employee's expense reports. I am trying to find the top 5 expenses for that person to be summarized on a summary tab. I have used the LARGE function to find the top 5 dollar amounts, but don't know how to pull the expense category, date and description into the summary. The detail could be in any order. Any thoughts or suggestions would be appreciated. Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding multiple items from a list | Excel Worksheet Functions | |||
Finding Repeated Items in Excel 2003 | Excel Discussion (Misc queries) | |||
Finding maximum qty of items to reach given limit | Excel Worksheet Functions | |||
how do I set up time sheet and items bought expense sheet | Excel Discussion (Misc queries) | |||
Finding unique items in data field for pivot tables | Excel Discussion (Misc queries) |