Hello all. I've tried multiple suggested guides to resolve this issue, but nothing seems to work.
=INDEX(QSUMS[logon],MATCH(LARGE(QSUMS[ANNUAL]),C6),QSUMS[ANNUAL] ,0))
I'm trying to summarize Corporate Credit Card usage for a large international company for management. I've created a tab for top/bottom users, that can be adjusted by changing field C6 (1-15) and the formula above checks the top/bottom number (C6) against another table (QSUMS) which has the user's employee number ("logon") and expenditures for each month, quarter, and annually already filled out in columns.
Large(qsums[annual],c6) is actually an indirect that allows management to change the search criteria (they can chose a month, a quarter, or 'annual' to show the top- and bottom- spenders from a specific time frame).
This works fine, until it hits several users with identical total costs (both in top/LARGE and bottom/SMALL) at which point it keeps pulling back the same logon number. Other columns in the top/bottom page pullback (via a vlookup to a fully employee list) the user's position in the company, their name, and department. But because duplicate cost values end up pulling back the first Logon value each time, the spreadsheet fails to differentiate duplicate expenses; attributing them all to the first user with that value. I've tried using multiple guides and how-to videos and documents to avoid duplicates, but none of the suggested fixes work, usually resulting in a broken formula.
I have Frankenstein'd together many suggested excel formulas and VB Scripts in the past to make a functioning spreadsheet, but this has resisted several weeks of attempted fixed. Any suggestions to resolve it would be appreciated. Thanks in advance.
Bookmarks