How to group and add similar items with a unit of measurement

Question

I have the following spreadsheets. I need to compact the contents of this spreadsheet. I was wondering if it is possible to group similar items such as 1952 and add the numbers in the next column and output something along 5m / 2w or similar? 1951 would be compacted to 9w. This data is constantly changing and new reference numbers are added often.

Answer 1

Sheets doesn't do well adding numbers when they're right next to letters, so you'll need to split those cells into a number column and letter column using left() and right(). Put the number from left() inside the value() function as well so Sheets knows it's a number.

Example sheet

Once you have the helper columns made, you can use query() to consolidate and sum up your values. Query language can get tricky so I recommend the reference page. Once you have the sums and letters spit out, you can concatenate them in another column (J for me).

Answer 2

=ARRAYFORMULA(SUBSTITUTE(TRIM(SPLIT(TRANSPOSE(QUERY(TRANSPOSE({
 SORT(UNIQUE(INDIRECT("B2:B"&COUNTA(B2:B)+1)&"♦")),IF(ISNUMBER(
 QUERY(QUERY(TO_TEXT(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(IFERROR({
 B2:B&"♦", REGEXEXTRACT(C2:C, "\d+")*1, REGEXEXTRACT(C2:C, "\d+(.+)")}), 
 "select Col1,sum(Col2),Col3 
  where Col3 is not null 
  group by Col1,Col3 
  label sum(Col2)''", 0)),,999^99)), "♦")), 
 "select count(Col1) 
  group by Col1 
  pivot Col2", 0), "offset 1", 0)), SUBSTITUTE(
 QUERY(QUERY(TO_TEXT(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(IFERROR({
 B2:B&"♦", REGEXEXTRACT(C2:C, "\d+")*1, REGEXEXTRACT(C2:C, "\d+(.+)")}), 
 "select Col1,sum(Col2),Col3 
  where Col3 is not null 
  group by Col1,Col3 
  label sum(Col2)''", 0)),,999^99)), "♦")), 
 "select count(Col1) 
  group by Col1 
  pivot Col2", 0), "limit 0", 1), " ", ), )}),,999^99)), "♦")), " ", " / "))

spreadsheet demo