Fill down formulas and Do Until "No more data"

[AliB]

Hi

I am new to Mr Excel, returning to VBA after many years' break (I've been using power query for most of my solutions) and am hoping that some of you clever people will be able to solve my conundrum.

I have a spreadsheet that picks up data from another sheet from the same workbook, does a few lookups and presents the data in a different format.

Each line in the extract below represents an equipment installation that needs to be completed, and the Qty in column G shows how many instances of that installation will need to take place. If only 1 installation, then nothing needs to happen but if there are more than one, we need to insert additional rows and filldown the formulas for the whole row, and then overwrite the values in column G to 1. Then move on to the next one.

The formulas in columns D, E and F are based on the number in column C. So, for example, rows 33 to 35 need to say 22. Formulas from column G onwards are then based on the combined values of C, D, E and F.

The numbers in Column C are currently manually input until we get the result "No more data" (see below). I'm wondering if there is another macro that could be built in to automate this process too.

I have managed to find a macro that will insert the extra rows but I am having trouble with filling down the formulas. For the life of me I cannot work out how to tell it to stop when the result in column D says "No more data". The last thing I tried ended up in a never-ending loop!

This is all I have so far:

Any help you can give me would be very much appreciated.

Yours hopefully,

AliB

 

[felixstraube]

How about doing it with power query?:

With this table as input:

FillDownFormulas.xlsx
	B	C	D	E	F
1	Item	Heading	Equipment	Level	Qty
2	1	Heading01	ADP01	1	1
3	2	Heading02	LBP01	2	1
4	3	Heading03	UIP01	1	3
5	4	Heading04	UMS01	2	2
6	5	Heading05	UPM	1	1
Hoja1

And this code:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    Typed = Table.TransformColumnTypes(Origen,{{"Item", Int64.Type}, {"Heading", type text}, {"Equipment", type text}, {"Level", Int64.Type}, {"Qty", Int64.Type}}),
    Repeated = Table.TransformColumns(Typed, {"Qty", each List.Numbers(_,_,0), type list}),
    Expanded = Table.ExpandListColumn(Repeated, "Qty"),
    ColumnsRemoved = Table.RemoveColumns(Expanded,{"Qty"}),
    CustomColumn = Table.AddColumn(ColumnsRemoved, "Qty", each 1)
in
    CustomColumn

You get:

FillDownFormulas.xlsx
	B	C	D	E	F
1	Item	Heading	Equipment	Level	Qty
2	1	Heading01	ADP01	1	1
3	2	Heading02	LBP01	2	1
4	3	Heading03	UIP01	1	1
5	3	Heading03	UIP01	1	1
6	3	Heading03	UIP01	1	1
7	4	Heading04	UMS01	2	1
8	4	Heading04	UMS01	2	1
9	5	Heading05	UPM	1	1
Tabla1

You just have to add the formulas of columns I to N

 

[AliB]

How about doing it with power query?:

Hi @felixstraube, thanks for this, I'll give it a go and report back

 

[AliB]

How about doing it with power query?:

A bit of a late response but thank you for your help on this, it worked!

 

[felixstraube]

You are welcome. Thanks for the feedback.