Filter and join values into one cell

[hpapagaj]

I would like to filter and combine returned cells into one cell in Excel.

Products sheet:

Screenshot 2022-12-03 at 00.13.41.png


I would like to have an Export sheet, where conditions:

1. Brand == Apple
2. Stock > 0
3. Product Name = [Product Name + " " + Color]

So result should looke like this:

Screenshot 2022-12-03 at 00.14.44.png

I tried several things, this was the closest to what I want to achieve:

Please Login or Register  to view this content.

...but I can't combine product name and color into one cell.

Any help is appreciated.

[sandy666]

is that what you want?

with Power Query

SKU	Product Name	Brand	Stock
CellPI	iPhone 14 Red	Apple	1
CellP3	iPhone 12 Gold	Apple	1
CellP5	iPhone 16 Green	Apple	0

Please Login or Register  to view this content.

[HansDouwe]

With a formula:

Please try:

Please Login or Register  to view this content.

[hpapagaj]

Thank you very much. I would like to have compatibility with Google Sheets, but HSTACK is not available in that. Can we replace this function with something else? Maybe CHOOSE function?

I tried:

Please Login or Register  to view this content.

, but I get #ERROR.

[Glenn Kennedy]

Why did you not mention GoogleSheets in your FIRST post?

[Glenn Kennedy]

=FILTER(CHOOSE({1,2},Products!B2:B6&" "&Products!C2:C6,Products!E2:E6),Products!E2:E6*(Products!D2:D6="Apple"))

works for me (in Excel).

[sandy666]

google sheet? I wasted my time

[Haluk]

For "Google Sheets", use this;

Please Login or Register  to view this content.

Or;

Please Login or Register  to view this content.