how to convert multiple column data into two columns

[sap2975]

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

[FlameRetired]

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.

Please Login or Register  to view this content.

In B10 and filled down

Please Login or Register  to view this content.

[sap2975]

Hi Dave,

Thank you, it is working. Now if i want to increase number of columns from 5 to 10, then in formula {1;1;1;1;1} will increase to 10 one's ?

I tried it but it didn't work.

Best Regards

[FlameRetired]

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.

Please Login or Register  to view this content.

[kuni]

I've been trying to use these formulas on a similar (and larger) file and haven't had any luck. Could you help me out?

I have product names in Cells A1-A63 and Store Numbers in B1-HG63. Let's assume I'm beginning to combine everything in A65 & B65.

Thank you!

[alansidman]

@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