Join data from 2 sheets and point differences

Asked

Viewed 795 times

1

I have 2 excel spreadsheets, one has project management data from a fictitious company and another the same data from a fictitious factory:

PLANILHA 1: Empresa
------------------------------
Projeto História    Data    Esforço
PROJ-1  PJ1-1       jan/15  4
PROJ-1  PJ1-2       jan/15  8
PROJ-1  PJ1-3       jan/15  12
PROJ-1  PJ1-4       jan/15  40
PROJ-2  PJ2-1       jan/15  32
PROJ-2  PJ2-2       fev/15  2
PROJ-2  PJ2-3       fev/15  10
PROJ-4  PJ4-2       mai/15  6

PLANILHA 2: Fábrica
------------------------------
ID Projeto  ID Estoria  Data Início Fab Story Point
PROJ-1      PJ1-1           jan/15              2
PROJ-1      PJ1-2           jan/15              4
PROJ-1      PJ1-3           jan/15              6
PROJ-1      PJ1-4           fev/15              10
PROJ-2      PJ2-1           jan/15              32
PROJ-2      PJ2-2           fev/15              2
PROJ-2      PJ2-3           fev/15              10
PROJ-3      PJ3-1           jun/15              10

From these 2 spreadsheets I want to generate a third containing the following data:

Projeto | História | Data Empresa | Data Fábrica | Pontuação Empresa | Pontuação Fábrica

My goal with this is to identify divergences of information, there are data registered only in 1 of the 2 spreadsheets, data of the same history but with different date or score, for example.

The problem is I have no idea how and what to start with.

excel
  • 1

    Have you considered using the PROCV formula? If not, can I post a reply exemplifying the use.

  • Hello @Cantoni, you can post the answer, although I thought about PROCV I don’t know how I should apply it.

  • A question @Eduardosilva, this will be done only once or are you looking for a solution to automate this problem?

  • It’ll only be done once, @Cantoni.

  • 1

    @Eduardosilva believes that the solution to your problem is even with VBA, but anyway, here is an interesting solution if you have the patience to learn it: http://ambienteoffice.com.br/blog/ambientexl/#introducao

  • Ok, so I will post the solution considering that it is only once. There are a few manual steps, it will certainly solve your problem.

Show 1 more comment

1 answer

3


This problem can be solved quickly using the PROCV formula, however, to do so, it will be necessary to create an additional column that will serve as the primary key of your data. In addition, an additional step using a dynamic table will be necessary in order to extract only one record per primary key (it is an artensal way of doing a distinct in Excel).

By checking the sample data, it is possible to notice that a certain Project ID and History ID never repeat, that is, we have a composite primary key case.

Do the following:

  • Create the 3rd worksheet (the result worksheet) with the fields indicated in the question. In this worksheet include a field called KEY that will be the first field of the worksheet.

  • In each of the Manufactures and Enterprise spreadsheets, create a column called KEY, also as the first column of the spreadsheet. Place the following formula in the 1st cell (A2): =B2&";"&C2

  • Create the formula, replicate it in every column A. As can be observed, this column is the primary key that will be used in PROCV. The primary key is the concatenation of PROJECT+";"+ESTORIA (the ; will be useful later).

  • Important: As there are projects that are in the Factory, but are not in the Company and vice versa, it will be necessary an extra step, in order to extract the keys that will be placed in the 3rd spreadsheet (the result sheet).

  • To do this, create a 4th spreadsheet with only a column called KEY. Go back to the Company spreadsheet and copy/paste (only the values) all the contents of the KEY column in the 4th spreadsheet. I do the same for the Manufactures spreadsheet. Obviously, the data of the Factory will be after the Company. Therefore, the 4th spreadsheet will have all the primary keys of Company and Factory.

  • After this step, create a Dynamic Table and place the key field in the Rows of this table. Note that with this step, you will have a distinct of the keys of the Manufactures and Enterprise tables. Copy and paste this data from the dynamic table rows into the key column of the 3rd sheet;

  • Ready, now you have all your search keys. Simply therefore assemble your PROCV. In the columns that refer to the company, your PROCV will search in the company spreadsheet and vice versa. The PROCV search key is the KEY field that is in the 3 spreadsheets.

  • The only pending is, therefore, in the fields Project and Estoria, since a PROCV in them will not solve. For this, the ";".

  • In the Project field of the 3rd worksheet put the following formula: =LEFT(A2;SEARCH(";";A2;1)-1)

  • in the Estoria field of the 3rd worksheet put the following formula: =RIGHTHAND(A2;NÚM.CARACT(A2)-SEARCH(";";";A2;1))

  • The other fields, as stated above, are obtained by PROCV.

Browser other questions tagged excel

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