Search code examples
google-sheets-formula

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

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?

Solution

  • 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))))))))