Conversion: Horizontal, Grouped Data to Vertical, Separated Data

[cdkeil]

Hey people,

Newbie here. With a fairly unique problem, I think.

Right now, my data looks like this:

BOB A B C E J Y
SAM A E J Y
JILL P A C M


In the future, I would love a two-column arrangement that looks like:

BOB A
BOB B
...
SAM A
SAM E
...
JILL P
JILL A
...


Is that possible?

[newdoverman]

This should do what you want:

Please Login or Register  to view this content.

[cdkeil]

Thank you!

I'm still somewhat confused however: how would I use that formula to create the new table? I want to convert a large dataset that is in the first format.

[nsv]

But I think cdkeil wants them in two different columns.

Assuming data starts in col A1, you can use this formula in cell B1

Please Login or Register  to view this content.

and this in cell C1

Please Login or Register  to view this content.

then copy cells B1 and C1 down

NSV

Edit: I forgot to change my semicolons to commas - corrected now

[newdoverman]

You can take the results of =LEFT(A2,SEARCH(" ",A2,1)+1) and copy the column of results and immediately Paste Values and that will get rid of the formulae. Now you can move the column to another location or delete the first column....you are free to do as you like now that you have the data that you need.

[newdoverman]

I was misreading the original problem.

[cdkeil]

Hello everyone,

I really appreciate the responses - but I still think I've been explaining this poorly. I've been able to separate the data into different columns (using text to columns, with a delimiter -- was initially confusing because there were line breaks, but I cleaned the data to get rid of them), but I would still like a different organization.

I've attached a file to explain what I have (INPUT), and what I'd like the data to look like (OUTPUT).

Again - thank you so much for your quick replies, this community is extremely impressive for its fast turnaround... and I apologize for my poor explanation above.

[AB33]

The attached solution is a macro and there is a button on sheet1.
The outout is on new sheet created by the code itself.

[newdoverman]

Maybe this will help.

[cdkeil]

That macro worked perfectly! Time for me to learn VisualBasic I guess...

[cdkeil]

And thanks for your help, newdoverman!

[cdkeil]

The attached solution is a macro and there is a button on sheet1.
The outout is on new sheet created by the code itself.

Hi AB33,

Thank you so much for this macro! It works perfectly.

If I wanted to make it grab (and repeat) the columns A and B, varying by columns C-..., how would I edit the code? The below attachment shows my new request.

Thanks again,
CK

[AB33]

I did not follow the output data. See if this is what you want.