Data Cleansing To Pull Unorganized Data Into Specific Colunms

Hello,

I have attached a spreadsheet with sample of the data I am trying to clean by organizing into columns (Name; City; Role; Last Active; Overview; Work; School). Currently the data is all in the first column, on different rows. On the attached spreadsheet I have shown how I want the data to be structured using the first person on the list. In Column B I have written out the corresponding titles for each piece of data, as well as highlighted which pieces of data can be deleted.

I believe VBA is the best way to go here, but am not sure what the code should look like.

If anyone can help me out with this it would be much appreciated.

Hi Kaper,

That works for the data set I provided. Thanks for your help.

What do I have to change in the above code for this to read data all the way down to cell A2098 (this is how much info I have in the actual data set).

It is not obvious in the code how I make this change.

Thanks for any further help you can provide.

Oh, I didn't realize it used the column B descriptors. I just added those as identifiers to show what I wanted the final data to look like. Is there a way to to this without using the column B descriptors and just using the information that is in column A?

Ok, thanks for looking into it. I though maybe there were enough trends in the data set for VBA to be used. For example, the school they went to is always in the cell under "school".

I will try and figure out another solution.

Hello,

I have decided to take a different approach here and use column B to manually classify all of the record that I am interested in. I have used a countif function to search for key words in the profile. Once I find a profile that I am interetsed in, I then classify them as per the spreadsheet that I attached in the original post.

Therefore, I can use your code, but I think it will need one change. Is it possible to aggregate data with the same classification? For example, if the profile has attended two different schools (classified in column B as "School") and has had 3 different jobs (classified in column B as "WORK", the data will get rolled up into one cell (e.g. BS Mechanical Engineering at University of California, Davis 2010; MSc Mechanical Engineering at University of Xx Barcelona 2011).

Is it possible to do this?

Hi Kaper,

Everything now works now I have introduced a little manual step.

Thanks so much for your help!