PDA

View Full Version : [SOLVED] K-ESIMO MATRIX FORMULA



mtrilce
04-09-2017, 10:19 PM
Dear All,


I have a matrix formula copy values from one column to another column in an orderly manner, but omitting the empty cells:



(B2: B300 <>), and if (B2: B300) is the same as the other, ; FILA (B2: B300)); ROWS (C $ 1: C2))));

However, when copying the data, the formula omits the first value ... What is failing in my formula?


Attached the file. Thank you very much for your support

mancubus
04-10-2017, 12:35 AM
compare COMPROMISO!C2 to ESTRATÉGICOS!C2 to see what the formula is missing.

correct the formula in ESTRATÉGICOS!C2 and copy the new formula below cells.

mtrilce
04-10-2017, 01:03 AM
Dear Mancubus,

Thanks very much for your answer... Unfortunately I can not solve my problem yet. I have the same error on both sheets. COMPROMISO as well ESTRATEGICO. The formula is the same, so BOTH are failing... I have checked many times and did not detect what I am doing wrong. I will appreciate your help a lot

Best regards

Marcela

mancubus
04-10-2017, 01:23 AM
you are welcome.

upload a file with desired output.
manually insert the values what the formulas are expected to return.

mancubus
04-10-2017, 01:42 AM
ooops.

try this array (CSE, matrix, etc) formula


=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(B:B,SMALL(IF($B$2:$B$300<>"",ROW($B$2:$B$300)),ROWS(C$1:$C1)))))

google is your best friend.

mtrilce
04-10-2017, 07:27 AM
Dear Mancubus,

Please, check the file. I tried to explain myself better.

Thank you very much

Marcela

mancubus
04-10-2017, 07:59 AM
did you try the formula in post #5?

mtrilce
04-10-2017, 08:15 AM
I tried, but because of different languages (I think), my computer did not recognize it...

But I have good news. I changed just one little thing and formula works all right: changed (C$4:C5) by (C$5:C5). So now it is like this:

={+SI.ERROR(SI(FILAS(C$5:C5)>CONTARA(B5:B303)-1; ""; INDICE(B:B; K.ESIMO.MENOR(SI(B5:B303<>"";FILA(B5:B303));FILAS(C$5:C5))));"")}

Thanks a lot for your support Mancubus

Best regards

Marcela

mancubus
04-10-2017, 10:49 AM
argument separator is ; and not ,.
ok.

google says


in English
=LOOKUP(REPT("z";255);CHOOSE({1;2};"";INDEX(B:B;SMALL(IF($B$2:$B$300<>"";ROW($B$2:$B$300));ROWS(C$1:$C1)))))

=

in Spanish
=BUSCAR(REPETIR("z";255);ELEGIR({1;2};"";INDICE(B:B;K.ESIMO.MENOR(SI($B$2:$B$300<>"";FILA($B$2:$B$300));FILAS(C$1:$C1)))))


array formulas must be confirmed with Ctrl+Shift+Enter, not just Enter.

you dont need + sign at the beginning of the formulas. = sign is enough.

mtrilce
04-10-2017, 11:11 AM
That´s great, thanks Macubus

mancubus
04-10-2017, 12:23 PM
you are welcome.
please mark the thread as solved from Thread Tools (above the first post, on the right)