I have a question that I cannot solve. I have a table with 4 columns:
License plates can appear several times repeated. I need to make it go through the rows and group (add) the value of hours and punches by license plate. But I also need you to check the aisle and store that aisle where the hours value is higher.
That is, for license plate 12239 this should be stored:
12239 | M3/M4 | 2080 | 5.96 |
I accept any kind of help because I'm really stuck...
Thanks.
Taking into account that you are using Tables, with formulas it would be as follows.
First you have to copy the data from the license plate column and paste it in another sheet, then use the " " option
Eliminar duplicados
from the " " menuDatos
to get the unique list of license plates.To obtain the corridor with the most hours of each Enrollment, use the following matrix formula.
The key in this formula is the combination of the functions
COINCIDIR, MAX, SI
, with these the position of the aisle with the most hours is obtained, but which in turn is related to the Enrollment on duty, that position is the one that the function needsINDICE
to return the data.Remember that array formulas are entered by pressing the key combination
CONTROL+SHIFT+ENTER
after typing or pasting it, normal formulas are entered by pressing Enter.Once the formula is entered, it will be seen with braces at the beginning and end.
https://support.office.com/es-es/article/crear-una-f%C3%B3rmula-de-matrix-e43e12e0-afc6-4a12-bc7f-48361075954d
Total Chopped.
Total hours.
These last two formulas are normal (not matrix).
This can help you, if, as in the example, the license plates are not repeated more than 2 times, otherwise you would have to look at some other way: