Get a value from a set of rows in Excel

Navigation
2

I have a table in Excel that represents the purchase orders. Ex: 

pedido | linea ped. | estado línea   
-----------------------------------
1      | 1          | Cumplido     
1      | 2          | En progreso  
2      | 1          | Cumplido

And I need to add a column that is calculated automatically, through formulas, the status of the order: 

pedido | línea ped. | estado línea | estado pedido
-------------------------------------------------------
1      | 1          | Cumplido     | En progreso
1      | 2          | En progreso  | En progreso
2      | 3          | Cumplido     | Cumplido

In the case of example, the order 1 has the states Met and In progress (for lines 1 and 2 respectively) . In other words, one part of the order arrived and the other did not. I need the order status column to condense that information and tell me that it is still In progress (since one of the materials has not yet arrived.) In the case of the 2 order, It only has one line (a material), which was already complied, so the status of the entire order was fulfilled.

When the line 2 of the order 1 arrives, I'll say that it's Met and, in that case, the status of the entire order should pass a Met (since both lines are already fulfilled).

Ideally I would like to be able to add the column in the same table, but if it can not be, it could be in a separate one.

    
asked by malvarez 21.02.2017 в 13:21
source

4 answers

3

I leave the answer here of the help that I received in StackOverflow in English:

Use COUNTIF.SI.CONJUNTO together with SI: 

=SI(CONTAR.SI.CONJUNTO(A:A,A2,C:C,"En progreso"),"En progreso","Cumplido")

Being the column with the orders and C with the status of the line items.

If there are more states, as it is my case, it is only a matter of nesting the SI.

    
answered by 21.02.2017 / 16:40
source
0

What I usually do is create a sheet with values as a teacher. That is, you would have a sheet with the data of this type: 

código | estado  
---------------------
A      | En progreso
B      | Cumplido

Then you just have to search the page where you want to show the corresponding value. For example, like this: =BUSCAR(A1;Hoja2!A:A;Hoja2!B:B)

I hope it serves you.

    
answered by 21.02.2017 в 13:27
0

With this formula it will serve you. 

=SI(A1="A";"En progreso";"Cumplido")

You just have to substitute the A1 for your column and use the formula in the cells that you want the value to appear.

    
answered by 21.02.2017 в 13:29
0

Another option is using the SI formulas.

Example of using the SI formula 

=SI(A1="A";"En progreso";"Cumplido")

The SI function works with the following parameters:

  

YES (logical test, true value, false value)

logical test : It is the field to evaluate. You will have to match it to what you consider to be true or false, in this case equal to "A". If it were type number goes without quotes.

true value : this is what will return the function if the condition is met.

false value : it will be returned by the function if the condition is not met.

I hope it serves you.

    
answered by 21.02.2017 в 13:32
C ++ Initialization of an arrangement of structures How to connect phonegap / Apache Cordova to SQL Server?