Clean a database using a list

Hello,
I'm trying to simplify a database I got, of the follow form. Consider that each line is the descrition of an electoral funding bank transfer. Each candidate can have dozens.
election code; data; hour; contract; Canditate ; transfer ; value ; financer
133; ............................................John ; 77987 ; 800$ ; Big company
133; ............................................John ; 79619 ; 1900$ ; Fat company
133; ............................................Mark ; 87681 ; 300$ ; Little company
(i put these unuseful variables before, as it is in the real file)

I have 130.000 lines like that because the database is about candidates. But Mark has not been elected.
I have an other database, with 550 names: the elected ones.

Is there an automatic process, or VBA thing (i am so bad at VBA....) to clean the huge list ? I would like to delete all the lines where CANDIDATE is a name that does not appear in the other list. Do you see what i mean ?

Thank you so much for your help,

best

Hi Ayadin,

The attached workbook has code in it that removes the names from the Candidates sheet that are not in the Elected sheet, which I think is was you are describing. You can use this as an example and hopefully it gives you some guidance.

Thanks,

Dan

Hi Ayadin,

The only code you really need is the macro called "CleanCandidates". It could be modified to fit your specific situation. The ones that list all then names were just in there to reset the sheets for the example.

I posted the pertinent code below with some comments on what would need to be modified for your specific case. It is set up to delete the entire row where a candidate name is not elected.

Please Login or Register  to view this content.

thanks ! great ! i did it !

Now i have an other question... My file is like this now:
elected1 - 900$ received - big company
elected 1 - 750$ etc
elected 1
elected 2 - 670$ - little company
eletected 3

There are 513 différent "elected"

I have an other file, of 513 lines, i have additional data on these guys:
- which fraction of the party they are in
- which alliance they have
- the number of trials and condamnation they endure (yes...i'm working on a corrupted country and these numbers tend to infinity...)
- their patrimony
etc
the form is:
ELETECTED 1 - v 1......vn

elected1 - 900$ received - big company ++++++ ELETECTED 1 - v 1......vn
elected 1 - 1800$ received - big company2 +++ ELETECTED 1 - v 1......vn
elected 2 - 670$ - little company ++++++++++ ELETECTED 2 - v 1......vn



I'm soryr i dont have a clearer way to express what i'm trying to do. DO you think there is a way to do it ? thank you very much. I'll probably have to do this kind of manipulation often because i often get new data file with additional variables.


THANK YOU ! and good weekend !

Hi,
I found a way to do it: i'm adding values thanks to Search V
in page 1 i have infos like
name - v1 v 2
and in paeg 2 i have:
name v3 v4
because variables come from different sources. So with searchV i manage to important the needed variables to my page1, which is where i have all the data.

do you maybe know an other way to do it ?