Add data from 2 worksheets and point differences

Question

Add data from 2 worksheets and point differences

Navigation

#1 by (3 votes)

1

I have 2 excel spreadsheets, one has project management data from one fictitious company and the other 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 worksheets I want to generate a third one 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 worksheets, data of the same story but with different date or punctuation, for example.

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

excel

asked by anonymous 14.10.2015 / 19:13

1 answer

Policy as function parameter? How to run a program in java daily at a certain time automatically [duplicate]

score 3 · Accepted Answer

This problem can be solved quickly by using the PROCV formula, however, for this you will need to create an additional column that will serve as the primary key for your data. Also, an additional step using a PivotTable will be required in order to extract only one record per primary key (it's an artful way of doing a distinct in Excel).

By checking the sample data, you can see that a given Project ID and History ID never repeat, ie 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 1st field of the worksheet.
In each of the Works and Company spreadsheets, create a column called KEY, also as the 1st column of the worksheet. Put the following formula in the 1st cell (A2): = B2 & ";" & C2
Once the formula has been created, replicate it in every column A. As you can see, this column is the primary key that will be used in PROCV. The primary key is the concatenation of the PROJECT + ";" + ESTORIA (the; will be useful later).
Important : Since there are projects that are in the factory, but are not in the Company and vice versa, an extra step is required in order to extract the keys that will be placed in the 3rd spreadsheet (the result spreadsheet).
To do this, create a 4th worksheet with just one column called KEY. Go back to the Company spreadsheet and copy / paste (values only) the entire contents of the KEY column into the 4th worksheet. I do the same for the Worksheet. Obviously, the data of the Factory will remain after the Company. Therefore, the 4th worksheet will have all the primary keys of Company and Factory.
After completing this step, create a PivotTable 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 Fabrica and Empresa tables. Copy and paste this data from the rows of the table into the key column of the 3rd worksheet;
Done, now you have all your search keys. It is enough, therefore, to mount 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 worksheets.
The only pending is therefore in the fields Project and Estoria, since a PROCV in them will not solve. For this, the ";" was concatenated.
In the Project field of the 3rd worksheet, enter the following formula: = LEFT (A2; SEARCH (";"; A2; 1) -1)
The other fields, as stated above, are obtained by PROCV.