Search code examples
google-sheets

Google Sheet Concatenate + Filter

I have a data table in Google Sheet and I wanted to create a formula to display a concatenated list of specific items from the table in 1 cell only. My spreadsheet sample shows the formula I am using in cell F1.

I have been struggling to figure out a way to turn this formula into a simple list of all available items in this fashion:

U-001 Tiger Claw Plastic Toy x 10
U-005 Zombie Barbie x 35

I wanted 1 cell to display the result SKU + " " + Description + " x " + Qty. I tried using Concatenate as shown in F10 but obviously this does not work. Is there a way to modify a filtered data to display differently as above?

Solution

  • You can use BYROW in your filtered list, and concatenate the values as desired. INDEX is used to access the three columns of each row (you can also use CHOOSECOLUMNS):

    =BYROW(FILTER($B:$D,SEARCH("Available",$A:$A)),LAMBDA(each,
INDEX(each,,1)&" "&INDEX(each,,2)&" x "&INDEX(each,,3)))

    enter image description here

    Other option with ARRAYFORMULA, simpler in its expression but it concatenates before filtering (it may be heavier if the table has too many rows):

    =FILTER($B:$B&" "&$C:$C&" x "&$D:$D,SEARCH("Available",$A:$A))

    enter image description here