200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I have attached a sample spreadsheet,first two set of tables(A:B) is the source, the middle tables(F:G) are actual results which is using Index-match functions to retieve sorted data(asc/desc) from source tables. The last set of tables are desired output which is my requirement.
Apparently Large/small functions are not working on duplicate values. Is there an alternative/effective formula to get the desired output.
Thanks.
Dr Liss
Would you consider using Power Query for this?
In the attached revision to your workbook, two PQE tables appear in columns K to M, both based on data in A1:B9 (now a table). For both the "AscendingRev" and "DescendingRev" tables, the data is sorted by revenue (descending) then by name (A>Z). I added an Index column after that, picking the "Start with 1" option (but renamed it to "SI No" like your desired result).
You could Hide column M but there's still the downside that you need to Refresh the tables (by clicking in a table then from the Table Tools/Query menu clicking Refresh, though that could be done with a simple macro on worksheeet change event).
(If you change the data to have more duplicates, you'll see the sort still works)
