Formula - receive values from one cell range to another, except empty cells

Asked

Viewed 478 times

3

I have a range of cells containing empty cells and others not, arranged in a row and need a formula that receives these values in a column, except empty cells, below an example of what I need, adding that these ranges are named "Blanksrange" and "Noblanksrange"

inserir a descrição da imagem aqui

excel
  • 1

    Hello. Can you edit the question to provide a visual example of what you want to do? It’s not very clear.

  • ok! I’ll simplify.

  • @Luizvieira saw on the tag and did not find any other question regarding formula. Therefore I commented that it could be outside the scope.

  • 1

    @Diegofelipe has some. By the way, this discussion about inside or outside the scope is very common. The last time I had it was in this question: http://answall.com/questions/105277/get-value-of-scale%C3%B5es

  • 1

    Please use Markdown to format your posts, HTML gets very confused: http://answall.com/editing-help

  • 3

    You have removed from the question the statement that BanksRange was a list. Also, it did not offer an example with data about what it wants to do. The way the question is, someone has to spend time analyzing your formula to try to understand the problem (if your formula is correct). If your problem is just moving the data between horizontal x vertical layouts, there is the "transpose" option in the "paste special" dialog box. Please be clearer on your problem.

  • 1

    @Luiz Vieira As suggested, I inserted an image as an example of what I needed; I had already tried to use the "transpose" option, but in my case it didn’t work; I analyzed the formulas I posted as an example and I found the solution. I apologize for the mistakes I made. Thank you.

  • 1

    No problem. I saw the amendment and I voted to reopen it. Now, if you found the solution yourself, don’t edit the question to include it in the body of the question. Add an answer yourself (and explain how this response works, to help other readers in the future!). Currently it is not possible to add an answer because the question is suspended. But if it is reopened, please proceed this way ok?

  • Okay! , I’ll do it.

Show 4 more comments

1 answer

2

Before posting my question, I had already done searches on the web and found this formula next to what I needed, but the intervals are column to column and as explained in the question, I needed a row to column, I tried to adapt it but was not succeeding, which is why I requested help on this site. Later I started to analyze the formula I had downloaded and found that it was enough to change the terms of the formula "ADDRESS(núm_lin;núm_col)", and it worked out.
Formula example: 

{=SE(LIN()-LIN(NoBlanks)+1>LINS(BlanksRange)-CONTAR.VAZIO(Blanks);"";INDIRETO(ENDEREÇO(MENOR((SE(Blanks<>"";LIN(Blanks);LIN()+LINS(Blanks)));LIN()-LIN(NoBlanks)+1);COL(Blanks);4)))}

Modified formula: 

{=SE(LIN()-LIN(NoBlanks)+1>COLS(Blanks)-CONTAR.VAZIO(Blanks);"";INDIRETO(ENDEREÇO(LIN(Blanks);MENOR((SE(Blanks<>"";COL(Blanks);COL()+COLS(Blanks)));LIN()-LIN(NoBlanks)+1);4)))}

See the image:

inserir a descrição da imagem aqui

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.