Concatenate in Different Scenarios

[shah0101]

Hi Experts,

Following is the data and I want to concatenate the numbers with unit. The data is extracted from (=SUMIFS()).


[TABLE="width: 427"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Quantity[/TD]
[TD] Formula Enetered[/TD]
[TD]Unit[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1219[/TD]
[TD]=SUMIFS(F42:F71,G42:G71,"PCS")[/TD]
[TD] PCS [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1377[/TD]
[TD]=SUMIFS(F42:F71,G42:G71,"KGS")[/TD]
[TD] KGS [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1750[/TD]
[TD]=SUMIFS(F42:F71,G42:G71,"SET")[/TD]
[TD] SET [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]938[/TD]
[TD]=SUMIFS(F42:F71,G42:G71,"YDS")[/TD]
[TD] YDS [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1100[/TD]
[TD]=SUMIFS(F42:F71,G42:G71,"MTR")[/TD]
[TD] MTR [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2390[/TD]
[TD]=SUMIFS(F42:F71,G42:G71,"PAR")[/TD]
[TD] PAR [/TD]
[/TR]
</tbody>[/TABLE]



Sometimes there is only one unit i.e. "PCS" or two units i.e. YDS and PAR or sometimes all seven units.

What I am trying to achieve is get the quantity and unit if it is present otherwise return void.

I mean if there is only 938 YDS then a cell must return: "938 YDS".
I mean if there are 1750 SET and 1100 MTR then a cell must return: "1,750 SET, 1,100 MTR".
I mean if there are 1750 SET and 1100 MTR and 1377 KGS then a cell must return: "1,750 SET, 1,100 MTR", 1,377 KGS".


Please guide / advise.

Thanks in advance.

 

[FormR]

Hi, OK - try instead.

=TEXTJOIN(", ",TRUE,IF(B82:B87>0,B82:C87,""))

Again, entered with CTRL+SHIFT+ENTER

 

[shah0101]

Hi, OK - try instead.

=TEXTJOIN(", ",TRUE,IF(B82:B87>0,B82:C87,""))

Again, entered with CTRL+SHIFT+ENTER

Result with above formula: 1219, PCS, , 1377, KGS, , 2688, SET, , 1100, MTR, , 2390, PAR,


Can we get following result with extra commas please: 1219 PCS, 1377 KGS, 2688, SET, 1100 MTR, 2390 PAR,

 

[shah0101]

Result with above formula: 1219, PCS, , 1377, KGS, , 2688, SET, , 1100, MTR, , 2390, PAR,


Can we get following result with extra commas please: 1219 PCS, 1377 KGS, 2688, SET, 1100 MTR, 2390 PAR,

The values on the range are as follows:

[TABLE="width: 269"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]1219[/TD]
[TD] PCS, [/TD]
[/TR]
[TR]
[TD="align: right"]1377[/TD]
[TD] KGS, [/TD]
[/TR]
[TR]
[TD="align: right"]2688[/TD]
[TD] SET, [/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] YDS, [/TD]
[/TR]
[TR]
[TD="align: right"]1100[/TD]
[TD] MTR, [/TD]
[/TR]
[TR]
[TD="align: right"]2390 [/TD]
[TD]PAR, [/TD]
[/TR]
</tbody>[/TABLE]


Should I remove the COMMA on UNIT fields or from the formula please?

 

[FormR]

OK - let's try like this (I wish I could test it!)

=TEXTJOIN(", ",TRUE,IF(B82:B87>0,B82:B87 & " " & C82:C87 ,""))

Don't include the comma on the unit field.

 

[FormR]

Should I remove the COMMA on UNIT fields or from the formula please?

Remove it from the unit field and try the formula in post 11 - if that doesn't work try the formula in post 14

 

[shah0101]

OK - let's try like this (I wish I could test it!)

=TEXTJOIN(", ",TRUE,IF(B82:B87>0,B82:B87 & " " & C82:C87 ,""))

Don't include the comma on the unit field.

===============================

FormR,

You know what, you are not a star, you are not a superstar, you are SUPER SUPER STAR!!!!!! Thanks a billion!!!

Following are the details for everyone else who can have the fruits from a SUPER SUPER STAR!


Data gathered with IF() and SUMIFS()

[TABLE="width: 269"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]7397[/TD]
[TD] PCS, [/TD]
[/TR]
[TR]
[TD="align: right"]1377 [/TD]
[TD]KGS, [/TD]
[/TR]
[TR]
[TD="align: right"]0 [/TD]
[TD]SET, [/TD]
[/TR]
[TR]
[TD="align: right"]0 [/TD]
[TD]YDS, [/TD]
[/TR]
[TR]
[TD="align: right"]0 [/TD]
[TD]MTR, [/TD]
[/TR]
[TR]
[TD="align: right"]0 [/TD]
[TD]PAR, [/TD]
[/TR]
</tbody>[/TABLE]


Unit filed has a Comma and a Space after it.

Here comes the SUPER SUPER STAR:


=TEXTJOIN(" ",TRUE,IF(B82:B87>0,B82:C87,""))
CTRL+SHIFT+ENTER

and the result is:
7397 PCS, 1377 KGS,





Thanks again FormR. Much Appreciated!