Concatenate respective values ​from two columns, group them and add the repetition of their terms as a "multiple"

Question

In a spreadsheet I have the values ​​in column A and B and I want to reach the result in column D. In column C I have the formula below. This was the closest I could get to the desired result:

=BYROW(ARRAYFORMULA(JOIN(" | "; TRANSPOR(SPLIT(B2; " | ";0)) & " " & TRANSPOR(SPLIT(A2; " | "))));LAMBDA(mem;SE(mem="";;
LET(cleaned; SUBSTITUIR(mem;" GB";"GB");
splitted; SPLIT(cleaned;" | ");
uniq; UNIQUE(TOCOL(splitted));
counts; BYROW(uniq;LAMBDA(each;CONT.SE(splitted;each)&" x "&each));
TEXTJOIN(" + ";1;counts)))))

Below is a representation of the spreadsheet:

Interface HD	Capacidade HD	Result obtained with the above formula	Desired result
SATA | SATA/SSD | SATA | SATA	500 GB | 240 GB | 500 GB | 500 GB	3 x 500GB + 3 x SATA + 1 x 240GB + 1 x SATA/SSD	1 x 240GB SATA/SSD + 3 x 500GB SATA
SATA/SSD | SATA | SATA | SATA/SSD	256 GB | 500 GB | 500 GB | 256 GB	2 x 256GB + 2 x SATA/SSD + 2 x 500GB + 2 x SATA	2 x 256GB SATA/SSD + 2 x 500GB SATA

The spreadsheet with the result presented above is this:

Spreadsheet

What change would be necessary in the formula in column C to arrive at the result in column D?

Answer 1

You need a ;0 in splitted.

=BYROW(ARRAYFORMULA(JOIN(" | "; TRANSPOSE(SPLIT(B2; " | ";0)) & " " & TRANSPOSE(SPLIT(A2; " | "))));LAMBDA(mem;IF(mem="";;
LET(cleaned; SUBSTITUTE(mem;" GB";"GB");
splitted; SPLIT(cleaned;" | ";0);
uniq; UNIQUE(TOCOL(splitted));
counts; BYROW(uniq;LAMBDA(each;COUNTIF(splitted;each)&" x "&each));
TEXTJOIN(" + ";1;counts)))))

Here's another solution:

=ARRAYFORMULA(
  MAP(A2:A;B2:B;LAMBDA(int;cap;
    IFERROR(JOIN(" + ";BYROW(QUERY(TOCOL(SPLIT(cap;" | ";)&" "&SPLIT(int;" | ";));
       "select count(Col1),Col1 group by Col1 label count(Col1)''");LAMBDA(r;JOIN(" x ";r))))))))

Answer 2

2 changes to current version

• Change SPLIT(cleaned;" | ") to SPLIT(cleaned;" | ";0)

• Add sort() to second_internal byrow()

Final formula:

=BYROW(ARRAYFORMULA(JOIN(" | "; TRANSPOSE(SPLIT(B2; " | ";0)) & " " & TRANSPOSE(SPLIT(A2; " | "))));LAMBDA(mem;IF(mem="";;
LET(cleaned; SUBSTITUTE(mem;" GB";"GB");
splitted; SPLIT(cleaned;" | ";0);
uniq; UNIQUE(TOCOL(splitted));
counts; sort(BYROW(uniq;LAMBDA(each;COUNTIF(splitted;each)&" x "&each)));
TEXTJOIN(" + ";1;counts)))))