Page 2 of 2 FirstFirst 12
Results 21 to 23 of 23

Thread: Excel sort & delete question....

  1. #21
    The Dashing Moderator
    Join Date
    Mar 2003
    Location
    Midlands
    Posts
    3,556
    Rep Power
    12

    Re: Excel sort & delete question....

    Quote Originally Posted by Martin View Post
    clue - it is a Vlookup or Hlookup standard formula.
    Or use Match and Index which is pretty much equivalent- in Will's case you only need the match formula.
    Quote Originally Posted by Msfab View Post
    Heres one I was using for my data

    =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"))))))
    This approach works if you only have a small number of things to check, but as the list gets bigger, it gets more cumbersome to manage the If formulae. Formulae like VLookup and Match and Index work with many more cells. See attachment for equivalent formulae
    Quote Originally Posted by LMC View Post
    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?
    - but only if Will doesn't mind the original data still being there in the sheet - depends if he wants rid of it for good. But filtering is handy and may be helpful here.

    What I'd do is this:
    • Add a list of surnames somewhere - I've put it on a separate sheet so as not to get in the way of all the sorting/ filtering/ etc. of rows
    • Add an extra column to hold the formula. Formula says something like:
      =IF(ISERROR(MATCH(C6,SurnamesToMatch,0)),"DeleteTh isRow","KeepThisRow")

      Where C6 is the cell with the surname in, and SurnamesToMatch is the column of 12 surnames. I've used Ctrl-f3 to name my column as "SurnamesToMatch" 'cos it's neater. I could've named the column with C3 in as well - makes for more readable formulae.
    • Copy this formula down all the data rows
    • You should now have a bunch of data rows labelled as DeleteThisRow or KeepThisRow. Sort on this column to put all the deletable rows together, then delete them
    • Putting this into a macro is trickier as you have to deal with a spreadsheet where you may not know how many rows to paste the formula into. It's possible but will take more time - working out the last row is the trickiest bit. (Try recording a macro and see what the Crtl-End key combbo does
    • If I was doing a macro I probably wouldn't bother sorting, just do a "for" loop working down all the cells in my new column, and deleting the row depending on the value in that cell. In Excel macros you can loop through a range of cells which is handy


    HTH
    Attached Files Attached Files
    Love dance, will travel

  2. #22
    purplehyacinth
    Guest

    Re: Excel sort & delete question....

    Seems to me that an IF (logic) function is one way forward. Msfab's is a good example (my own personal preference would be to configure a logic function of this kind, but you do need to have some idea of what you are doing to write the function in the first place.). You can use these formulae for large spreadsheets (back in the 1990s - 1994 to be precise - I was using logic functions on 10,000 row spreadsheets in whatever the then-current version of excel was).

    If you are prepared to do things manually, you can use the datafilter tool (basically, select your "names" column, click on "Data" then "autofilter". You will then see a "down button" appear on the column you selected . Click on that down button and you should have a list containing each different entry displayed once. If you click on e.g. a name on that list, it will bring you up all the rows where that particular name appears on the column you selected. You can then leave/delete the data in that row as you wish.
    Last edited by purplehyacinth; 23rd-May-2007 at 11:49 PM.

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

    Re: Excel sort & delete question....

    Just want thank Mr Martin, Mr David Franklin, Mr Davidy, Miss Purple Heather, Miss MSFAB, LMC, (hope I've not missed anyone out!) for all your help and input. Between all the repsonses I think I've cobbled together a solution, though nowhere near as clever as it could be judging by what's been suggested (alot of which has gone clean over my head!).

    Will

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
  •