Transpose Rows Into Columns Based on Cell Value

Hey guys!

I have a set of data in column A that is about 6000 cells long. It contains name, email address, and a "chapter (I thought I could use this as a unique identifier), set up like this:

Name
Email Address
Chapter: *Los Angeles* <- example

Problem is that when the data was imported the email addresses were weird and went into multiple rows. So the data I have looks like this:

Doe, John
john.doe(AT)
mail(dot)com
Chapter: JohnnyDoe
Doe, John
john.doe(AT)mail(dot)com
Chapter: JohnnyDoe

This goes on fairly randomly for the rest of the data. I need a way to get the data into a format like this for a CSV file:

Name / Email / Chapter
Name / Email / Chapter
etc

The only thing I can find online is how to use the INDEX function for transposing data for a set number of lines, but my number changes constantly. Hopefully the "Chapter" identifier can help a little.

Thanks in advance!

Attach example EXCEL file (not a picture, pasted text or any other than Excel format!). This file should be attached to a new post. Never change your original post (except admin's request).

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

Here is a brief section of the document I am working with.

Hi
Is your attachment indicative of the data? ie is it inconsistently broken up? Do you have the chance to add field prefixes like name/email in addition to the chapter?
If it was
name
email
chapter
I have a solution but if the names and addresses are not consistently spaced it will need a different solution.

If you are able to use PowerQuery

Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel

try this:

Hi,

Yes it is inconsistently broken up.

I can't add prefixes sadly.

with PowerQuery you can add prefix, suffix, whatever and didn't touch source table

I am unable to use PowerQuery.

I have now been able to add prefixes in Column A, and the same data as before in Column B.

Name / Doe, John
Email / johndoe(AT)gmail
Chapter / Chapter:GHNAJK

For the data uploaded in your workbook,

Please Login or Register  to view this content.

Thanks!!

It wasn't perfect, but I got it working.

I appreciate the help.

Originally Posted by emd62897

T
It wasn't perfect, but I got it working.

How wasn't it perfect?
You didn't even provide your desired result.

Your code worked perfectly, it was a formatting issue which can me attributed to me to not being specific on how I wanted the data to look.

Thanks for helping.