Fill in function

[tigerzen]

The desired result is on the right, I need a formula/function to fill in the gaps, the tricky part is that the formula needs to adjust when a new heading appears

Book3
	A	B	C	D	E
2	Project	A 1.1		Project	A 1.1
3				Project
4		A 1.2		Project	A 1.2
5		A 1.3		Project	A 1.3
6				Project
7		A 1.4		Project	A 1.4
8		A 1.5		Project	A 1.5
9				Project
10				Project
11		A 1.6		Project	A 1.6
12	Governance	B 1.1		Governance	B 1.1
13				Governance
14				Governance
15				Governance
16		B 1.2		Governance	B 1.2
17				Governance
18		B 1.3		Governance	B 1.3
19	Consultation	C 1.1		Consultation	C 1.1
20				Consultation
21				Consultation
22		C 1.2		Consultation	C 1.2
23				Consultation
24		C 1.3		Consultation	C 1.3
25		C 1.4		Consultation	C 1.4
Sheet1

 

[Cubist]

An option. Select the Range A2:A20. Ctr + G -> Specials -> Blanks -> Type =A2 -> Ctrl + Enter.

 

[tigerzen]

Thanks Cubist, that works really well when the 2 columns are next to each other, sometimes my data might read Project, status, on/off track, A1.2 so the above formula won't work. Also, users will add/delete rows so need a formula that will still be able to populate.

 

[Cubist]

I'm not aware any combination of Excel functions that can accomplish this but here's a UDF.

Function FillRange(rng As Range) As Variant
    Dim arr() As Variant
    Dim i As Long
    arr = rng.Value
 
    ' Loop through array to replace blank cells with the value of the previous non-blank cell
    For i = LBound(arr, 1) To UBound(arr, 1)
        If IsEmpty(arr(i, 1)) Then
            If i > LBound(arr, 1) Then
                arr(i, 1) = arr(i - 1, 1)
            End If
        End If
    Next i
 
    FillRange = arr
End Function

Book3
	A	B	C	D	E
1
2	Project	A 1.1		Project	A 1.1
3				Project
4		A 1.2		Project	A 1.2
5		A 1.3		Project	A 1.3
6				Project
7		A 1.4		Project	A 1.4
8	Mark	A 1.5		Mark	A 1.5
9				Mark
10				Mark
11		A 1.6		Mark	A 1.6
12	Governance	B 1.1		Governance	B 1.1
13				Governance
14				Governance
15				Governance
16		B 1.2		Governance	B 1.2
17				Governance
18		B 1.3		Governance	B 1.3
19	Consultation	C 1.1		Consultation	C 1.1
20				Consultation
21				Consultation
22		C 1.2		Consultation	C 1.2
23				Consultation
24		C 1.3		Consultation	C 1.3
25		C 1.4		Consultation	C 1.4
Sheet8

Cell Formulas
Range	Formula
D2:D25	D2	=FillRange(A2:A25)
Dynamic array formulas.

 

[Fluff]

A formula option

Fluff.xlsm
	A	B	C	D	E
1
2	Project	A 1.1		Project	A 1.1
3				Project
4		A 1.2		Project	A 1.2
5		A 1.3		Project	A 1.3
6				Project
7		A 1.4		Project	A 1.4
8		A 1.5		Project	A 1.5
9				Project
10				Project
11		A 1.6		Project	A 1.6
12	Governance	B 1.1		Governance	B 1.1
13				Governance
14				Governance
15				Governance
16		B 1.2		Governance	B 1.2
17				Governance
18		B 1.3		Governance	B 1.3
19	Consultation	C 1.1		Consultation	C 1.1
20				Consultation
21				Consultation
22		C 1.2		Consultation	C 1.2
23				Consultation
24		C 1.3		Consultation	C 1.3
25		C 1.4		Consultation	C 1.4
Data

Cell Formulas
Range	Formula
D2:E25	D2	=HSTACK(SCAN(,A2:A25,LAMBDA(a,b,IF(b="",a,b))),IF(B2:B25="","",B2:B25))
Dynamic array formulas.

 

[Cubist]

That's a nice way to use SCAN.