how to convert multiple column data into two columns

Hi all,
I am new to excel sheet. I have data like Jobs which are mapped to Roles.
Jobs are in first column and roles are mapped to multiple columns to jobs.
few columns have empty values. So now i want to make result exact to two columns
first column is Jobs and second column is roles mapped to it. Attachment 682294
for more details pls see attached image.

how to achieve this ?

Regards
Alena

Hello sap2975. Welcome to the forum.

In the attached columns A and B please find these formulas. If your regional settings require ";" argument separators your copy of Excel will correct for that.(These formulas use USA setting ... "," separators.)

In A10 and filled down is an approach I picked up from BMV a few months ago.

Formula:

Please Login or Register  to view this content.

In B10 and filled down

Formula:

Please Login or Register  to view this content.

Change the first formula to this array entered formula.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

@Kuni
Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread