New answers tagged google-sheets-arrayformula
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 ...
- 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&"|"&...
- 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)}
- 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+"),...
- 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 ...
- 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 ...
- 375
4
votes
Accepted
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 ...
- 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 ...
- 31
Top 50 recent answers are included