In my app (a social app where users create posts), I have a scorecard tab that shows various usage metrics for all the users. I’d like to include a metric indicating the time of last activity, by user. Ordinarily, I’d simply filter a timestamp field by user and find the max value. I cannot figure out a way to make this work with an ARRAYFORMULA. Any tips?
Here are the current formula and error message:
=arrayformula(max(filter([timestamp array in database sheet],[username array in database sheet]=[array of names in scorecard sheet])))
**Error** FILTER has mismatched range sizes. Expected row count: 46. column count: 1. Actual row count: 54, column count: 1.
The following formula gives the correct result for a single cell:
=max(filter([timestamp array in database sheet],[username array in database sheet]=[single name in scorecard sheet]))
This seems like it would be a common thing to do, so I hope there’s an easy solution that’s just in my blindspot. Apologies if the solution has been posted previously; I looked but couldn’t find it.
I like using a query to select a Max date, but I also haven’t been able to figure out how to do queries with an arrayformula, but you can build a new sheet with a query that selects user and max date grouped by user. You can then perform a VLOOKUP to look for that user in the new table. VLOOKUP is compatible with arrayformulas.
I’ve never been able to put a query inside an array formula. Well, I can but it will only work for the first row. How do you set it up if you have 300 rows and you want to do a query using data, in the WHERE cause, from that row? I have to manually drag the formula down.
I might have misunderstood the issue. Do you want to have multiple query answers in the same column? In my suggestion you can only get one query answer in the column where you place the query.
In my case I’m trying to run a query for each row since the WHERE will be different depending on other values in that row. Right now I have to manually place that formula in each row. I would like to use an arrayformula to autopopulate the formula into each row. That way if a new row is added, then the formula doesn’t have to be manually added. My query is designed to always return one result. I can understand there being problems if I was trying to return multiple results, but that’s not the case. I feel that arrayformula will not work with any function that has the potential to return multiple rows of data. I’m pretty sure that query, filter, and a handful of other functions are not compatible with arrayformula. That’s why I’m doing the 2 sheet method with a VLOOKUP that I described further up this thread. Scripting would be an option, but for simplicity, it would be nice to pull it off with formulas instead. I have run into daily processing limits with my scripts, so I try to avoid them when possible.
Since my original post, I have an additional challenge/wrinkle. Each post is in one of three categories that is recorded in another column. Is there a way to find the last post by user and category?
