Cleaning a database with a list

Solved
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,

1 answer

  • Votes
  • Date
  • Descending date
  1. 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
    1. Hello! Thank you for your response. I tried, but there’s something I must be doing wrong because it’s not working. Before, I had a (value) error, and now the formula copies without doing anything. I will try again. The names are in column E in sheet1 and A in sheet2, indeed.
    2. I don't know why, but it worked and I only have 0s. However, there should be 1s (I checked manually).
    3. @TOT127Maybe the names are not written the same way; there could be a space after the name, for example, invisible at first glance, but without the file, it's impossible to say more.
      Message me privately if you want for confidentiality, an excerpt of your file (a hundred lines from the first sheet is enough, and the list doesn’t have to be complete on the second sheet but should include a few names from the first; I'll take a look.
    4. Understood, I'll do that. Thank you!
    5. Hello... It's me again with my problems. Now I have a list of names in sheet 1 column A. 510 names. And a list of names, about 500 but 80% the same, in sheet 2 column A, accompanied in column 2 by a list of corresponding nicknames. Is there a way to automatically transfer the nicknames from sheet 2 to sheet 1 in front of the names indicated in sheet 2 column 1? There will be 20 to do by hand or 40 but that's okay... Can I modify the formula you made in this sense?