CONCATENATE

[Sufiyan97]

I want to =CONCATENATE 'Column I' occurring of next number in 'Column H' as per attached picture expected result in 'Column J'

 

[VBE313]

What office are you running? Office 365?

 

[VBE313]

If you are using Office 365, you can use this formula... The only part of this formula that requires Office 365 is TEXTJOIN. I noticed how you wanted spaces in between each thing, so TEXTJOIN is the best formula for this scenario. The part where it says "ROW(),2)" the 2 means column B.

=IF(A1<>"",TEXTJOIN(" ",TRUE,OFFSET(INDIRECT(ADDRESS(ROW(),2)),0,0,IFERROR(MATCH(A1,A2:A108,0),1+COUNTA(A2:A108)))),"")

 

[Brunts]

If not running Office 365, what's the alternative?

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects what functions you can use. Thanks

 

[Brunts]

2016 - now updated (Apologies to alidurfani for hijacking your thread but interested in the answer for my own purposes)

 

[Fluff]

Thanks for updating your profile.
With 2016, you would be better off with a UDF or macro, as you don't have the TextJoin function.
If you need that, then you will have to start a thread of your own.

 

[Sufiyan97]

What office are you running? Office 365?

Office 2016

 

[Sufiyan97]

2016 - now updated (Apologies to alidurfani for hijacking your thread but interested in the answer for my own purposes)

No problem

 

[Sufiyan97]

If you are using Office 365, you can use this formula... The only part of this formula that requires Office 365 is TEXTJOIN. I noticed how you wanted spaces in between each thing, so TEXTJOIN is the best formula for this scenario. The part where it says "ROW(),2)" the 2 means column B.

=IF(A1<>"",TEXTJOIN(" ",TRUE,OFFSET(INDIRECT(ADDRESS(ROW(),2)),0,0,IFERROR(MATCH(A1,A2:A108,0),1+COUNTA(A2:A108)))),"")

View attachment 21518

Thank you very much for your help.

I am using office 2016 so don't have TEXTJOIN function, if possible can you please provide solution with CONCATENATE or other which support in office 2016 if there is any?