K-ESIMO MATRIX FORMULA

[mtrilce]

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]

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]

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]

you are welcome.

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

[mancubus]

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]

Dear Mancubus,

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

Thank you very much

Marcela

[mancubus]

did you try the formula in post #5?

[mtrilce]

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]

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]

That´s great, thanks Macubus

[mancubus]

you are welcome.
please mark the thread as solved from Thread Tools (above the first post, on the right)