Dynamic arrays

Kariba

Board Regular
Joined
Mar 15, 2023
Messages
62
Office Version
  1. 365
Platform
  1. Windows
I have an array that pulls names from column C plus a value from column HR if column D does not contain 'Nights' or HQ if it does contain nights. The values in HR/HQ etc are matched from a named range called 'Holiday'

The result in HR250 looks like this
NAME A H10
NAME B H10
NAME C H8
=IFERROR(VSTACK(
FILTER($C5:$C210 & " " & INDEX(Holiday, MATCH(HR5:HR210, Holiday, 0)), (ISNUMBER(MATCH(HR5:HR210, Holiday, 0))) * (ISNUMBER(SEARCH("Nights", $D5:$D210))=FALSE)),
FILTER($C5:$C210 & " " & INDEX(Holiday, MATCH(HQ5:HQ210, Holiday, 0)), (ISNUMBER(MATCH(HQ5:HQ210, Holiday, 0))) * (ISNUMBER(SEARCH("Nights", $D5:$D210))))
), ""NAME )

The issue is when I drag the array to other cells columns HS and HT are blank and then values from HU onwards again. Formulas are correct. There are values in HS & HT, names are correct, nights etc are correct. I just can't work out when it works perfectly on the columns before and after.
 
Your formula in HR250 refers to columns HQ and HR. If you copy the formula to HS250, it will refer to columns HR and HS.

I am guessing that this is incorrect, and that the next formula should perhaps refer three columns to the right, i.e. to columns HT and HU? Are the results in HU250 correct?

It's relatively simple to have a formula increment by N (>1) columns when dragged to the right. But it probably makes more sense here to align the formulae with the relevant columns.

Your formula in HR250 can be shortened to:

Excel Formula:
=IFERROR(TOCOL($C5:$C210&" "&VLOOKUP(IF(ISNUMBER(SEARCH("Nights",$D5:$D210)),HQ5:HQ210,HR5:HR210),Holiday,1,),2),"NAME")

If I'm correct about the column spacing, you could change this to:

Excel Formula:
=IF(MOD(COLUMNS($HR250:HR250)-1,3),"",  IFERROR(TOCOL($C5:$C210&" "&VLOOKUP(IF(ISNUMBER(SEARCH("Nights",$D5:$D210)),HQ5:HQ210,HR5:HR210),Holiday,1,),2),"NAME"))

and drag to the right.
 
Upvote 0
Solution
Hi, the formula did work correctly as intended in every cell apart from columns HR and HS when dragged across. It should be picking up anything <> 'nights' in column HR and = 'nights' in HS. I've just discovered that there was no entries to match to 'nights' in 'holiday' range in HQ & HR so wasn't returning any results at all with VSTACK, but did if I separated the two.

Will change the divisor to 1 and check results of yours against the separated results.

Thanks for your help
 
Upvote 0
The data output all seems correct so far. I'll still need to work out how you did it, but at least we have the option of either separated days/nights or your stacked version.
 
Upvote 0

Forum statistics

Threads
1,226,867
Messages
6,193,426
Members
453,799
Latest member
shanley ducker

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
Back
Top