Split content on new sheet and extract info in parentheses

[tsalaki]

Hi all,

I have a table on one sheet which contains a comment column with info similar to the following:

where * number = quantity and ** string = product description

and I would like to copy that info to another sheet where it will be split in different rows like:

How can I do that using a formula? Keep in mind that each string - number separated by a comma on the comment column.

Thanks in advance
Regards

 

[DanteAmor]

Thanks for your reply. Pivot table works but all the total quantities are zeros. Looks like the column that created with the formula with the quantity per product it is not recognized as a number in order for the sum function to work properly, or something like that

Try the following formula to convert the second column to numeric values, then use the pivot table:

=LET(a,TEXTSPLIT(TEXTJOIN(",",,SUBSTITUTE(Sheet1!A2:A5,")","(")),"(",",",1),VSTACK(CHOOSECOLS(a,4),CHOOSECOLS(a,2)+0))