Performance issue, is it my Lookup formula? Excel 2010

[andygis]

Good afternoon,

I am experiencing a few performance issues and struggling to get to the bottom of it, I am using Windows 7 and Excel 2010, 32 bit.

I am unsure if I am not understanding the issue, i.e. it lies in computer memory or should look to use an alternative formula.

The problem:

Once the formula is implemented (which works excellently) I then need to remove certain rows from the worksheet if the maximum time in a room exceeds a certain amount and in doing so I run into the performance issue on the use of right click and delete with move rows up.

There are > 40,000 rows in some work sheets and I have to do this multiple times on varying file sizes and varying amounts of data.

Clearing 3 rows mid worksheet takes < 1 second (select and delete)
When right click and delete (move rows up) the same three blanks rows takes 9 minutes

For larger deletion and shuffling of data to remove blank rows takes considerably longer, for 5000 rows about 39 mins.

Fixes tried so far include:

Disable add-ins, set formulas to manual calculations not automatic. Working on smaller data chunks works but very tedious


Any advice greatly appreciated.

The data and formula is detailed below:

Formula in C2 =IF(B2<>B3,A2-LOOKUP(2,1/(1-($B$1:B1=$B$2:B2)),$A$2:$A2)+IF(B2=B1,0,1),"")

Timestamp Zone TimeInZone
1339074004888 Base Corridor 1
1339074015578 Corridor 1 1
1339074026144 Base Corridor
1339074036803 Base Corridor
1339074047435 Base Corridor 21291
1339074058056 Staff Base 1
1339074068693 Isolation Bay 1
1339074079333 Staff Base
1339074089974 Staff Base
1339074100591 Staff Base
1339074111208 Staff Base
1339074121847 Staff Base
1339074132454 Staff Base
1339074143315 Staff Base
1339074153702 Staff Base 74369
1339074164344 Isolation Bay
1339074174996 Isolation Bay 10652
1339074176482 Staff Base
1339074183139 Staff Base
1339074185622 Staff Base 9140

The formula looks for an entry time into a room (indicated by a change in zone), then finds the exit time and calculates time in room.

 

[andygis]

Cracked it, an issue with the way I was deleting and the formula used caused long delays. Simple result to alter the deletion of the data to entire row instead of shuffle up.