Dynamic Range with Dynamic Array to Fill Down? Unit Price Analysis Civil Eng Problem

[dejhantulip]

Dear community,

I turn to you with a problem that I am facing and that I have tried to come up with a solution but I cannot solve it yet.

In this worksheet I have formatted information laid out in a particular way or format that I have to maintain (D2:H39).
You can see that the "UNIT PRICE ANALYSIS" is kind of a form that may change in size (meaning more rows can be added) and if needed it can be pasted below so I might have 3 or 4 or more UNIT PRICE ANALYSIS forms. Again, not the best scenario for data, however I am restricted to working this way.

So basically I have been thinking and thinking of a way to use SEQUENCE to dynamically or automatically expand a helper column (column A) that would "fill down" the code found in Column C only when the "Activity:" word is on Column D. Also in Column C you can find other codes, but I only want the helper column to have the code whenever in Column D there is the word "Activity:".

You can see in Column B a manual help column (that must be dragged and dropped) that shows the desired result:

You can see in cells with yellow background and green text (B3 and B23) that when "Activity:" comes up it changes the code respectively. This is a good solution but it isn't dynamic.



So I tried to do the following idea with the SEQUENCE dynamic array function on Column A to try to dynamically replicate what I did on Column B:

Which I think has the "potential" to work because at first I am using the:

MAX(IF(ISBLANK(D:D),0,ROW(D:D)))

to find the row number of the last non blank cell in column D.
And then I am using the SEQUENCE function to generate a "dummy" (?) array to expand as the last non black cell changes as more forms are added or taken out.

And then finally I am using an IF funcion to try to repeat the formula or fill down. However, I believe it is "exactly" repeating the result of the formula and not evaluating it as it fills down.

To be honest, this is what I came up with, and I wanted to try to explain to the best of my abilities (sorry, English is not my first language) in order for someone to understand what I am trying to accomplish.

Any and all help is greatly appreciated

Also, if someone wants to give me another approach or formula (maybe a Lambda function), or something else that is not VBA I would be very very grateful


ORLANDO

 

[Marcelo Branco]

Not sure i understand what you're looking for. See if this does what you need.

A3 copied down for as many rows you want
=IF(ROW()>MATCH(REPT("z",255),D:D),"",IF(D3="Activity",C3,A2))

Hope this helps

M.

 

[dejhantulip]

Dear all,

As I have noticed, the wording and explanation of my problem was too confusing.
Hence here you can find a simplified version of my problem.

Here is the situation:
1) I have a dynamic array on column R
2) I have some text on column S
3) The end result I am looking for is shown in column T

Basically I would like to use dynamic arrays to repeat the text on column T every time a number (greater than zero or other than zero) is shown on column R.
As you can see the AP-001 text is repeated from the start (row 3) up until row 22 because of row 23 there is the AP-002 text, and from row 23 on up until row 37 AP-002 is repeated.
By the way, the numbers in column R are unique (will never be repeated in the R column).

I am looking for a dynamic array solution as I want this to be dynamic and not have to "manually" copy a formula.

Any ideas or help?
I really don't know if this is even possible.

Hopefully someone will be able to tackle this... thanks so much in advance!


ORLANDO

 

[Marcelo Branco]

Unfortunately I can't help you as I don't have the 365 version yet.
Hope someone else can help.

M.

 

[tboulden]

Also, if someone wants to give me another approach or formula (maybe a Lambda function), or something else that is not VBA I would be very very grateful

Very easy with new LAMBDA helper SCAN. Apologies for not using XL2BB, working on a machine without the add-in.

 

[Eric W]

I know I can do better than this, but here's a start:

Book1
	R	S	T
1
2
3	10	AP-001	AP-001
4	0		AP-001
5	0		AP-001
6	0		AP-001
7	0		AP-001
8	0		AP-001
9	0		AP-001
10	10	AP-002	AP-002
11	0		AP-002
12	0		AP-002
13	0		AP-002
14	0		AP-002
15	0		AP-002
16	0		AP-002
17	10	AP-003	AP-003
18	0		AP-003
19	0		AP-003
20	0		AP-003
21	0		AP-003
22	0		AP-003
23
Sheet27

