1 Attachment(s)
Re: Excel sort & delete question....
Quote:
Originally Posted by
Martin
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
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
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?
:yeah: - 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:clap:
HTH
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.
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