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 ...
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&"|"&...
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)}
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+"),...
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 ...
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 ...
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 ...
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 ...
Top 50 recent answers are included
Related Tags
google-sheets-arrayformula × 732google-sheets × 717
formulas × 472
google-sheets-query × 237
vlookup × 96
google-sheets-dates × 61
regex × 60
concatenate × 43
google-sheets-arrays × 28
google-apps-script × 25
regexextract × 25
importrange × 20
filter × 19
conditional-formatting × 16
google-forms × 15
google-sheets-charts × 11
sorting × 10
google-sheets-timestamp × 8
links × 7
google-finance × 7
google-sheets-custom-function × 6
google × 5
text-formatting × 5
google-docs × 4
google-sheets-filter × 4