0

I currently have an Excel Table which is edited by multiple users. I wrote some formula to compare the actual status with the last saved file. It works well, but as any array formula, it is extremely slow:

  1. Macro copies all entries when I open the workbook (Mirror FEP Sheet)

  2. Array Formula compares corresponding rows in the original Table and the Mirror Table to find any differences; if there was some modification, it returns TODAY(). If not, the previous date is written...

    =IFERROR(IF(AND(EXACT([@[PTI-Nr]]:[@KIFA];INDIRECT("'Mirror FEP'!" & "B" & (2+MATCH([@[EAARCH-Nr]]; Table3[EAARCH-Nr]; 0))):INDIRECT("'Mirror FEP'!"&SUBSTITUTE(ADDRESS(1; COUNTA($2:$2);4); 1; "")&(2+MATCH([@[EAARCH-Nr]]; Table3[EAARCH-Nr]; 0))))); INDIRECT("'Mirror FEP'!" & "A" & (2+MATCH([@[EAARCH-Nr]]; Table3[EAARCH-Nr]; 0))); TODAY()); TODAY())

The formula is complex in order to find the right row in Mirror FEP even when the user changes the order of the original table.

I am familiar with the VBA solution, but my users need to Ctrl + Z all the time. Do you have any suggestion on how to speed up my Workbook? Alternative solutions are more than welcome!

Community
  • 1
  • 1
t.novaes
  • 126
  • 2
  • You can paste both tables into separate arrays and compare the rows by a key column using dictionaries. That would take barely seconds. – Damian Apr 09 '19 at 08:23
  • Hi Damian, I am not sure about what you mean. A VBA Code to create an UDF? Or something else? – t.novaes Apr 09 '19 at 13:58

0 Answers0