1

Please let me know if it is possible for Google Sheets to sort data separated by "|" and then sum all unique values counting with multiplier. The multiplier is specified with "#". I am sending an example photo of the input data and what I would like to receive. enter image description here

I can separate the values ​​using the "SPLIT" function, but I still have the names with the multiplier in front.

enter image description here

user11222393
  • 3,245
  • 3
  • 13
  • 23
Peter
  • 13
  • 3
  • I think your question needs a bit of clarification... I'm struggling to wrap my head around exactly what you're trying to do. REGEXEXTRACT will likely be useful here. – Phil Anderson May 18 '23 at 13:53

1 Answers1

2

I am sure someone will give you prettier solution but:

=let(ars,arrayformula(split(tocol((SPLIT(A1:A3,"|")),3),"#")),byrow(unique(choosecols(ars,2)),lambda(uq,HSTACK(sumproduct((uq=choosecols(ars,2))*choosecols(ars,1)),uq))))

Result:

enter image description here

user11222393
  • 3,245
  • 3
  • 13
  • 23