StarliteLemming
New Member
- Joined
- Jun 2, 2022
- Messages
- 12
- Office Version
- 2019
- Platform
- Windows
I have a table of data that I can't sort (because it's the merge of a Query with static data, and sorting messes up the references to the query -- a whole other issue!).
I want to know which record is alphabetically first out of a subset of the table, determined by certain conditions.
Currently, I'm using a rather clever solution I found online, but there is a noticeable calculation overhead because every item in the list has to be assigned a rank. So the calculation is n^2, and n is currently about 350.
To explain: the FIND() is required to generate an error if column P doesn't meet the criteria. The COUNTIFS() conditions are: 1) only rank records that have the same value in column N as the current record; 2) only rank records where the column heading, S1, appears in the text in column P; and 3) count how many records in this set are alphabetically less than the current record, in column B (the sort key). Because I only care about the first record in each set, I set TRUE if the rank is 1 (first) and FALSE otherwise.
As I said, the rank gets calculated for every record in the table, and that produces a noticeable delay from Excel. While the values in column N are all between 0 and 9, I need to actually determine the first record for those ten values for multiple (maybe a dozen) text values S1. In some cases, the results will be the same, but in others they won't. Regardless, I don't need the rank of every record to be calculated for each header value.
So, how do I do it?
If the sort were numeric, it would be easy (just use MINIFS()). Because it's an alphabetic sort (and I can't re-sort the data itself), I need to use functions that will compare alphabetic values.
My guess is that an array formula may be needed, but I've never truly understood how those work -- especially because there's at least two different types.
So, how do I determine the alphabetically-first record that meets two other criteria from other columns of the table?
I want to know which record is alphabetically first out of a subset of the table, determined by certain conditions.
Currently, I'm using a rather clever solution I found online, but there is a noticeable calculation overhead because every item in the list has to be assigned a rank. So the calculation is n^2, and n is currently about 350.
Excel Formula:
=IFERROR(FIND(S$1, $P2)*0 + COUNTIFS($N:$N, $N2, $P:$P, "*" & S$1 & "*", $B:$B, "<=" & $B2) = 1, FALSE)To explain: the FIND() is required to generate an error if column P doesn't meet the criteria. The COUNTIFS() conditions are: 1) only rank records that have the same value in column N as the current record; 2) only rank records where the column heading, S1, appears in the text in column P; and 3) count how many records in this set are alphabetically less than the current record, in column B (the sort key). Because I only care about the first record in each set, I set TRUE if the rank is 1 (first) and FALSE otherwise.
As I said, the rank gets calculated for every record in the table, and that produces a noticeable delay from Excel. While the values in column N are all between 0 and 9, I need to actually determine the first record for those ten values for multiple (maybe a dozen) text values S1. In some cases, the results will be the same, but in others they won't. Regardless, I don't need the rank of every record to be calculated for each header value.
So, how do I do it?
If the sort were numeric, it would be easy (just use MINIFS()). Because it's an alphabetic sort (and I can't re-sort the data itself), I need to use functions that will compare alphabetic values.
My guess is that an array formula may be needed, but I've never truly understood how those work -- especially because there's at least two different types.
So, how do I determine the alphabetically-first record that meets two other criteria from other columns of the table?
