Dynamic Indexing question

[seh0872]

OK all, another efficiency question.

When a workbook drives off of data imported into a particular tab and updated periodically, in order for Excel to adjust to any changes in the data's structure (often a change in the number of rows of data), a dynamic INDEX function is used to tell Excel to find the lowest and farther right cell of the table. The formula that does this is as follows:

(Assume header in row 2, data starts in col 3)

$A$3:INDEX($3:$1048576,COUNTA($A$3:$A$1048576),COUNTA($2:$2))

This will index the entire spreadsheet, starting with the first data row. But, to increase Excel's efficiency, if the columnar structure of the table will not change, having Excel search all the way to column XFD is wasteful. Historically, I've modified this formula thusly (assume 10 columns of data):

$A$3:INDEX($3:$1048576,COUNTA($A$3:$A$1048576),COUNTA($A$2:$J$2))

But recently I got to thinking ... that formula is still telling Excel to index all the way to column XFD ... and should my modification actually be:

$A$3:INDEX($A$3:$J$1048576,COUNTA($A$3:$A$1048576),COUNTA($A$2:$J$2))

The first modified formula works fine, of course, but I'm wondering if I'm sacrificing calculation speed that would be improved by making the change to the second modified version.

Am I correct? Any other thoughts on how to keep this indexing efficient while keeping it dynamic to accommodate varying rows of data in any given data import?

Many thanks!

[AliGW]

I would be using 365 functions for this.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[AliGW]

Something like this:

=FILTER(A3:J1048576,A3:A1048576<>"")

This is a spill formula, so you'd need to make room for it to spill by removing all of your current formulae from the area into which it needs to spill.

[seh0872]

Thanks, I probably should have titled that post "Dynamic Named Range" rather than "Dynamic Index" because that's where I'm headed as I design this analysis workbook.

[AliGW]

I have no idea what you want from that comment - sorry. In what way does post #3 not meet your requirements?

[seh0872]

Can you use a FILTER function in a named range? I haven't tried. I've used these arrays plenty inside workbooks themselves, but never a substitute for this:
https://exceljet.net/glossary/dynamic-named-range

I have always built these with the INDEX function given that OFFSET is a volatile function. But in the multi-sourced (and multiple lookup) analysis file I'm trying to design, performance is going to be an issue as the datasets can have hundreds of thousands of rows. So, in an effort to plan better, rather than having Excel index the entire worksheet, I thought I'd try to limit it to a smaller range -- but one that still allowed for it to automatically expand. As it is far more likely that rows will expand and contract rather than columns, my intent was to limit the number of columns indexed.

Attachment 876671

My best answer is probably to use Excel tables, which I've limited experience with. But, ultimately the workbook could easily have 15-20 XLOOKUPS across various source data tabs, which is such a performance hog. Trying to plan the best way to structure this file to perform optimally while keep the source data's integrity is a challenge.

[AliGW]

I know what a named range is, but thanks for the link.

Without seeing any of this in context, I can’t really offer any advice. You haven’t actually clarified why you need named ranges. Have you looked at PowerQuery at all?

Sorry I couldn’t be of more help.

[seh0872]

No worries. This is a 14-sheet workbook with 8 source reports all in different tabs (those source reports will be periodically re-imported), and a set of "analysis" sheets that cross-reference data among the source files (e.g look column A in source file 1, return column D....take that data, look it up in source file 2, return column E...if E is x, then look it up in source file 3 and return col G, but if E is y, then look it up in source file 4 and return col H....and so forth....). The underlying structure of the data is, of course, the root problem here, as is the fact that we're doing this in Excel versus a proper Business Analytics tool, but I can't change either of those variables. If I could come up with an easy way to demonstrate with a sample file, I'd try to load it here, but this isn't about getting a formula to work ... it's about structuring the whole thing to optimize performance ... oh, yea, and to be able to hand this whole workboook off to less sophisticated users who, with some basic instruction, should be able to generate the output each successive time the source data is updated .... ugh. In essence, I'm building a program here, but one that doesn't require Excel to take 22 minutes to calculate...

[AliGW]

This sounds like a candidate for VBA to me - is that an option? If so, I can move the thread for you.

[FlameRetired]

Please try this.

If I understand correctly use this for numbers

=$A$3:INDEX($3:$3,MATCH(25^25,$3:$3))

and this for text

=$A$3:INDEX($3:$3,MATCH("zzzz",$3:$3))

You can name the formula in Name Manager. It is dynamic and it is important to use approximate match type. It is the default type and also very fast. It will match on the last item in the row.

Does this do what you want?

[josephteh]

Your attachment in post #6 is not valid.