Formula to copy header after each empty row to next column

[thedeadzeds]

Ia there a way to create a formula in column B so it copies the header from each section so it looks like the below. The next header starts after each empty row in column A. Thanks

Units/Hours	Unit Hours
New Car Sales - Retail	Unit Hours
New Car Sales - Motability	Unit Hours
New Car Sales - Fleet	Unit Hours
New Car Sales - Agency	Unit Hours
Used Car Sales	Unit Hours
Service	Unit Hours
Turnover	Turnover
New Car Sales - Retail	Turnover
New Car Sales - Motability	Turnover
New Car Sales - Fleet	Turnover
New Car Sales - Agency	Turnover
Used Car Sales	Turnover
Service	Turnover
Parts	Turnover
Rental	Turnover
Total Turnover	Turnover
GP inc Other Income	GP inc Other Income
New Car Sales - Retail	GP inc Other Income
New Car Sales - Motability	GP inc Other Income
New Car Sales - Fleet	GP inc Other Income
New Car Sales - Agency	GP inc Other Income
Used Car Sales	GP inc Other Income
Service	GP inc Other Income
Parts	GP inc Other Income
Rental	GP inc Other Income
Total GP inc Other Income	GP inc Other Income
Expenses	Expenses
New Car Sales - Retail	Expenses
New Car Sales - Motability	Expenses
New Car Sales - Fleet	Expenses
New Car Sales - Agency	Expenses
Used Car Sales	Expenses
Service	Expenses
Parts	Expenses
Rental	Expenses
Total Expenses	Expenses

 

[hagia_sofia]

Hello, one option could be:

=LET(
a,A1:A40,
b,SCAN(0,a,LAMBDA(x,y,IF(y="",0,x+1))),
c,IF(b=1,a,IF(b=0,"",NA())),
SCAN("",c,LAMBDA(x,y,IF(ISNA(y),x,y))))