Aug 27, 2019
Convert a formula into Arrayformula.
I am having hard times converting a column full of formulas into an Arrayformula and would love to get some help please.
The column in question is column C. The current formulas there (starting from C4) basically populates the number displayed in column B down the line until I type something in column D.
In other words, the number in column B appears only when something is entered in column D. When a number is added to column B, column C pick this up and starts duplicating this along the line.
The column in question is column C. The current formulas there (starting from C4) basically populates the number displayed in column B down the line until I type something in column D.
In other words, the number in column B appears only when something is entered in column D. When a number is added to column B, column C pick this up and starts duplicating this along the line.
I hope this makes sense (probably not), please have a look at the sheet. If someone could help converting column C into Arrayformula I would really appreciate it!
https://docs.google.com/spreadsheets/d/1Qpk4t5fnlej4BcFgLzRpshlooPdwnmns02NdZm-4pDE/edit?usp=sharing
https://docs.google.com/spreadsheets/d/1Qpk4t5fnlej4BcFgLzRpshlooPdwnmns02NdZm-4pDE/edit?usp=sharing
Please let me know if something needs to be clarified!
Thank you in advance,
Vladlen
Details
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
I made a copy of your template tab and put this in cell C4:
=ARRAYFORMULA(VLOOKUP(ROW(C4:C),FILTER({ROW(B4:B),B4:B},B4:B<>""),2,TRUE))
Does that work like you wanted?
If not, can you share where/how it went wrong?
Original Poster Vladlen Vasilcenko marked this as an answer
All Replies (6)
I made a copy of your template tab and put this in cell C4:
=ARRAYFORMULA(VLOOKUP(ROW(C4:C),FILTER({ROW(B4:B),B4:B},B4:B<>""),2,TRUE))
Does that work like you wanted?
If not, can you share where/how it went wrong?
Original Poster Vladlen Vasilcenko marked this as an answer
Aug 27, 2019
Thank you for the quick response.
The formula works exactly how it should! Could you please explain how you constructed this? Just so that I understand it. I understand why vlookup is there but struggling to get my head around the filter part.
Anyway, I am very grateful for your help! Thank you.
Anyway, I am very grateful for your help! Thank you.
Kind Regards,
Vladlen
I can provide a breakdown of how it works but it would be helpful to have access to the file again to do that.
In general, it takes advantage of the "is sorted" switch in VLOOKUP to provide the previous response. It also uses curly brackets, {}, to create the custom range of ROW | Value for column B which is what VLOOKUP uses to find the value. The FILTER is only there to remove the rows where there is no defined value in column B.
When you've received a response that answers your question, please observe these forum courtesies:
- Leave your demo sheet shared as part of this solution's archive (you can change it to "can view" if you want to but I haven't seen any vandalism here). That will allow others to learn from the solution provided. Note that G. Sheets files do NOT use any of your storage space.
- Either click the Helpful button in your notification email for the main post that helped solve your issue (not just the most recent one) OR click the Recommend button on the post that best addressed your question.
Aug 28, 2019
Thank you for the reply!
I will leave the file opened for others to view.
https://docs.google.com/spreadsheets/d/1Qpk4t5fnlej4BcFgLzRpshlooPdwnmns02NdZm-4pDE/edit?usp=sharing
Kind Regards,
Vladlens
Thank you, Vladlens!
Do you need me to provide more breakdown of the formula still or is the brief description from yesterday enough?
Aug 28, 2019
The description provided yesterday is enough.
Thank you very much for helping, appreciate this!
Thank you very much for helping, appreciate this!
Kind Regards,
Vladlen