Dear All,
I have a matrix formula copy values from one column to another column in an orderly manner, but omitting the empty cells:
However, when copying the data, the formula omits the first value ... What is failing in my formula?HTML Code:(B2: B300 <>), and if (B2: B300) is the same as the other, ; FILA (B2: B300)); ROWS (C $ 1: C2))));
Attached the file. Thank you very much for your support
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.
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
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
you are welcome.
upload a file with desired output.
manually insert the values what the formulas are expected to return.
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
ooops.
try this array (CSE, matrix, etc) formula
google is your best friend.PHP Code:=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)))))
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
Dear Mancubus,
Please, check the file. I tried to explain myself better.
Thank you very much
Marcela
did you try the formula in post #5?
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
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
argument separator is ; and not ,.
ok.
google says
array formulas must be confirmed with Ctrl+Shift+Enter, not just Enter.PHP Code: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)))))
you dont need + sign at the beginning of the formulas. = sign is enough.
Last edited by mancubus; 04-10-2017 at 12:18 PM. Reason: typo
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
That´s great, thanks Macubus
you are welcome.
please mark the thread as solved from Thread Tools (above the first post, on the right)
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
Posting Permissions
Reply With Quote