Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

New answers tagged

3 votes
Accepted

Google Sheets - Group/Aggregate data

You can remove sizes from names with regexextract() in cell F1 like this: =arrayformula( iferror( regexextract(A1:A, "(.+?)-[\dA-Z]"), A1:A ) ) Then query the data: =query(A1:F, "select ...
user avatar
  • 6,039
1 vote

Creating new rows using arrayformula if a cell contains info

This is the formula that I wrote, based on your first question: =index(split(flatten("product|include",A2&"|"& Filter(B2:2,B2:2<>""),A3&"|"&...
user avatar
  • 743
0 votes

Sorting multiple columns using formula

If I understand you correctly this formula should solve: ={SORT(A1:A,1,true),SORT(B1:B,1,true),SORT(C1:C,1,true),SORT(D1:D,1,true)}
user avatar
  • 743
1 vote
Accepted

Regexextract numbers from string

simply add a question mark, like this: =REGEXEXTRACT(A2,"\d+-?\d+") you could make an array for that formula with: =index(if(A2:A<>"",REGEXEXTRACT(A2:A,"\d+-?\d+"),...
user avatar
  • 743
2 votes
Accepted

Creating a dynamic 2d array

You can create a matrix like this: =arrayformula( if( sequence(1, max(A1:A)) <= A1:A, sequence(1, max(A1:A)), iferror(1/0) ) ) The formula uses the open-ended reference A1:A, and will thus process ...
user avatar
  • 6,039
0 votes
Accepted

How do I simplify this formula to be expandable?

=ARRAYFORMULA(IF(ISBLANK(B2:B),"",IF(B2:B >= $A$2,CEILING(B2:B/A2),1))) I added the function to your google sheet too. I couldn't work out a better logic so that it won't show a value if ...
user avatar
  • 375
4 votes
Accepted

Text input if cell > than 1

Use filter(), like this: =filter(A57:A66, B57:B66 > 1)
user avatar
  • 6,039
1 vote
Accepted

Match value of cell if in between range and text match

You can now check out the sheet: It is possible but you have to make some changes to the data placement for it to work. Also the number should not have the string '£', but instead apply a number ...
user avatar
  • 136
0 votes
Accepted

Return matching text value in cell

You can use a FILTER with COUNTIF =UNIQUE( FILTER( B28:B, COUNTIF(B28:B,B28:B)>1)) If you wanted to also return the name, change the first range in the FILTER to A28:B Since it will return ...
user avatar
  • 31

Top 50 recent answers are included