Excel - Match rows in another file

Navigation
2

The question is: The question is:

I have 2 csv files, one of them has only one email column with several lines of valid emails, I also have another file that has the same emails and some more, in this file there are other columns like Country, City, Name etc. .

My goal is to filter the second file all lines in which email is present the file of valid emails + invalid + info (country, city, etx ..) Like this:

Valid email file: 

email            |
------------------
[email protected]
------------------

Valid email file + invalid + info: 

email | name | city | country
------------------------------
[email protected] | Miguel | Porto | PT
------------------------------------------
[email protected] | Invalid | Lisbon | PT

The result should be all valid emails + info: 

email | name | city | country
------------------------------
[email protected] | Miguel | Porto | PT
------------------------------------------

Files contain thousands of lines, not possible to erase line by line. How can I do this? Thanks!

    
asked by anonymous 31.07.2017 / 18:23

1 answer

0

Hello, do you have to do this through VBA or do you want to do it for work and can it be by formulas?

If it can be done by formulas the solution is somewhat simple. It would just do an index-match or a simple Vlookup (procv) with an if for the second file.

In this way, the solution could be to put this formula below in the second file, in a column after "country" as the name you want (Valid e-mails / for example): 

=if(vlookup("célula onde está o e-mail";"no ficheiro 1 Coluna inteira onde está o e-mail";1;0)="célula onde está o e-mail";"Válido"-entre aspas mesmo;"Não Válido"-também entre aspas)

If you continue to have problems just let me know.

    
31.07.2017 / 19:01
Create a blur effect image on Xamarin.Android Function in jquery button disabled