Cell Formulas
Range	Formula
R3:R22	R3	=IF(MOD(SEQUENCE(20),7)=1,10,0)
T3:T22	T3	=INDEX(S:S,INDEX(SMALL(IF(R3#>0,ROW(R3#),""),SEQUENCE(COUNTIF(R3#,">0"))),MATCH(SEQUENCE(ROWS(R3#),,ROW(INDEX(R3#,1))),SMALL(IF(R3#>0,ROW(R3#),""),SEQUENCE(COUNTIF(R3#,">0"))))))
Dynamic array formulas.

The R3 formula is just a dummy formula I put in to have something to work with.

 

[dejhantulip]

I know I can do better than this, but here's a start:

Book1
	R	S	T
1
2
3	10	AP-001	AP-001
4	0		AP-001
5	0		AP-001
6	0		AP-001
7	0		AP-001
8	0		AP-001
9	0		AP-001
10	10	AP-002	AP-002
11	0		AP-002
12	0		AP-002
13	0		AP-002
14	0		AP-002
15	0		AP-002
16	0		AP-002
17	10	AP-003	AP-003
18	0		AP-003
19	0		AP-003
20	0		AP-003
21	0		AP-003
22	0		AP-003
23
Sheet27

Cell Formulas
Range	Formula
R3:R22	R3	=IF(MOD(SEQUENCE(20),7)=1,10,0)
T3:T22	T3	=INDEX(S:S,INDEX(SMALL(IF(R3#>0,ROW(R3#),""),SEQUENCE(COUNTIF(R3#,">0"))),MATCH(SEQUENCE(ROWS(R3#),,ROW(INDEX(R3#,1))),SMALL(IF(R3#>0,ROW(R3#),""),SEQUENCE(COUNTIF(R3#,">0"))))))
Dynamic array formulas.

The R3 formula is just a dummy formula I put in to have something to work with.

Wow!!! Amazing!!!

You rock!!! Thank you sooooo much!!

If you ever optimize the formula, please be kind enough to share it with me Thank you very very much!! I am amazed and so grateful for what you did

 

[Eric W]

Glad we could help!

Here's a shorter version:

Book1
	R	S	T
1
2
3	10	AP-001	AP-001
4	0		AP-001
5	0		AP-001
6	0		AP-001
7	0		AP-001
8	0		AP-001
9	0		AP-001
10	10	AP-002	AP-002
11	0		AP-002
12	0		AP-002
13	0		AP-002
14	0		AP-002
15	0		AP-002
16	0		AP-002
17	10	AP-003	AP-003
18	0		AP-003
19	0		AP-003
20	0		AP-003
21	0		AP-003
22	0		AP-003
23
Sheet27

Cell Formulas
Range	Formula
R3:R22	R3	=IF(MOD(SEQUENCE(20),7)=1,10,0)
T3:T22	T3	=INDEX(S:S,LOOKUP(SEQUENCE(ROWS(R3#),,ROW(INDEX(R3#,1))),SMALL(IF(R3#>0,ROW(R3#),""),SEQUENCE(COUNTIF(R3#,">0")))))
Dynamic array formulas.

 

[CA_Punit]

Here is a little shorter version.

Book1
	Q	R	S	T	U	V
2				Eric		Punit
3		10	AP-001	AP-001		AP-001
4		0		AP-001		AP-001
5		0		AP-001		AP-001
6		0		AP-001		AP-001
7		0		AP-001		AP-001
8		0		AP-001		AP-001
9		0		AP-001		AP-001
10		10	AP-002	AP-002		AP-002
11		0		AP-002		AP-002
12		0		AP-002		AP-002
13		0		AP-002		AP-002
14		0		AP-002		AP-002
15		0		AP-002		AP-002
16		0		AP-002		AP-002
17		10	AP-003	AP-003		AP-003
18		0		AP-003		AP-003
19		0		AP-003		AP-003
20		0		AP-003		AP-003
21		0		AP-003		AP-003
22		0		AP-003		AP-003
23
Sheet1

Cell Formulas
Range	Formula
R3:R22	R3	=IF(MOD(SEQUENCE(20),7)=1,10,0)
T3:T22	T3	=INDEX(S:S,LOOKUP(SEQUENCE(ROWS(R3#),,ROW(INDEX(R3#,1))),SMALL(IF(R3#>0,ROW(R3#),""),SEQUENCE(COUNTIF(R3#,">0")))))
V3:V22	V3	=LOOKUP(SEQUENCE(COUNTA(R3#)),FILTER(SEQUENCE(COUNTA(R3#)),R3#>0),FILTER(S3:S22,S3:S22<>""))
Dynamic array formulas.

 

[dejhantulip]

Wow!! Thank you so very very much guys! You guys are incredible!!!!

I am planning on using this beauty of a formula on a very long spreadsheet (thousands of rows), which of the variations do you think will perform better on such large sheets?

Once again thank you sooo much