Textjoin, but ignore if qty is zero (or blank)

[ellison]

Hi, we are looking to add a summary of some data by way of a textjoin...
But we'd only like the textjoin to be carried out on info where the corresponding qty is > 0.
Sometimes the corresponding qty can also be blank (please also ignore for the textjoin)
Sorry for the rambling, hopefully this maye help make it more a bit clearer...

textjoin-but-ignore-if-qty-is-zero.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Row	Class-1	Class-1-Qty	Class-2	Class-2-Qty	Class-3	Class-3-Qty	TextJoin (Straight, but the info isn't the way we would like it)	TextJoin (Manual!)		Manual Notes (not needed, just to help to explain)
2	3	alpha	150			gold	250	alpha ; 150 ; gold ; 250	alpha ; 150 ; gold ; 250		Alpha and Gold both have accompanying qty's, so include, but skip the blank info in Class-2
3	4	beta	100	hotel	500	mike	20	beta ; 100 ; hotel ; 500 ; mike ; 20	beta ; 100 ; hotel ; 500 ; mike ; 20		Classes 1,2 & 3 are present, all qty's are > 0, so include all of the info
4	5	charlie	0	india	25	november		charlie ; 0 ; india ; 25 ; november	india ; 25		Class 1 is present but qty is 0, Class 2 is blank, so only include the info for Class 3
5	6	delta				juliet	5	delta ; juliet ; 5	juliet ; 5		Only include the info for Class 3
6	9	foxtrot	25	kilo				foxtrot ; 25 ; kilo	foxtrot ; 25		Only include the info for Class 1
Sheet1

Cell Formulas
Range	Formula
H2:H6	H2	=TEXTJOIN(" ; ",TRUE,B2:G2)

Thanks for taking a peek

 

[Peter_SSs]

Would this suffice?

23 10 09.xlsm
	B	C	D	E	F	G	H
1	Class-1	Class-1-Qty	Class-2	Class-2-Qty	Class-3	Class-3-Qty
2	alpha	150			gold	250	alpha ; 150 ; gold ; 250
3	beta	100	hotel	500	mike	20	beta ; 100 ; hotel ; 500 ; mike ; 20
4	charlie	0	india	25	november		india ; 25
5	delta				juliet	5	juliet ; 5
6	foxtrot	25	kilo				foxtrot ; 25
TJ

Cell Formulas
Range	Formula
H2:H6	H2	=TEXTJOIN(" ; ",1,IF(ISNUMBER(C2:G2),IF(C2:G2>0,B2:F2&" ; "&C2:G2,""),""))

 

[Georgiboy]

That's shorter than my effort:

LeaveData.xlsx
	A	B	C	D	E	F	G	H
1	Row	Class-1	Class-1-Qty	Class-2	Class-2-Qty	Class-3	Class-3-Qty	TextJoin (Manual!)
2	3	alpha	150			gold	250	alpha ; 150 ; gold ; 250
3	4	beta	100	hotel	500	mike	20	beta ; 100 ; hotel ; 500 ; mike ; 20
4	5	charlie	0	india	25	november		india ; 25
5	6	delta				juliet	5	juliet ; 5
6	9	foxtrot	25	kilo				foxtrot ; 25
Sheet2

Cell Formulas
Range	Formula
H2:H6	H2	=BYROW(B2:G6,LAMBDA(x,TEXTJOIN(" ; ",,BYCOL(FILTER(WRAPCOLS(x,2),INDEX(WRAPCOLS(x,2),2)>0),LAMBDA(x,TEXTJOIN(" ; ",,x))))))
Dynamic array formulas.

 

[ellison]

Crumbs, thanks for your help guys, absolutely brilliant - no wonder I couldn't figure that out!!!

I marked up the shorter one for the solution, but both work....

And actually the one from Georgiboy allows you to add in extra columns (i.e. add in Class 4 with qty, Class5 etc)

 

[ellison]

Can I ask a follow on question from the Georgiboy formula... could something like that also be used to avoid including any duplicated information?

(I think I should probably post that as a separate question - but just in case it's a quick answer, I'll try putting it on here first...)

 

[Georgiboy]

You mean if the class and QTY is duplicated as below:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Row	Class-1	Class-1-Qty	Class-2	Class-2-Qty	Class-3	Class-3-Qty	Class-4	Class-4-Qty	TextJoin (Manual!)
2	3	alpha	150			gold	250			alpha ; 150 ; gold ; 250
3	4	beta	100	hotel	500	mike	20			beta ; 100 ; hotel ; 500 ; mike ; 20
4	5	charlie	0	india	25	november				india ; 25
5	6	delta				juliet	5	juliet	5	juliet ; 5
6	9	foxtrot	25	kilo		foxtrot	25			foxtrot ; 25
Sheet1

Cell Formulas
Range	Formula
J2:J6	J2	=BYROW(B2:I6,LAMBDA(x,TEXTJOIN(" ; ",,UNIQUE(TOCOL(BYCOL(FILTER(WRAPCOLS(x,2),INDEX(WRAPCOLS(x,2),2)>0),LAMBDA(x,TEXTJOIN(" ; ",,x))))))))
Dynamic array formulas.

 

[ellison]

Oh my gosh that is astonishing! Wow, yes - that's exactly what I meant.....

(Sorry Peter, I changed the solution to the one above as this version of the solution can also cope with extra columns being added - and can also offer the dedupe. Totally appreciate I hadn't mentioned either of those in my first post though, apologies)

 

[Peter_SSs]

Sorry Peter, I changed the solution to the one above as this version of the solution can also cope with extra columns being added - and can also offer the dedupe.

No need to apologise - you should choose whichever one suits you best.

Never-the-less I'm not sure what makes you think that my style of formula cannot cope with more columns (or dupes)

23 10 09.xlsm
	B	C	D	E	F	G	H	I	J
1	Class-1	Class-1-Qty	Class-2	Class-2-Qty	Class-3	Class-3-Qty	Class-4	Class-4-Qty	TextJoin
2	alpha	150			gold	250			alpha ; 150 ; gold ; 250
3	beta	100	hotel	500	mike	20			beta ; 100 ; hotel ; 500 ; mike ; 20
4	charlie	0	india	25	november				india ; 25
5	delta				juliet	5	juliet	5	juliet ; 5
6	foxtrot	25	kilo		foxtrot	25			foxtrot ; 25
TJ (2)

Cell Formulas
Range	Formula
J2:J6	J2	=TEXTJOIN(" ; ",1,UNIQUE(IF(ISNUMBER(C2:I2),IF(C2:I2>0,B2:H2&" ; "&C2:I2,""),""),1))

 

[ellison]

Ouch, you're absolutely right - sorry Peter!