+ Reply to Thread
Results 1 to 11 of 11

Data Cleansing To Pull Unorganized Data Into Specific Colunms

  1. #1
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,857

    Re: Data Cleansing To Pull Unorganized Data Into Specific Colunms

    Proposition of code:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    Re: Data Cleansing To Pull Unorganized Data Into Specific Colunms

    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.

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,857

    Re: Data Cleansing To Pull Unorganized Data Into Specific Colunms

    If data is in the same format (columns A darta and B descriptors, headers in row 1, data starts in row 2), then no change is needed.
    Last row is detected here:
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    Re: Data Cleansing To Pull Unorganized Data Into Specific Colunms

    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?

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,857

    Re: Data Cleansing To Pull Unorganized Data Into Specific Colunms

    Don't know. Artificial Inteligence?
    How Excel could differentiate between

    School
    and
    BSc Computer Science at University of XX

    or note that person 2 got 3 jobs, while person 3 only 2 etc.

    Of course this is just my fault, because you wrote about data in A and your description in B already in post #1.
    But I opened the file and forgot what you wrote, focusing on efficient code to deal with data I've seen in a workbook.
    Last edited by Kaper; 11-01-2017 at 02:30 PM. Reason: gave description how the misunderstanding occured.

  7. #7
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    Re: Data Cleansing To Pull Unorganized Data Into Specific Colunms

    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.

  8. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,857

    Re: Data Cleansing To Pull Unorganized Data Into Specific Colunms

    So there are some "easy to recognize" descriptions. Then may be VBA could do this. But to try to modify a representative data set is needed (of course no real names, could be Hogwart University, Software Engineer at ACME Inc. etc.

  9. #9
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    Re: Data Cleansing To Pull Unorganized Data Into Specific Colunms

    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?

  10. #10
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    Re: Data Cleansing To Pull Unorganized Data Into Specific Colunms

    Hi Kaper,

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

    Thanks so much for your help!

  11. #11
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,857

    Re: Data Cleansing To Pull Unorganized Data Into Specific Colunms

    Glad to hear so, and thanks for marking thread solved and for reputation point.

    Note that aggregating for multiple Work, School (and Overview - this one will probably never be the case ) was already in a code I proposed:
    Please Login or Register  to view this content.
    because in this aspect your sample was representative.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automate columns of an unorganized data
    By aikiya22 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-09-2016, 11:41 PM
  2. Calculating simple data for large amount of unorganized data
    By eliasc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-31-2016, 01:59 PM
  3. [SOLVED] Pull specific data from a single cell with a long string of data
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-15-2015, 07:26 PM
  4. Sorting task of very unorganized data
    By ykw1991 in forum Excel General
    Replies: 2
    Last Post: 11-28-2013, 06:55 AM
  5. Need to pull data from closed file, sort, and send the data to specific sheets.
    By ghostman73 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2013, 05:13 PM
  6. [SOLVED] Macro to pull imported data from sheet2 and place data in specific rows on sheet1
    By PatRiot199 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-05-2013, 04:22 PM
  7. Transfer Unorganized Data between sheets
    By ryanb4614 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-25-2012, 01:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1