PDA

View Full Version : Excel sort & delete question....



Will
23rd-May-2007, 04:00 PM
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?

Martin
23rd-May-2007, 04:05 PM
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

Will
23rd-May-2007, 04:08 PM
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 :cheers:

Martin
23rd-May-2007, 04:08 PM
clue - it is a Vlookup or Hlookup standard formula. But not to worry, send it over.....

Martin

Will
23rd-May-2007, 04:09 PM
PS. Shouldn't you be in bed?

Martin
23rd-May-2007, 04:11 PM
Hi Will

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

I will then sort and tell you how.

Martin

Martin
23rd-May-2007, 04:14 PM
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...............

Martin
23rd-May-2007, 04:21 PM
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

David Franklin
23rd-May-2007, 04:23 PM
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.

Martin
23rd-May-2007, 04:25 PM
It might make more sense for Will to fill all the confidential fields with dummy data.

:yeah: :yeah: :yeah:

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

Martin
23rd-May-2007, 04:32 PM
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

David Franklin
23rd-May-2007, 04:37 PM
easy as Will.... Get rid of sensitive, replace with whatever... OR just trust meTo 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").

Will
23rd-May-2007, 04:38 PM
Just sent it to your email account.

Thanks again,

Will

Will
23rd-May-2007, 04:40 PM
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

Martin
23rd-May-2007, 04:41 PM
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)

Msfab
23rd-May-2007, 04:51 PM
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="Ent01",C2="Ent03"),"PT13A",IF(OR(C2="Ent05",C2="EntPT4"),"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!:rolleyes:

Blame the mathematician if doesnt work!

Martin
23rd-May-2007, 04:54 PM
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!:rolleyes:

Blame the mathematician if doesnt work!

I will pass on to MsFab.. she obvioously has a handle on this....:love:

Msfab
23rd-May-2007, 04:58 PM
Heres the example I was trying to post before!

LMC
23rd-May-2007, 05:07 PM
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?

Martin
23rd-May-2007, 05:09 PM
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... :grin:

DavidY
23rd-May-2007, 11:37 PM
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.
Heres one I was using for my data

=IF(OR(C2="Ent45",C2="Ent42"),"PT11",IF(OR(C2="Ent01",C2="Ent03"),"PT13A",IF(OR(C2="Ent05",C2="EntPT4"),"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 :)
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)),"DeleteThisRow","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

purplehyacinth
23rd-May-2007, 11:45 PM
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.

Will
26th-May-2007, 11:14 PM
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