Cleaning a database with a list
Solved
TOT127
Posted messages
1045
Status
Member
-
TOT127 Posted messages 1045 Status Member -
TOT127 Posted messages 1045 Status Member -
Hello,
I am currently simplifying a database, which has the following format. Consider that each line is the description of a bank transfer for electoral funding (public data). Each candidate can have dozens of funding sources, resulting in dozens of lines.
election code; data; time; contract; candidate, transfer, amount; funder
133; ............................................ John; 77987; $800; Big Company
133; ............................................ John; 79619; $1900; Huge Company
133; ............................................ Mark; 87681; $300; Small Company
(I put these unnecessary variables, like election code, at the beginning, as in the file)
I have 130,000 lines like this, because the database is about candidates, whether elected or not. But Mark was not elected! So I have 90% of unnecessary people in the database, and obviously, the creator of the database did not think to include an "elected" variable.
However,
I have another database with 550 names: those elected.
Is there an automatic process, or VBA (I am so bad at VBA…) to clean up the list?
I would like to delete all the lines where the CANDIDATE is a name that does not appear in the other list. Do you see what I mean?
Thank you very much for your help,
I am currently simplifying a database, which has the following format. Consider that each line is the description of a bank transfer for electoral funding (public data). Each candidate can have dozens of funding sources, resulting in dozens of lines.
election code; data; time; contract; candidate, transfer, amount; funder
133; ............................................ John; 77987; $800; Big Company
133; ............................................ John; 79619; $1900; Huge Company
133; ............................................ Mark; 87681; $300; Small Company
(I put these unnecessary variables, like election code, at the beginning, as in the file)
I have 130,000 lines like this, because the database is about candidates, whether elected or not. But Mark was not elected! So I have 90% of unnecessary people in the database, and obviously, the creator of the database did not think to include an "elected" variable.
However,
I have another database with 550 names: those elected.
Is there an automatic process, or VBA (I am so bad at VBA…) to clean up the list?
I would like to delete all the lines where the CANDIDATE is a name that does not appear in the other list. Do you see what I mean?
Thank you very much for your help,
1 answer
-
Hello
A possibility without VBA
Assuming the candidates are in column E and the list of elected officials is in Sheet2 in column A, add a column in the first sheet where in row 2 you put the formula =COUNTIF(Sheet2!A:A,C2)
Then drag the formula down to the bottom of your sheet (double click on the small black square at the bottom right of your cell)
This counting formula should return 0 if the name does not exist in the list of elected officials and 1 otherwise
Then just filter the table on this column by unchecking the value 0
Finally, copy the filtered table and paste it into another sheet using Paste Special, Values only
Best regards
Via
--
"Imagination is more important than knowledge." A. Einstein
