Need to convert large amount of data from one format into another

[mjp155]

Hello,

Simply put, I have a large sheet of data that is organized one way, and I need to organize it into a specific format.

The data I have looks like this, where there are two columns(separated by "/"):

UniqueID/Product+email

23456 / productx
23456/producty
23456/ productz
23456/ email234@email.com
23457/ productw
23457/ productz
23457/ email234567@email.com
23457/ email23567@email.com

I also have a version that looks like this, where there are 3 columns and emails are in the third (columns separated by "/"):

UniqueID Product Email

23456 /productx
23456 / producty
23456 / productz
23456// email234@email.com
23457 / productw
23457/ productz
23457// email234567@email.com
23457// email23567@email.com


Ultimately, I NEED the spreadsheet to look like this (in csv format):

email234@email.com,productx,producty,productz
email234567@email.com,productw,productz
email23567@email.com,poductw,productz

Is there an easy way to do this that I can't think of? Or do I need to convert into this format manually? There is close to 1000 lines of this data so I hope the answer is not the latter!

Thanks!

 

[icecurtain]

Are the numbers field important, I am think text to columns will assist here using the / as a delimiter.

I normally only because excel in the older versions did not cope very well. copy all the data in to word doc replace the spaces with find and replace with a comma then get rid of any other /.\ also with find and replace , then save as csv or paste back in to a excel spreadsheet the use text to colums, or covert to a table in word before pasting back to excel.

 

[mjp155]

I should clarify....I not only want the data in csv format, but I want to show, for each email in the unique id, what products they bought within that id, thus showing what each email is entitled to.

So, for unique id 23457, I want to show, by email, what products they bought.

so I want to show that
email234567@email.com is entitled to productw,productz...

and I also want to show that

email23567@email.com is entitled to productw,productz.

All of the products are above the emails for each unique ID.