Why has Index Match array slowed calculation of spreadsheet to 1.5 hours

[carrach]

I have a workbook with numerous tabs - I have created an example with only 3 tabs to show my formula.
example Index Match query.xlsx
Worksheet 'Map' is the list that I am using to change the names of the items on the items tab.
Worksheet 'items' - Formula is in col. F. Column G is a cocatination of colA & ColF to create a unique field. The sale ref in col A can have more than 1 entry.
The main worksheet is 'data' -
I need to match the sale ref in col A on 'data' to a record in col A of 'items' AND match the heading of the row in 'data' with the entry in col F of 'items' and when a match has been found pull the corresponding value from column C in 'items' (Quantity) into the relevant column in 'data'. I then need to repeat this for columns D and E in 'items' into the next batch of columns in 'data'.
The formula I have entered into D5 is '=INDEX(ItemsTable,MATCH($A5&D$4,Items!$G$9:$G$16,0),3) using Cntl+shift+enter as it is an array. This was then copied down the 100000 rows of the data worksheet and across the columns from G:HX. The array is working and is pulling back the correct values however it is taking 1.5 hours for the worksheet to recalculate everytime I do anything in it, even just highlighting. How can I do this differently in order to be more efficient as this is work that needs to be done on a very tight schedule and I cannot take the time to do it this way.
Please be aware that because of the slowness of the array I have not copied it across in the example spreadsheet attached but have left it as a text example.
I have never used VBA so if that is required please could you describe the process for a novice user.

[Pepe Le Mokko]

To avoid the array perhaps create a helper column with the concatenation and use the results for the MATCH function

[martindwilson]

you already have a concatenated column g so there is no need to array enter
=INDEX(ItemsTable,MATCH($A5&D$4,Items!$G$9:$G$16,0),3)

[ben_hensel]

5 rows x 18 columns = 90 formulas
each fed an array of 100,000 items?

And lookups are kind of expensive calculations to begin with...

That's basically, too many for an arrayed formula.

Try these on for size instead, in D5 & pull around:

Please Login or Register  to view this content.

I think those should perform better.

[mikeTRON]

LOL 1.5hrs! That is insane.
So typically how I approach optimizing a model or workbook after I have created it (or more likely after someone passes it along) I'll look for calculations that happen in multiple cells that only need to happen once. For example if we have a complex formula that is then being divided by the the sum of an entire column in EACH cell, I will calculate the sum in another cell, throw a name range on it so it is intuitivr and reference it in each of the calculations. In a workbooks I was recently handed I was able to make a few absurdly quick changes like that and save over 700,000 of the same calculations.

I think another of the specific calculations I avoided was an index+match+match where the horizontal match was the same number for the entire column of cells. I simply made that match calculation once, in a cell above, then made all of the formulas reference that cell.

Also for performance I typically dislike using sumifs on more than 20k Cells. I usually end up wrapping a pivot table around the data and looking up off the pivot making everything dynamic. It basically exchanges workbook size for calculation and responsiveness of the workbook.

Hopefully you can make sense of all that rambling lol.

[Vikas_Gautam]

Try this...

Please Login or Register  to view this content.

Check the attached file...

[martindwilson]

what advantage over the original formula is that apart from adding iferror?

[Tony Valko]

Just passing by...

If you can sort the data in ascending order on column G AND if there will always be an exact match of the lookup values then using:

=INDEX(ItemsTable,MATCH($A5&D$4,Items!$G$9:$G$16),3)

Should be faster to calculate.

[shg]

If you sort the Items table ascending by col G, you can make this run a few thousand times faster with 100,000 rows and a different formula in the body of the table.

EDIT: Essentially the same as Tony's, except it calculates the Items column once for each column instead of in every cell.

[kvsrinivasamurthy]

Pl see attached file
An attempt is made to use worksheet Change events to get data live similar to formula, without having formula in the entire range.
Formula will be available only in one cell.
Time required may be only few seconds.(not in hours)
Test it on a copy of your file.

[carrach]

Thank you all for your help. Sorry for the late response but I have been off work until today. I will work through your replies now and advise if they help me. Some are using formulas that I have not worked with before so it will be a bit of a learning curve as well so thank you all for your time.

[carrach]

Sorry, but have tried to reply to posts but getting error message each time. Thank you for your help

[carrach]

