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

dejhantulip

Board Regular
Joined
Sep 9, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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:

PjOFZE4.png


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:

pj6ZBc4.png


Which I think has the "potential" to work because at first I am using the:
VBA Code:
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,879
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Sep 9, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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

YJhWPQQ.png


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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,879
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Unfortunately I can't help you as I don't have the 365 version yet.
Hope someone else can help.

M.
 

tboulden

Board Regular
Joined
Jan 14, 2021
Messages
59
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
OrlandoScan.jpg
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,545
I know I can do better than this, but here's a start:

Book1
RST
1
2
310AP-001AP-001
40AP-001
50AP-001
60AP-001
70AP-001
80AP-001
90AP-001
1010AP-002AP-002
110AP-002
120AP-002
130AP-002
140AP-002
150AP-002
160AP-002
1710AP-003AP-003
180AP-003
190AP-003
200AP-003
210AP-003
220AP-003
23
Sheet27
Cell Formulas
RangeFormula
R3:R22R3=IF(MOD(SEQUENCE(20),7)=1,10,0)
T3:T22T3=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.
 
Solution

dejhantulip

Board Regular
Joined
Sep 9, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Book1
RST
1
2
310AP-001AP-001
40AP-001
50AP-001
60AP-001
70AP-001
80AP-001
90AP-001
1010AP-002AP-002
110AP-002
120AP-002
130AP-002
140AP-002
150AP-002
160AP-002
1710AP-003AP-003
180AP-003
190AP-003
200AP-003
210AP-003
220AP-003
23
Sheet27
Cell Formulas
RangeFormula
R3:R22R3=IF(MOD(SEQUENCE(20),7)=1,10,0)
T3:T22T3=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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,545
Glad we could help! :biggrin:

Here's a shorter version:

Book1
RST
1
2
310AP-001AP-001
40AP-001
50AP-001
60AP-001
70AP-001
80AP-001
90AP-001
1010AP-002AP-002
110AP-002
120AP-002
130AP-002
140AP-002
150AP-002
160AP-002
1710AP-003AP-003
180AP-003
190AP-003
200AP-003
210AP-003
220AP-003
23
Sheet27
Cell Formulas
RangeFormula
R3:R22R3=IF(MOD(SEQUENCE(20),7)=1,10,0)
T3:T22T3=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

Well-known Member
Joined
Nov 18, 2019
Messages
941
Office Version
  1. 365
Platform
  1. Windows
Here is a little shorter version.

Book1
QRSTUV
2EricPunit
310AP-001AP-001AP-001
40AP-001AP-001
50AP-001AP-001
60AP-001AP-001
70AP-001AP-001
80AP-001AP-001
90AP-001AP-001
1010AP-002AP-002AP-002
110AP-002AP-002
120AP-002AP-002
130AP-002AP-002
140AP-002AP-002
150AP-002AP-002
160AP-002AP-002
1710AP-003AP-003AP-003
180AP-003AP-003
190AP-003AP-003
200AP-003AP-003
210AP-003AP-003
220AP-003AP-003
23
Sheet1
Cell Formulas
RangeFormula
R3:R22R3=IF(MOD(SEQUENCE(20),7)=1,10,0)
T3:T22T3=INDEX(S:S,LOOKUP(SEQUENCE(ROWS(R3#),,ROW(INDEX(R3#,1))),SMALL(IF(R3#>0,ROW(R3#),""),SEQUENCE(COUNTIF(R3#,">0")))))
V3:V22V3=LOOKUP(SEQUENCE(COUNTA(R3#)),FILTER(SEQUENCE(COUNTA(R3#)),R3#>0),FILTER(S3:S22,S3:S22<>""))
Dynamic array formulas.
 

dejhantulip

Board Regular
Joined
Sep 9, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Forum statistics

Threads
1,144,342
Messages
5,723,824
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top