Hello,

I'm tackling with a peculiar problem in Excel that my somewhat limited knowledge of formulas is unable to solve (if it can be solved at all using formulas without VBA). I'll be referring to cell names from my example spreadsheet (Sorting.xlsx) during the description of my problem.

Note: Cells with "..." in them mean they should be expandable in the future with automated recomputing.

The basic of the spreadsheet is a two-column category. The left column contains name of the category entry and the right column contains the value of the entry. See columns F-M in my spreadsheet. Each category has values that go as "Category 1-6" then followed by "Category ML1-Indeterminate". Within each category, each numbered entry is always higher than the previous one and each corresponding number of a category is higher than the same number from previous category (ex. CatA 2 > CatA 1 and CatB 1 > CatA 1). However, the diagonal relationship between categories is irregular because each category grows at a different rate.

Now, what I'd like to do, somehow, is to have a column somewhere (on the left most preferably so I can expand the categories to the right) that would compare all the values from all the categories and would write out the names in ascending order based on the name value. The table should be expandable both to the bottom (adding more numbers to categories) and to the right (adding more categories) and the list should reflect the changes (Having to recompute formulas every time I update the tables is not a problem).

I tried using some forms of VLOOKUP combined with MIN and comparing the previous entry in the list but nothing worked.

Furthermore, it would be nice to have the category name in the list be marked green or similarly when the confirm cell (column B) next to it contains "x". However, that is easily done using conditional formatting which can then be expanded. The somewhat bigger issues is column D which is my "help column" that I use to source the names of values after the initial 6, because the ML values raise indefinitely. I don't know if it's possible to use a formula for the naming that avoids the help column, perhaps by incrementing the name of the previous column somehow but I was unable to do that as well. However, this is just a minor cosmetic problem, so it's not really important.

If somebody could help me put together some kinda expandable formula that would work as I need, that would be amazing. It's probably easier to do in VBA programming but I have no experience with it at all so I don't know.

Thank you for any help or advice.