Page 1 of 2 12 LastLast
Results 1 to 20 of 23

Thread: Excel sort & delete question....

  1. #1
    Registered User
    Join Date
    Aug 2002
    Location
    Berkshire
    Posts
    1,476
    Rep Power
    11

    Excel sort & delete question....

    Hi all,

    Just wondered if there are any EXCEL boffins out there who might be able to tell me who to do the following :-

    I've got an EXCEL spreadsheet with 11 columns (fields) and 92 rows of data. One of the columns contains surnames.

    I have a list of 12 surnames. If any of those 12 surnames happen to be the surname that appears in the surname column, then I want that row of data to remain as is. However, I want to delete all the remaining rows of data (i.e. where the surname columns data isn't equal to any of the 12 surnames in my list).

    Is there anyway of doing this automatically? Apart from the risk of human error, this is a process that I'm going to have to repeat quite often so I don't want to have to do this manually if possible.

    I'm guessing that a bit of c-sharp or vb script might be necessary, but if so I don't know where to start.

    Anyone got any ideas?

  2. #2
    Registered User
    Join Date
    Oct 2003
    Location
    Gold Coast, Austra
    Posts
    2,345
    Rep Power
    11

    Re: Excel sort & delete question....

    Quote Originally Posted by Will View Post
    Hi all,

    Just wondered if there are any EXCEL boffins out there who might be able to tell me who to do the following :-

    I've got an EXCEL spreadsheet with 11 columns (fields) and 92 rows of data. One of the columns contains surnames.

    I have a list of 12 surnames. If any of those 12 surnames happen to be the surname that appears in the surname column, then I want that row of data to remain as is. However, I want to delete all the remaining rows of data (i.e. where the surname columns data isn't equal to any of the 12 surnames in my list).

    Is there anyway of doing this automatically? Apart from the risk of human error, this is a process that I'm going to have to repeat quite often so I don't want to have to do this manually if possible.

    I'm guessing that a bit of c-sharp or vb script might be necessary, but if so I don't know where to start.

    Anyone got any ideas?
    Step 1

    send it to me Martin22@iprimus.com.au I will sort and tell you how I did it.

    Not VB, just simple stuff...

    Step 2

    In future can be a button put on your tool bar, push and go... simple macro.

    Macro means, you record what you did, and play back the same....

    Martin

  3. #3
    Registered User
    Join Date
    Aug 2002
    Location
    Berkshire
    Posts
    1,476
    Rep Power
    11

    Re: Excel sort & delete question....

    Hi Martin,

    Thanks for the repy.

    I can't send you the spreadsheet as it contains sensitive client information and I'd be breaking reems of company rules if I did.

    I'm reasonably familiar with Macros. I just can't see how I can automate this process using a macro. Any explanation would be much appreciated.

    Cheers,

    Will

  4. #4
    Registered User
    Join Date
    Oct 2003
    Location
    Gold Coast, Austra
    Posts
    2,345
    Rep Power
    11

    Re: Excel sort & delete question....

    clue - it is a Vlookup or Hlookup standard formula. But not to worry, send it over.....

    Martin

  5. #5
    Registered User
    Join Date
    Aug 2002
    Location
    Berkshire
    Posts
    1,476
    Rep Power
    11

    Re: Excel sort & delete question....

    PS. Shouldn't you be in bed?

  6. #6
    Registered User
    Join Date
    Oct 2003
    Location
    Gold Coast, Austra
    Posts
    2,345
    Rep Power
    11

    Re: Excel sort & delete question....

    Hi Will

    strip out the sensitive stuff, then send. Save as...

    I will then sort and tell you how.

    Martin

  7. #7
    Registered User
    Join Date
    Oct 2003
    Location
    Gold Coast, Austra
    Posts
    2,345
    Rep Power
    11

    Re: Excel sort & delete question....

    Quote Originally Posted by Will View Post
    PS. Shouldn't you be in bed?
    I guess i should be in bed.... my mum always told me, if you are not in bed by 10 pm................ come home


    She also told me, safe sex is important..... To be safe, never tell them your address...............

  8. #8
    Registered User
    Join Date
    Oct 2003
    Location
    Gold Coast, Austra
    Posts
    2,345
    Rep Power
    11

    Re: Excel sort & delete question....

    I am a true Excel boffin, but cannot help unless I see the set up of the worksheet.

    Gaps and other things make a difference, so I need to see it.

    Martin

  9. #9
    Registered User David Franklin's Avatar
    Join Date
    Feb 2002
    Location
    London
    Posts
    3,426
    Rep Power
    14

    Re: Excel sort & delete question....

    Quote Originally Posted by Martin View Post
    I am a true Excel boffin, but cannot help unless I see the set up of the worksheet.

    Gaps and other things make a difference, so I need to see it.
    It might make more sense for Will to fill all the confidential fields with dummy data.

  10. #10
    Registered User
    Join Date
    Oct 2003
    Location
    Gold Coast, Austra
    Posts
    2,345
    Rep Power
    11

    Re: Excel sort & delete question....

    Quote Originally Posted by David Franklin View Post
    It might make more sense for Will to fill all the confidential fields with dummy data.


    After all I have zero interest in the content... Just looking at the challenge...

  11. #11
    Registered User
    Join Date
    Oct 2003
    Location
    Gold Coast, Austra
    Posts
    2,345
    Rep Power
    11

    Re: Excel sort & delete question....

    easy as Will.... Get rid of sensitive, replace with whatever... OR just trust me

    Send over,I will sort in a couple of minutes for you.

    You asked, you have now an option...

    Martin

  12. #12
    Registered User David Franklin's Avatar
    Join Date
    Feb 2002
    Location
    London
    Posts
    3,426
    Rep Power
    14

    Re: Excel sort & delete question....

    Quote Originally Posted by Martin View Post
    easy as Will.... Get rid of sensitive, replace with whatever... OR just trust me
    To be fair, it's not exactly a trust issue. If the data's confidential, it's confidential. You don't want to ever be in the position of having to answer "Yes" to the question "Did you send this data to someone unauthorised?". (You don't usually get to even say "...but I trust him").

  13. #13
    Registered User
    Join Date
    Aug 2002
    Location
    Berkshire
    Posts
    1,476
    Rep Power
    11

    Re: Excel sort & delete question....

    Just sent it to your email account.

    Thanks again,

    Will

  14. #14
    Registered User
    Join Date
    Aug 2002
    Location
    Berkshire
    Posts
    1,476
    Rep Power
    11

    Re: Excel sort & delete question....

    Quote Originally Posted by David Franklin View Post
    To be fair, it's not exactly a trust issue. If the data's confidential, it's confidential. You don't want to ever be in the position of having to answer "Yes" to the question "Did you send this data to someone unauthorised?". (You don't usually get to even say "...but I trust him").
    Tha's basically the issue. I've now sent Martin a sanitized copy which I hope will be enough.

    Cheers,

    Will

  15. #15
    Registered User
    Join Date
    Oct 2003
    Location
    Gold Coast, Austra
    Posts
    2,345
    Rep Power
    11

    Re: Excel sort & delete question....

    Quote Originally Posted by David Franklin View Post
    To be fair, it's not exactly a trust issue. If the data's confidential, it's confidential. You don't want to ever be in the position of having to answer "Yes" to the question "Did you send this data to someone unauthorised?". (You don't usually get to even say "...but I trust him").

    True (opps my text is too short - maybe this will drivil will put it over the limit)

  16. #16
    Registered User Msfab's Avatar
    Join Date
    Feb 2005
    Location
    PhD land - Yippeee
    Posts
    1,104
    Rep Power
    10

    Re: Excel sort & delete question....

    Quote Originally Posted by Will View Post
    Is there anyway of doing this automatically? Apart from the risk of human error, this is a process that I'm going to have to repeat quite often so I don't want to have to do this manually if possible.

    I think you need an IF statement.

    Heres one I was using for my data

    [quote=Excel equation]
    =IF(OR(C2="Ent45",C2="Ent42"),"PT11",IF(OR(C2="Ent 01",C2="Ent03"),"PT13A",IF(OR(C2="Ent05",C2="EntPT 4"),"PT4",IF(OR(C2="Ent08",C2="Ent09"),"PT6",IF(OR (C2="Ent48",C2="Ent51"),"PT9",IF(OR(C2="Ent10",C2= "Ent12"),"PT8","ERROR"))))))


    You could then sort the table (once you've got rid of the equation) and take what you want.
    Have fun working that lot out!

    Blame the mathematician if doesnt work!
    Last edited by Msfab; 23rd-May-2007 at 04:56 PM.

  17. #17
    Registered User
    Join Date
    Oct 2003
    Location
    Gold Coast, Austra
    Posts
    2,345
    Rep Power
    11

    Re: Excel sort & delete question....

    Quote Originally Posted by Msfab View Post
    I think you need an IF statement.

    Heres one I was using for my data




    You could then sort the table (once you've got rid of the equation) and take what you want.
    Have fun working that lot out!

    Blame the mathematician if doesnt work!
    I will pass on to MsFab.. she obvioously has a handle on this....

  18. #18
    Registered User Msfab's Avatar
    Join Date
    Feb 2005
    Location
    PhD land - Yippeee
    Posts
    1,104
    Rep Power
    10

    Re: Excel sort & delete question....

    Heres the example I was trying to post before!
    Attached Files Attached Files

  19. #19
    Registered User LMC's Avatar
    Join Date
    Jun 2005
    Location
    In the corner
    Posts
    4,508
    Rep Power
    12

    Re: Excel sort & delete question....

    Excel is my weak point in MS Office suite - but if I haven't misunderstood the original question Will, it strikes me that filters might be what you're looking for?

  20. #20
    Registered User
    Join Date
    Oct 2003
    Location
    Gold Coast, Austra
    Posts
    2,345
    Rep Power
    11

    Re: Excel sort & delete question....

    Quote Originally Posted by Msfab View Post
    Heres the example I was trying to post before!

    Much more important stuff....

    We now have a pet duck...

    It came into our front garden and is now in the back garden 1/2 acre of lawn.

    So we filled up a paddling pool and put out food...

    Duck is doing well...

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Jokes
    By TheTramp in forum Fun and Games
    Replies: 3144
    Last Post: 1st-October-2012, 03:31 AM
  2. excel
    By under par in forum Geeks' Corner
    Replies: 12
    Last Post: 8th-March-2007, 10:51 PM
  3. Swish 2008 - question
    By JiveLad in forum Social events
    Replies: 11
    Last Post: 21st-February-2007, 10:06 PM
  4. A Doggy Question
    By drathzel in forum Chit Chat
    Replies: 16
    Last Post: 27th-January-2007, 02:33 PM
  5. First Move Question
    By JiveLad in forum Intermediate Corner
    Replies: 12
    Last Post: 22nd-January-2007, 03:06 PM

Tags for this Thread

Bookmarks

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
  •