if cell has any value, then return text in different cell

[liloo]

Hi Everyone,

I created a simple table to illustrate the data I have in my original workbook. In my workbook, this table is pulled from another sheet using VLOOKUP. The values are quantity purchased by each company. "-" indicates the company did not purchase anything in said year for said product. "0" indicates the company did purchase said product, in a particular quarter of the year.

I would like to show in a new column the tally for each company. I want to write a formula that would spit out "2019Q4_A, 2019Q2_B, 2020Q2_B, 2020Q4_C" for ABC Inc. I don't know where to start. I've been looking up different functions and imagine this may be a combination of VLOOKUP, ISNUMBER and COUNTIF???

Any guidance is much appreciated!

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1		2019Q1 Product A	2019Q2 Product A	2019Q3 Product A	2019Q4 Product A	2019Q1 Product B	2019Q2 Product B	2019Q3 Product B	2019Q4 Product B	2019Q1 Product C	2019Q2 Product C	2019Q3 Product C	2019Q4 Product C	2020Q1 Product A	2020Q2 Product A	2020Q3 Product A	2020Q4 Product A	2020Q1 Product B	2020Q2 Product B	2020Q3 Product B	2020Q4 Product B	2020Q1 Product C	2020Q2 Product C	2020Q3 Product C	2020Q4 Product C
2	ABC Inc	0	0	0	4	0	6	0	0	-	-	-	-	-	-	-	-	0	10	0	0	0	0	0	20
3	DEF Corp	-	-	-	-	1	0	0	2	-	-	-	-	1	0	0	0	0	0	5	0	-	-	-	-
4	GHI Ltd	10	0	0	0	-	-	-	-	-	-	-	-	-	-	-	-	0	10	0	0	0	0	50	0
5	JKL Enterprise	0	1	0	1	0	2	0	2	-	-	-	-	0	1	0	0	6	0	0	0	-	-	-	-
6	MNO Ltd	-	-	-	-	-	-	-	-	-	-	-	-	0	0	2	0	0	0	0	8	-	-	-	-
Sheet1

 

[kweaver]

Great. Happy it worked.

If there's a change NONE of the periods would have a value, you might want to change the formula to:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER($B$1:$Y$1,(B2:Y2>0)*(B2:Y2<>"-"))),"")

 

[liloo]

Great. Happy it worked.

If there's a change NONE of the periods would have a value, you might want to change the formula to:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER($B$1:$Y$1,(B2:Y2>0)*(B2:Y2<>"-"))),"")

I'm back with a follow-up question.

Now in an effort to simplify the results, is there a way to write a formula that will result in JKL Enterprise's summarized purchase to "A 1H19, A 2H19, B 1H19, B 2H19, A 1H20, B 1H20"?
I started with =IF(OR(ISNUMBER(SEARCH("2019Q1 Product A", Z2)), ISNUMBER("2019Q2 Product A", Z2))), "A 1H19", ""), I don't know how to tag on the other conditions.
*Column Z being where the TEXTJOIN formula is housed.