working with large tables. pointers to reduce calculation time

**nipz** · 07-14-2017, 01:15 AM

hi
i have three tables. RFID input, Main database and Attendance.
when a new IFID input is added the function checks if it is in the "Main database". If so it moves the relative information to "Attendance" table. with your help i managed to get the formula running perfectly. big thanks.
but wen i increase the number of rows in those tables around 500, it takes around 30 seconds to excel to do its calculations. For may application I need around 3000 rows. I want these tables to update real time. Is it possibly with excel ???
any pointers on methods to reduce calculation time ??
I have attache the workbook, check if my use of formula is increasing calculation time??
Try adding or deleting few values in "RFID input" sheet, you will see it takes few seconds.RFID attendance database.xls
any better and faster method for this application??
Thanks

**shukla.ankur281190** · 07-14-2017, 02:32 AM

I don't understand why you are increasing rows in TABLE??? While if you converted your data sheet into table then you don't need to increase rows manually this the first thing. The second thing is as I said in first thing table format itself increase and decrease ranges if you are using the ranges not manually but in table format. Do the follow below instruction which will reduce calculation time in formulas.

1- Do clear or delete all the cell other than data.
2- Do decrease rows till the end of data.
3- now use below formulas instead of yours.

In attendance sheet

A3

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

B2

Formula:

Please Login or Register  to view this content.

Do drag towards the cells and down till H15.

Check the attached file for your information.

You can also change file format while saving as .xlsm as you have Macro and VBA codes.

Note:- Table format can increase and decrease rows according the formula need.

Hope this will help you.

If not you can also change calculation option press Alt->M->X->M this step will change calculation option automatic to manual. When ever you finish you work and save the data the formula will work other wise you will have get easy working with wrong result.

**FlameRetired** · 07-14-2017, 11:28 AM

Also you have duplicate RFIDs E5:FD:FF:AA with two names associated in 'maindatabase'. Of the two only obama will match in 'Attendance'. sriyaani will not.

**PietBom** · 07-14-2017, 04:21 PM

My solution is pretty quick for this size.
Please test it with your actual size of tables.
If it is to slow then, I can help you with a VBA solution (Binary Search) that will be very quick even for extreme large tables.

**nipz** · 07-17-2017, 02:25 AM

@shukla.ankur281190
your method works and it is faster. but when i reduce rows in attendance table, the data will only get updated until row 15. it only updates for the size of the table. that's why i had added more rows.

new with data.xlsm

**nipz** · 07-17-2017, 02:29 AM

@FlameRetired
sorry about that. i was just entering few RFIDs to test the system. in my real application there wont be duplicates.

**nipz** · 07-17-2017, 03:14 AM

Originally Posted by PietBom

My solution is pretty quick for this size.
Please test it with your actual size of tables.
If it is to slow then, I can help you with a VBA solution (Binary Search) that will be very quick even for extreme large tables.

Thank you this method works. I added few random data for the actual size of my final database. around 4000 raw. it works really fast. but the attendance table stops updating after raw 556 ??
Could you explain your method. also is there a way to prevent duplicates from updating attendance table. like if the same RFID input is entered twice in "RFID table" then it only get updated the first time in "attendance table"
thank you
Attachment 528186

**PietBom** · 07-17-2017, 04:34 AM

I added Macro1 and assigned it to the button on worksheet Attendance.
If you click on it it will do the trick....

**PietBom** · 07-25-2017, 06:54 AM

@nipz,
Did you find a solution yourself ?

working with large tables. pointers to reduce calculation time

LinkBack

Thread Tools

Rate This Thread

Display

working with large tables. pointers to reduce calculation time

Re: working with large tables. pointers to reduce calculation time

Re: working with large tables. pointers to reduce calculation time

Re: working with large tables. pointers to reduce calculation time

Re: working with large tables. pointers to reduce calculation time

Re: working with large tables. pointers to reduce calculation time

Re: working with large tables. pointers to reduce calculation time

Re: working with large tables. pointers to reduce calculation time

Re: working with large tables. pointers to reduce calculation time

Thread Information

Users Browsing this Thread

Similar Threads

Need some pointers in the right direction [Large Excel files]

Help to reduce SUMIFS array calc time & include additional variable to large formula

[SOLVED] time calculation formula not working well

[SOLVED] Time entry on UserForm displaying inccorectly and calculation not working on the time.

Working on Date /time calculation

how do I reduce calculation time?

Keep calculation time down in large Workbooks

Bookmarks

Bookmarks

Posting Permissions