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