CODEWITHSUNDEEP
google-sheets-formula
J. SungHoon
J. SungHoon

Reputation: 201

Filter and Array formula

enter image description here

I want to sort my data in order of the values of the members, but the groups should not be scattered.

For this, I wrote a formula to create a group min value. However, you must populate the formula whenever groups and members are added. I first tried = ARRAYFORMULA(MIN(FILTER(I34:I41, G34:G41 = G34:G41))), but it seemed that FILTER didn't work with ARRAYFORMULA.

I hope that the value of the remaining cells will be automatically updated with the formula of one cell. Is there a way to achieve this?
(First of all, I'm trying to figure out how to use formulas without apps scripts.)

Upvotes: 0

Views: 298

Answers (1)

Krzysztof Dołęgowski
Krzysztof Dołęgowski

Reputation: 2660

Filter function does not work with arrayformula Min function does not either. If you put an array inside Min function it returns the single smallest value.

But I have a solution:

=ArrayFormula(vlookup(B4:B11, query(B3:D,"select B, min(D) where B is not null group by B label B '', min(D) ''",1),2,0))

enter image description here

First you build a table with minimum values using query function.

It returns:

1 | 1

2 | 10

3 | 4

Then you use vlookup formula to get a second column of freshly made table and you go with this using column B as a key.

Here is my solution in a spreadsheet: https://docs.google.com/spreadsheets/d/1VoM6ZRV0O3NUiqggGqN49kdDuBinfo6hQDiF8uNhKKY/copy

Upvotes: 1

Related Questions