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()).

	A		B
1	Quantity	Formula Enetered	Unit
2	1219	=SUMIFS(F42:F71,G42:G71,"PCS")	PCS
3	1377	=SUMIFS(F42:F71,G42:G71,"KGS")	KGS
4	1750	=SUMIFS(F42:F71,G42:G71,"SET")	SET
5	938	=SUMIFS(F42:F71,G42:G71,"YDS")	YDS
6	1100	=SUMIFS(F42:F71,G42:G71,"MTR")	MTR
7	2390	=SUMIFS(F42:F71,G42:G71,"PAR")	PAR

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

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:

1219	PCS,
1377	KGS,
2688	SET,
0	YDS,
1100	MTR,
2390	PAR,

<colgroup><col><col></colgroup><tbody>
</tbody>

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()

7397	PCS,
1377	KGS,
0	SET,
0	YDS,
0	MTR,
0	PAR,

<colgroup><col><col></colgroup><tbody>
</tbody>

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!