2

I would like a formula that groups/aggregates the data of Column A, B, C and D if column A is the same string

The issue is the end of the string in column A with different sizes at the end of the string - I have highlighted the ones I would like grouped to hopefully make it clearer

https://docs.google.com/spreadsheets/d/1zsSQD20IYM57hQNMoHscVDKgl3J1IMxPTcUwSpDgvEE/edit?usp=sharing

Improve this question

1 Answer 1

Reset to default
4

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 F, sum(B), sum(C), avg(D) where F is not null group by F", 1)

See your sample spreadsheet.

Improve this answer
2
  • That's definitely along the right lines thanks - This was a simplified extract of the data I am working with - I have included a couple of exact strings which are more complicated below on the spreadsheet if you wouldn't mind having a look at those Essentially I would like all of the string apart from the data after the last ' - ' Thanks again
    – magelincs
    May 27, 2022 at 9:31
  • 1
    Please ask only one question per post. To learn how to adjust the regex, see regularexpressions.info and RE2.
    – doubleunary
    May 27, 2022 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.