Thank you martindwilson. I used this one this morning with the small change that instead of referencing column 3 at the end I referenced a cell reference that contained the column number i.e. I used the row 3 above the headings to add the column number of 3 4 or 5 and added D$3 as the reference within the formula. Therefore I used =INDEX(ItemsTable,MATCH($A5&D$4,Items!$G$9:$G$16,0),D$3) which I amended to suit my full table and copied it across the 155 columns and then down the 110000 rows, however my excel threw a wobbly and crashed (eventually). It populated the cells with the answers but it was at 0% calculating for about 2 hours before crashing and reverting back to the spreadsheet with no formulas in. Is this an excel issue or is it an issue with the memory on my machine? Should Excel 2007 be able to handle this amount of cells with this formula copied into them?

[carrach]

Thank you for this and for the example - it has been useful

[carrach]

Pl see attached file
An attempt is made to use worksheet Change events to get data live similar to formula, without having formula in the entire range.
Formula will be available only in one cell.
Time required may be only few seconds.(not in hours)
Test it on a copy of your file.

Thank you for this.

[carrach]

If you sort the Items table ascending by col G, you can make this run a few thousand times faster with 100,000 rows and a different formula in the body of the table.

EDIT: Essentially the same as Tony's, except it calculates the Items column once for each column instead of in every cell.

Thank you Shg, however it may take me some time to get my head around how the formula works and how formulas within named ranges work as I have not used these before. Please do not think me rude if I take a while to respond. Hoping for the best,
Carrach

[carrach]

If you sort the Items table ascending by col G, you can make this run a few thousand times faster with 100,000 rows and a different formula in the body of the table.

EDIT: Essentially the same as Tony's, except it calculates the Items column once for each column instead of in every cell.

Thank you Shg, however it may take me some time to get my head around how the formula works and how formulas within named ranges work as I have not used these before. Please do not think me rude if I take a while to respond. Hoping for the best,
Carrach

[carrach]

If you sort the Items table ascending by col G, you can make this run a few thousand times faster with 100,000 rows and a different formula in the body of the table.

EDIT: Essentially the same as Tony's, except it calculates the Items column once for each column instead of in every cell.

So sorry to bother you again with this query but I am still unable to get it to work.
I have tried to amend your formula to match my full spreadsheet but it is not pulling though the correct values.
Unfortuantealy I have tried to shrink my spreadsheet many times today but I cannot get it under 5000 kb which is too big to attach to this message.
From the spreadsheet that you attached: scratch.xlsx I changed the formula to:
in cell BQ9 of data tab
=IF(LOOKUP($A9&BQ$6,tblmerged)=$A9&BQ$6,INDEX(tbl,MATCH($A9&BQ$6,merged),BQ$5),"")
named range tblmerged:
=INDEX(tbl,0,COLUMNS(DATA!$A$10:$G$10))
named range tbl:
=INDEX(SaleRefItems,ROW(items!$A$10)+1):INDEX(merged,MATCH("zzzz",SaleRefItems))
named range SaleRefItems:
=items!$A$11:$A$150000
Row 5 holds the number of the column to reference in the items spreadsheet (either 3, 4, or 5)
I then copied the formula across from BQ9 to GO9 and down the 190000+ rows but unfortunately all cells now read #N/A

I am now very keen to get this to work and would appreciate any pointers as to where I have gone wrong.
Many thanks

[carrach]

bump. Any help appreciated

[kvsrinivasamurthy]

Pl see attached file.
In the example file Column A in Items sheet is in sorted fashion.
Assuming it will be same in your original file, I have changed formula & used one Helper column.
Time required will be drastically reduced.

[carrach]

Thank you so much. That is wonderful news to start the day with. I am eternally grateful.

[kvsrinivasamurthy]

Thanks for the compliments.
May i know what is the time taken now instead of previous 1.5 Hrs.

[carrach]

Thanks for the compliments.
May i know what is the time taken now instead of previous 1.5 Hrs.

Not sure if I am allowed to say this but - OMG I could kiss you!!!! :-)
The entire spreadsheet 100000 rows and columns A to GP runs in 47 seconds and it works.
you are wonderful in my eyes. I know I am gushing a bit but when you have struggled for so long with something and suddenly it works, it is wonderful news.
Thank you again.

[Vikas_Gautam]

This is something great..
No matter what you have done..
Processing something which needs 1.5 hours in just 47 Seconds.. needs some serious application...

I am glad to know this truth..
You are great KVS...





Regards,

[kvsrinivasamurthy]

@carrah & @ Vikas_Gautham

Thanks for the compliments. I am happy ,that I could save some time.