+ Reply to Thread
Results 1 to 10 of 10

I need help organizing data in wrong cells and putting them in the right the categories

  1. #1
    Registered User
    Join Date
    10-09-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    3

    I need help organizing data in wrong cells and putting them in the right the categories

    Hey guys,

    I need help sorting through data that is not in the correct cells.

    How do I make it so the data matches the categories (Highlighted yellow)?

    Edit: Heres a link to a better picture : http://imgur.com/NrMOAcd

    Also I have included the excel file. It includes about 2800 entries that need this kind of cleaning up! Please help!!
    Attached Files Attached Files
    Last edited by summit2ghype; 10-09-2015 at 09:23 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,647

    Re: I need help cleaning a file with data in wrong cells and not matching the categories

    Where did the data come from? How did you treat it to get it into the state that it is in? Can we see the original format of the data?
    Glenn



  3. #3
    Registered User
    Join Date
    10-09-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    3

    Re: I need help cleaning a file with data in wrong cells and not matching the categories

    It is homework for an business class that is requiring us to organize the data. Unfortunately this is the only file given with data.

    Edit: How about just finding the zip code (5 digits) in a record row (a range of cells rather than one cell)?
    Last edited by summit2ghype; 10-09-2015 at 12:50 PM.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,647

    Re: I need help organizing data in wrong cells and putting them in the right the categorie

    Is this a VBA class, or a non-VBA class???

  5. #5
    Registered User
    Join Date
    11-01-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: I need help organizing data in wrong cells and putting them in the right the categorie

    It looks like the addresses just need to be concatenated together. 1 Tiffany Place is taking up 3 cells. Also "Woods Hole" is taking up 2 cells instead of one. I would make a helper column Concatenate the information together and then paste as Value the helper column and delete what you don't need.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,647

    Re: I need help organizing data in wrong cells and putting them in the right the categorie

    But how do you KNOW how many cells need to be concatenated??? The raw data really do look like something the cat sicked up!!!

  7. #7
    Registered User
    Join Date
    11-01-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: I need help organizing data in wrong cells and putting them in the right the categorie

    Yeah, that is an issue. It seems to vary based on the length of street and city names. I'm not sure how you would programatically fix that.

  8. #8
    Registered User
    Join Date
    10-09-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    3

    Re: I need help organizing data in wrong cells and putting them in the right the categorie

    Alright thanks guys, I just spoke with my professor and he did not realize it was jumbled up. I got a new one and now I know what to do :D.

  9. #9
    Registered User
    Join Date
    11-01-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2010
    Posts
    14
    Haha, so you did what I would have done. Send it back to the person who gave me the data, and tell them I can't use this format.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,647

    Re: I need help organizing data in wrong cells and putting them in the right the categorie

    Typical academic.... He never thought to check it first!!

+ 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. [SOLVED] Keyword Matching & Categories
    By leeandoona in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-09-2014, 08:29 AM
  2. Replies: 0
    Last Post: 07-22-2011, 03:51 PM
  3. Replies: 7
    Last Post: 06-15-2011, 04:40 AM
  4. Excel 2007 : Cleaning Data in an upload file...
    By AppSupportKarl in forum Excel General
    Replies: 0
    Last Post: 02-09-2011, 10:51 AM
  5. Splitting cells, rearranging columns and rows, cleaning up data
    By daniel.henriksen in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-11-2010, 03:55 AM
  6. Annual summary with categories and matching
    By minghh in forum Excel General
    Replies: 3
    Last Post: 12-14-2009, 07:24 PM
  7. Matching categories and Summing
    By Pasha81 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-10-2009, 01:52 PM

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