1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Removing unwanted data - Excel

Discussion in 'Business Applications' started by Callisto012, Aug 1, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. Callisto012

    Callisto012 Thread Starter

    Joined:
    Jul 21, 2006
    Messages:
    24
    Here is my dilemma:
    I was given a spreadsheet and was asked to clean it up. They know I know what a computer is so that must assume I know Excel too... anyway:
    Please note example. This is a list of car dealers but not all the data is the same for each group. ie, Map Directions is missing, or Managers name, Fax Number, etc

    I know how to change rows to column, but only if each grouping had the same number of items. See Sheet 2 for the formula I am using to take care of this... but note how it goes awry.

    All I need is Company name, address, phone number (which every grouping has) It just has other stuff I don't need, and I don't know how to go about deleting this extra data.

    Any help would be great!
    Thanks in advance!
     

    Attached Files:

  2. djangojazz

    djangojazz

    Joined:
    Apr 11, 2006
    Messages:
    301
    Is this a ton of stuff? Because you can simple cut and paste if it isn't. To speed it up you can use the quick commands of CTRL + C is copy to clipboard, CTRL + V is paste from clipboard. Make up new headings then do an auto filter for your boss, supervisor, person you answer to.(see attached)

    I forgot how you do that, you could teach me that one.
     

    Attached Files:

  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    If all the data is like the example, you're stuck with doing it manually. It's just way too unstructured for formulas and/or code to get a handle on. Sorry. :(
     
  4. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Here is a manual way that should work.

    First in B1, enter 1. In B2, enter 2.
    Then, select B1 and B2
    Then, grab the bottom corner of the selection (where the large + appears) and drag all of this down column B to the bottom of your data.
    This will number each record from 1 to whatever.

    Then, Do edit, find, replace tab
    enter FAX: and replace with XXXX, replace ALL

    Then, change FAX: to Map Directions and replace with XXXX, replace ALL

    Then Manager Name and replace with XXXX, replace ALL

    Now, all of your data that you don't want should start with XXXX

    Select all of your data (including the new B column) from A1 to B {whatever}

    Then, Data, Sort, Sort by Column A
    (now, all of the XXXX fields are together)

    Scroll to them, select all those rows and deleted them

    Then, reselect all of the data again from A1 to B {whatever}
    And, Data, Sort, Column B
    (this will now resort the data back based on the sequence number)

    Then, delete column B
     
  5. Callisto012

    Callisto012 Thread Starter

    Joined:
    Jul 21, 2006
    Messages:
    24
    I take NO credit for this: ALL THANKS TO bomb #21 (y) (y) (y)

    This works perfectly
     
  6. Callisto012

    Callisto012 Thread Starter

    Joined:
    Jul 21, 2006
    Messages:
    24
    :eek: Geez! That's so easy it's gotta work!!! I'll try this when I'm back at work, but there is no reason why it shouldn't.
    I think there are close to 9000 records in the original document... manual Select and Delete is NOT an option I want to do... which is why I'm here! :D
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    My mistake, I misunderstood the question.

    WhitPhil's method will strip the cells you don't want. You could do it a lot easier (do Replace x 3 ; replace FAX:* -- Map Directions* -- Manager Name* with nothing, then do Go To > Special > Blanks & Delete).

    You can then use TRANSPOSE to convert the list to "records", as long as each required item is present throughout (in your sample I notice that there's no phone for "Robertson Toyota", but hopefully that's just an oversight).
     
  8. Callisto012

    Callisto012 Thread Starter

    Joined:
    Jul 21, 2006
    Messages:
    24
    WhitPhil: Thank you, your method did work very well, but bomb #21 did make it easier. Sorry buddy!

    Again thank you bomb #21 for your help!!! 2 for 2, with me anyway (y) (y) (y)
    THANKS THANKS THANKS!!!

    I would have never thought that Find and Replace would see the * as the wildcard rather than the * character.
     
  9. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    Awesome!

    Another trick for the tool kit! :)
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/488534

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice