excel spreadsheet (not VBA) formula to concatenate tekst

lpvdsteen

Board Regular
Joined
Jan 21, 2003
Messages
161
Office Version
  1. 2016
Platform
  1. Windows
I am looking for an excel spreadsheet (not VBA) formula to concatenate text based on variable arrays.
to clarify
I have an employeefile where employees may or may not have multiple contracts serving in different roles/capacities
i have a column with the employeenumer
i have a column with the role
I would like a third column concatenating the roles, based on the employeenember (that this happens in every employee row is not a problem)
Employee ; Role ; AllRoles
12345 ; nurse ; nurse
23454 ; nurse ; nurse,teammanager
34345 ; manager ; manager,jobcoach,teacher
23454 ; teammanager ; nurse,teammanager
56875 ; nurse ; nurse
34345 ; jobcoach ; manager,jobcoach,teacher
34345 ; teacher ; manager,jobcoach,teacher
etc
Any ideas? this cannot be in VBA in this case,
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Please update your account details to tell us which version of excel you are using, remember to scroll down and save changes after choosing. Not all versions have the functions needed for a task like this.
Book1
ABC
1Employee Role AllRoles
212345 nurse nurse
323454 nurse nurse , teammanager
434345 manager manager , jobcoach , teacher
523454 teammanager nurse , teammanager
656875 nurse nurse
734345 jobcoach manager , jobcoach , teacher
834345 teacher manager , jobcoach , teacher
Sheet1
Cell Formulas
RangeFormula
C2:C8C2=TEXTJOIN(", ",1,UNIQUE(FILTER($B$2:$B$8,$A$2:$A$8=A2,"")))
 
Upvote 0
Thanks jasonb75, and a good suggestion to update profile. Did so right away.
your solution is and office 365 solution (Unique and Filter are 365 and up attributes as far as I know), and our company still works with 2016. Would have been so nice otherwise.
 
Upvote 0
To the best of my knowledge, 2016 will not have the functions needed to perform your task. I haven't tested anything to see if it is possible without Unique and Filter although I believe that it should be but would still require excel 2019 for the textjoin function.

Older versions can not concatenate arrays without using vba.
 
Upvote 0
Ik took the 365 solution and used it. When copying to excel 2016 it translate to a form of Language. Can't use it really, It took up 30 cores for about 10 minutes, which made me wonder a bit, and even comes with a calculation in excel 2016, but in 365 works like a charm.
Thanks
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top