I wanted a ArrayFormula at C1 which gives the required result as shown.
Entry sheet:
(Column C is my required column)
Date Entered is the date when the Name is Assigned a group i.e. a, b, c, d, e, f
Criteria:
Example:
After I sort the data in any random order say like this:
Random ordered sheet:
(Count value remains permanent)
And when I do New entries in between (Row 4th & 14th) and after last row (Row 17th):
Random Ordered sheet:
(Doesn't matter where I do)
={"AF Formula1"; ArrayFormula(IF(B2:B="", "", COUNTIFS(B$2:B, "="&B2:B, D$2:D, <"&D2:D)+1))}
I tried to figure my own ArrayFormula but it's not working:
I got Formula for each cell:
=RANK($D2,FILTER($D$2:$D, $B$2:$B=$B2),1)
I figured out Filter doesn't work with ArrayFormula so I had to take a different approach.
I took help from my previous question answer (Arrayformula at H3) which was similar since in both cases each cell FILTER formula returns more than 1 value. (It was actually answered by player0)
Using the same technique I came up with this Formula which works absolutely fine :
=RANK($D2, ARRAYFORMULA(TRANSPOSE(SPLIT(VLOOKUP($B2, SUBSTITUTE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({$B:$B&"×", $D:$D}, "SELECT MAX(Col2) WHERE Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1", 1),, 9^9)), "×")), " ", ","), 2, 0), ","))), 1)
Now when I tried converting it to ArrayFormula: ($D2 to $D2:$D & $B2 to $B2:$B)
=ARRAYFORMULA(RANK($D2:$D,TRANSPOSE(SPLIT(VLOOKUP($B2:$B, SUBSTITUTE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({$B:$B&"×", $D:$D}, "SELECT MAX(Col2) WHERE Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1", 1),, 9^9)), "×")), " ", ","), 2, 0), ",")), 1))
It gives me an error "Did not find value '' in VLOOKUP evaluation", I figured out that the problem is only in VLOOKUP when I change $B2 to $B2:$B.
I'm sure VLOOKUP works with ArrayFormula, I fail to understand where my formula is going wrong! Please help me correct my ArrayFormula.
if I understand correctly, you are trying to "rank" B column based on D column dates in such way that dates are in theoretical ascending order so if you randomize your dataset, the "rank" of each entry would stay same and not change based on the randomness you introduce.
therefore the correct formula would be:
={"fx"; INDEX(IFNA(VLOOKUP(B2:B&D2:D,
{INDEX(SORT({B2:B&D2:D, D2:D}, 2, 1),,1),
IFERROR(1/(1/COUNTIFS(
INDEX(SORT(B2:D, 3, 1),,1),
INDEX(SORT(B2:D, 3, 1),,1), ROW(B2:B), "<="&ROW(B2:B))))}, 2, 0)))}
{"fx"; ...}
array of 2 tables (header & actual table) under each other eg.;
outer shorter
INDEX
or longerARRAYFORMULA
(doesnt matter which one) is needed coz we are processing an array
IFNA
for removing possible#N/A
errors fromVLOOKUP
function whenVLOOKUP
fails to find a match
we
VLOOKUP
joint B and D columnB2:B&D2:D
in our virtual table{}
and returning second2
column if there is an exact match0
our virtual table
{INDEX(SORT({B2:B&D2:D, D2:D}, 2, 1),,1), ...}
weVLOOKUP
from is constructed with 2 columns next to each other eg.,
we are getting the first column by creating an array of 2 columns
{B2:B&D2:D, D2:D}
next to each other where weSORT
this array by date/2nd column2
, in ascending order1
but all we need after sorting is the 1st column so we useINDEX
where we bring all rows,,
and the first column1
now lets take a look on how we getting the 2nd column of our virtual table by using
COUNTIFS
which will mimic the "rank"
IFERROR(1/(1/
is used to remove all zero values from the output (all empty rows would have 0 in it as the "rank")
under
COUNTIFS
we put 2 pairs of arguments: "if column is qual to column" and "if row is larger or equal to next row increment it by 1"ROW(B2:B), "<="&ROW(B2:B))
for "if column is qual to column" we do this twice and use range
B2:D
and sort it by date/3rd column3
in ascending order1
and of this we again need only the 1st column so weINDEX
it and return all rows,,
and first column1
with this formula you can add, remove or randomize your dataset and you will always get the right value for each of your rows
as for why your formula doesnt work... to not get #N/A error for vlookup you would need to define the end row of the range but still, the result wont be as you would expect coz formula is not the right one for this job.
as mentioned there are functions that are not supported under AF like SUM
,AND
,OR
and then there are also functions which work but in a different way like IFS
or with some limitations like SPLIT
,GOOGLEFINANCE
,etc.