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, welcome to the forum!

If I understand correctly, you can change your SUMIFS() formula, for example - to the below:

=SUMIFS(F42:F71,G42:G71,"PCS") & " PCS"

 

[shah0101]

Thanks FormR,

If I understand correctly, this would work with only one UNIT i.e. PCS, but I have another UNITS and their relevant quantities as well.

Please advise.

Thanks,

 

[FormR]

Hi, sorry but I don't understand then.

Your SUMIFS() formula are only summing one unit at a time.

Maybe you need to post some example data of both the inputs and expected outputs - this link shows a few different ways to do that:

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[shah0101]

Hi, sorry but I don't understand then.

Your SUMIFS() formula are only summing one unit at a time.

Maybe you need to post some example data of both the inputs and expected outputs - this link shows a few different ways to do that:

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Thanks again FormR.

Inputs above are real numbers with the formula to extract that as well.

For precise output three precise examples are mentioned above.

Please advise if I am unable to explain it clearly please?

Thanks.

 

[FormR]

Please advise if I am unable to explain it clearly please?

Are you trying to concatenate all the quantity and units into a single cell where the quantity returned from the SUMIF() is greater than zero?

If yes, does your version of Excel have the TEXTJOIN() formula available? Are there really just 6 different unit types that need conditionally concatenating?

 

[shah0101]

Are you trying to concatenate all the quantity and units into a single cell where the quantity returned from the SUMIF() is greater than zero?

If yes, does your version of Excel have the TEXTJOIN() formula available? Are there really just 6 different unit types that need conditionally concatenating?

Dear FormR,

Again thanks and much appreciated.

Yes, you are absolutely right, this is exactly what I am trying to achieve. Quantity and its relevant Unit concatenated if there is any value in Quantity.

Yes, in my version of XL I can see the TEXTJOIN() formula. Never used or known of it before, can you please guide / advise to get me where I want to be please.

Thanks,

 

[shah0101]

Dear FormR,

Again thanks and much appreciated.

Yes, you are absolutely right, this is exactly what I am trying to achieve. Quantity and its relevant Unit concatenated if there is any value in Quantity.

Yes, in my version of XL I can see the TEXTJOIN() formula. Never used or known of it before, can you please guide / advise to get me where I want to be please.

Thanks,

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

Dear FormR,


With your suggestion I tried TEXTJOIN() as follows: =TEXTJOIN(" ",TRUE,B82:C87)


The Result is as follows: 1219 PCS, 1377 KGS, 2688 SET, 0 YDS, 1100 MTR, 2390 PAR,


FROM B82 to C87 following are the values taken from sumifs():
1219 PCS
1377 KGS
2688 SET
0 YDS
1100 MTR
2390 PAR


Now I am unable to figure out how I hide or remove "0 YDS" from the result because there is no value in it so it shouldn't be there.

Please guide / advise.

Thanks.

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

 

[FormR]

Hi, I can't test this as I don't have a new enough version of Excel where I am today but you could try this:

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

This is an array formula and you need to commit it using CTRL+SHIFT+ENTER - google will help if you need more information on how to enter array formula in Excel.

 

[shah0101]

Hi, I can't test this as I don't have a new enough version of Excel where I am today but you could try this:

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

This is an array formula and you need to commit it using CTRL+SHIFT+ENTER - google will help if you need more information on how to enter array formula in Excel.

I just tried and the result is:

1219 PCS, 1377 KGS, 2688 SET, FALSE FALSE 1100 MTR, 2390 PAR,

Again the same problem, how may I remove / hide the "FALSE FALSE" now. Any suggestions please?