Forum Discussion
Formula speed / Laptop specification
Al_Dyas You haven't really explained what you are indexing and matching. Comparing/matching such large data sets could perhaps be done with Power Query. It allows you to connect to the data without loading it into an Excel sheet. Not sure, though, if this could work you.
Thank you Riny_van_Eekelen. I have no idea what a power query is so I will find out and try that.
A sample of the formula I use is this:
=INDEX('20201109 MOSL_MDS_SSPID.xlsx'!$I2:$I229079,MATCH(B2,'20201109 MOSL_MDS_SSPID.xlsx'!$A2:$A229079,0))
There are many different scenarios but I use the same formula structure for the majority.
From a 2016 file I am looking at a 2019 file to see how the data changed, or if it has been removed. For example the MATCH is looking for the exact sewerage point id (SPID) reference numbers in both files. The index returns the status of the SPID in the 2019 file.
So the index match formula answer 2 question for me, is the SPID still active and if it is what is the status.
In another scenario: I have carried out a process to batch update the status field of a large number of SPIDs overnight and the following day I run an index match against today's report and yesterday's report to check that the update worked. So again I am looking to see if the status of the SPID has changed as it should have.
I hope this helps and makes sense.