Attn Excel Guru`s

[Markz]

Ok here is my dilema, I have two lists of data 1 is very large, both on Excel of clients. I want to remove the people who are no longer in Country from my main list, the bigger one.

I have separted the lists into two, 1 is the total amount and the other is people I want to remove.

I could do this manually but it would take a bloody long time, any ideas people?

Thank!

 

[RetroAndy]

Ok here is my dilema, I have two lists of data 1 is very large, both on Excel of clients. I want to remove the people who are no longer in Country from my main list, the bigger one.

I have separted the lists into two, 1 is the total amount and the other is people I want to remove.

I could do this manually but it would take a bloody long time, any ideas people?

Thank!

I aint too clear what your wanting. Is there anyway of telling that they are no longer in the country?

Andy

 

[RetroAndy]

Oh better still mark if the data is not confidential you could msn it me and i will have a quick look. Am only here till 5 though

Andy

 

[Markz]

I aint too clear what your wanting. Is there anyway of telling that they are no longer in the country?

Andy

I`ve done that already and have that list, call it list A, How do i then remove them from the main list call it B that they are sitting in, bearing in mind it is 30,000 odd?

 

[brixtonite]

errr

still not too clear what you have done Mark pal - sounds like it can be done tho - I know a few Excel tricks (Excel, bane of my life), but I'm shooting from here now to get plane back to UK.

 

[Markz]

Lol maybe i`m not making it clear enough, its clear to me cause i understand it like.

Scenario:

Master list A contains all names.

List B contains only the names I want to remove from list A.

Quickest way? Other than manually doing it.

 

[RetroAndy]

I`ve done that already and have that list, call it list A, How do i then remove them from the main list call it B that they are sitting in, bearing in mind it is 30,000 odd?

Gonna have to concede on this one mate. Not sure about that but i bet there will be a way. Try Amy (pixie) she is good with excel

Andy

 

[johns_ar]

sort the master list into country order (presuming youve got a diff column for country)

then theres bound to be big chunks of them from the same country so just remove them in chunks

im sure you could write a formula tho but excel is a twat and ive never bothered with it - something like if col b = anything but UK clear the cell

im certain u can do something like that - google is your friend

youll defo get answer off there - 100%

 

[Markz]

sort the master list into country order (presuming youve got a diff column for country)

then theres bound to be big chunks of them from the same country so just remove them in chunks

im sure you could write a formula tho but excel is a twat and ive never bothered with it - something like if col b = anything but UK clear the cell

im certain u can do something like that - google is your friend

youll defo get answer off there - 100%

Its not done by Country name I just used that as an example, its simply just peoples emails.

Andy came up with a pretty good idea i`m going to give a go, still manual graft though, any more ideas more than welcome.

I`ll check google now.

Ta.

*Edit - The powers of Google revealed the following site, The Office Experts Forums Here

Pretty sure i`ll get the answer here, might even find a saucy secretary who knows

 

[wildpitch]

I've probably got an excel macro for this already mate, or at least one that's 90% there - I used to have to do similar exercises with mailing lists, ie list 1 = emails already subscribed, list 2 = people unsubscribing. It's simple enough what you want to do anyway. I'm not back in the office till Tuesday so if you've not cracked it by then let me know.

 

[Markz]

I've probably got an excel macro for this already mate, or at least one that's 90% there - I used to have to do similar exercises with mailing lists, ie list 1 = emails already subscribed, list 2 = people unsubscribing. It's simple enough what you want to do anyway. I'm not back in the office till Tuesday so if you've not cracked it by then let me know.

Ah nice 1 u got it spot on what i need to do, i knew there was a way, let me know plz appreciated!!!

I`ve got a code off that other board but never done like that before.

 

[Phil]

If you've not managed to get it sorted, send me a PM, and I'll take a look at them for you....

 

[Markz]

Finally managed to get this little brain teaser sorted with some help, you needed to insert a code into the macro which was the hard part but then it was sorted at the press of a button

I was doing it manually at first which was taking forever, took me an hour just to do the names beggining with a.

Thanks for all the ideas anyhow, if anyone might for some bizzare reason need the same doing let me know and i`ll send you the sample.

 

[brixtonite]

macro... aye

you can also use the MATCH function

if value in column A equals a value in column B, then return a value ("1"), then use filter function

 

[Jimmy]

I'll wirte you a bit of VBA to delete the entries if you want, need you to mail me the file thought, I'm several days late but love this sort of shite.

Cheers

 

[Markz]

I'll wirte you a bit of VBA to delete the entries if you want, need you to mail me the file thought, I'm several days late but love this sort of shite.

Cheers

Cheers Jimmy but its all done now with the macro