Getting values from one line if cells in another line are not empty

[cogumelo]

Hi guys,

I'm trying to build a concatenation for each line (group 1, group 2, group 3,...) based on the values each line has.

For example, for Group 1 line, if a cell value is not blank then i wish to concatenate the country and category for that column. So for Group the concatenation should be PT1100. For Group 2 line, it should be PT1101 + ES1100 + ES1101 and for Group 3 line it should be PT1100 + ES1101

Country -->​	PT	PT	ES	ES
Category -->​	1100 Cat 1	1101 Cat 2	1100 Cat 1	1101 Cat 2	Concatenation
Group 1	100		-1		PT1100
Group 2		20	20	23	PT1101 + ES1100 + ES1101
Group 3	30			21	PT1100 + ES1101
Group 4

Can it be done? Thx in advance for any help.

 

[HongRu]

Try.
F3
=TEXTJOIN("+",TRUE,IF(B3:E3<>"",B$1:E$1&LEFT(B$2:E$2,4),""))
Press CTRL+SHIFT+ENTER to enter array formulas.
Copy F3 downward.

book1
	A	B	C	D	E	F
1	Country -->	PT	PT	ES	ES
2	Category -->	1100 Cat 1	1101 Cat 2	1100 Cat 1	1101 Cat 2	Concatenation
3	Group 1	100				PT1100
4	Group 2		20	20	23	PT1101+ES1100+ES1101
5	Group 3	30			21	PT1100+ES1101
sheet1

Cell Formulas
Range	Formula
F3:F5	F3	=TEXTJOIN("+",TRUE,IF(B3:E3<>"",B$1:E$1&LEFT(B$2:E$2,4),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

Press CTRL+SHIFT+ENTER to enter array formulas.

The OP has MS365 so will have no need to use Ctrl+Shift+Enter

 

[cogumelo]

Thanks a lot! Worked like a charm.
(confirmed no need for CTRL+SHIFT+ENTER as I'm using M365)

 

[Peter_SSs]

Thanks a lot! Worked like a charm.
(confirmed no need for CTRL+SHIFT+ENTER as I'm using M365)

Just checking:

if a cell value is not blank then i wish to concatenate the country and category for that column.

.. so why isn't the first result as below, given the the first row contains "100" and "-1", both of which meet the <>"" condition?
PT1100 + ES1100

Either that first expected result is incorrect or else you actually need something more like this?

=TEXTJOIN("+",1,IF(B3:E3>0,B$1:E$1&LEFT(B$2:E$2,4),""))