SOLVED

Contributor

# Formula help expanding table?

The subject is probably not a good summary of what I'm looking to do. But, It's the best could come up with.

I would like to know if there is a way to take range A1:A21 and make it appear as range B1:B21

I have column A and need to convert it to look like column B so that I can use those for calculation in other formulas. I was thinking it had to be something with a BYROW / LAMBDA function but I can't make it work.

9 Replies

# Re: Formula help expanding table?

@Jagodragon What's the logic here?

why a 5 next to the 2 and why 4 numbers 2 below it?

# Re: Formula help expanding table?

that was an error. that '5' should have been a '2'

# Re: Formula help expanding table?

that's fixed now... sorry

# Re: Formula help expanding table?

The 4 numbers below it is by the range definition of A1:A21. For each cell in that range, if the cell is blank use last number found, if cell has a number use that number.

# Re: Formula help expanding table?

Just try with =IF(A2="",B1,A2) and First Row should be header or blank.

 1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 4 4

# Re: Formula help expanding table?

@Jagodragon Did something with PowerQuery as I'm not comfortable with VBA. Perhaps it works for you.

# Re: Formula help expanding table?

Thank you sivakumarrj. I am trying to get this to work with range formulas. so, i need this result but from a single formulas that looks at A1# (A1# = A1:A21). I can not use a copy and pasted formula as I need this to be a dynamic formula. that is to say that A1# could be 1 row, 1000 rows, or more possibly.

# Re: Formula help expanding table?

``````Sub fill()

Dim MaxRow As Long
Dim i As Long

MaxRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To MaxRow

Cells(i, 2).Value = Cells(i, 1).Value
If Cells(i, 1) <> "" Then

Else
Cells(i, 2).Value = Cells(i - 1, 2).Value

End If

Next i

End Sub``````

Assuming that there is always a value in cell A1 you can try these lines of code. In the attached file you can click the button in cell E2 to start the macro.

best response confirmed by Jagodragon (Contributor)
Solution

# Re: Formula help expanding table?

I didn't actually use your response because I needed to be able to set it for a specific range. But, you confirmed my fear that i would have to create a VBA function for this.

Here is the code I ultimately used.

``````Function Expand(x As Range) As Variant
'formula to expand values of a column range to fill all cell
'  =Expand(A1:A20) or =Expand(A1#)
Application.Volatile

Dim RET As Variant                                      'retun table
Dim XVal                                                'vriable to hold current num
Dim i                                                   'counter

i = 1                                                   'presetting the counter
XVal = 0                                                'presetting the initial set value

ReDim RET(1 To x.Rows.Count, 0)                         'defineing the Return array
For i = 1 To x.Rows.Count                               'For each cell in the intial range dor the following
If IsNumeric(x(i).Value) = True And x(i) > 0 Then       'check if the value is a number greater than 0
XVal = x(i).Value                                       'if it is a nuber greater than zero,set the pass thru value to cell value
End If                                                  '
RET(i, 0) = XVal                                        'set cell value to updated pass thru value
Next i                                                  '
Expand = RET                                            'sends the processed information back to the excel sheet thru formula
End Function``````