Share via

Tetris on excel

Anonymous
2018-03-20T22:41:19+00:00

Hi everyone

I wanted to know if it's possible to complete empty cells with other ones following a "tetris" condition.

E.g. if a row of 4 cells has the 3 last cells empty, then it should be replaced by values with those 3 cells of other row to complete the chain.

See below an example:

On the first row, right matrix you see orange juice by itself, i need to add on that first row the other 3 juices (apple-tangerine-orange cells) to complete the row. 

In this case we have two options to complete that row (seen in light blue), the chosen one should be the one with the least sum ($31,199.78). 

Therefore the completed row should look something like this:

Link is: 

http://www.mediafire.com/file/llkzqf4wra6bkn7/Tetris%20scenario.xlsx

Thanks!!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-03-22T05:35:25+00:00

    We appreciate your response. We'd like to gather more information regarding your concern, kindly answer the questions below:

    • Can you please tell us how did we identify to use the amount of supplier 7 to row 2 of the left matrix?
    • As shown on the right matrix the least amount that provided the Orange juice, is supplier 11, we'd like to know on how did we identify that supplier 1 sent the orange juice while supplier 7 sent the rest of the juices.
    0 comments
  2. Anonymous
    2018-03-21T03:58:38+00:00

    Hi Regile,

    Thanks for your help first of all. My question was not formulated in the best way. 

    For the empty cells I need to have the value in $, in other words go from this:

    to this:

    With the second condition, as you can see on the left matrix you have 2 rows with the last three (juices) cells and the first one empty (orange): supplier 7 and 30. So we have to choose only one of these suppliers. The chosen one has to be the one with the total minimum cost. 

    Supplier 7 last three cells sum $31.199

    Supplier 30 last three cells sum $34.957

    So we have to choose supplier 7. 

    Basically the solution will say: orange juice is sent by supplier 1 and the rest of the juices are sent by supplier 7.

    Hope I explained myself, any doubt let me know please.

    Thanks.

    0 comments
  3. Anonymous
    2018-03-21T03:44:09+00:00

    Hi Diego,

    You may use the formula below, for the rows that has the last 3 cells empty:

    Note: Make sure to edit the values depending on the cell.

    =(IF(C30<0,"",IF(C30>0,$C$2,IF($C30<=0,$C$2))))

    Also, can you please elaborate your concern for the second condition, "In this case we have two options to complete that row (seen in light blue), the chosen one should be the one with the least sum ($31,199.78)."

    Keep us update with the status of the issue.

    0 comments