Join data from two worksheets in Excel

Asked

Viewed 1,395 times

0

I have two spreadsheets in Excel.

In the first I have the following data, for example:

ID   | NOME
-----|----------
100  | Luiz
101  | Pedro
102  | Aline

On the second spreadsheet, I have:

ID   | CATEGORIA
-----|------------
100  | Livros
100  | Filmes
101  | Livros
101  | Games
101  | Móveis
102  | Filmes

That is, this second spreadsheet has Ids that repeat for each category. I would like to unite these two spreadsheets, so that it stays more or less like this:

ID   | NOME      | CATEGORIA
-----|-----------|-------------
100  | Luiz      | Livros
100  | Luiz      | Filmes
101  | Pedro     | Livros
101  | Pedro     | Games
101  | Pedro     | Móveis
102  | Aline     | Filmes

Is that a possibility? Thank you.

1 answer

1


Is there such a possibility?

Yes.


PROCV

In the second sheet include the desired column (NOME) and do the procv:

=PROCV(A2;Planilha1.A:B;2;0)

Being:

  • A2 - the cell containing the ID
  • Planilha1 - the worksheet containing the names
  • A:B - column selection A and B of the spreadsheet Planilha1
  • 2 - column index with the value you want to bring (column NOME)
  • 0 - informs that you want Excel to look for the exact ID

See also

For more information, examples and video, visit office page.


Note: I tested this command with Libreoffice. With Excel may have some subtle variation, but the principle is the same.

  • I will test here. In case, the two sheets have to be in the same file and in separate tabs or can be in separate files?

  • They can be in separate files. No problem. You can even do in the same spreadsheet, but it is very messy. Do it in separate spreadsheets even

  • By the way, this formula fills only one cell. After you type the formula press enter, then click again on that cell and a small black square will appear at the bottom right corner of the cell. Click, hold and drag down to fill the remaining cells.

  • The cells are getting "#NAME?".

  • 1

    Libreoffice.

  • Send a print of your spreadsheets, please

Show 1 more comment

Browser other questions tagged

You are not signed in. Login or sign up in order to post.