# Thread: Recalc nightmare...hope there's a better way

1. ## Recalc nightmare...hope there's a better way

I have a client file that's very large (92,129 rows). There are (only) a double handful of calculations being performed, but recalc takes forever.
I suspect it might be mainly due to two columns with array formulas.

Here's one: =MAX((\$B\$2:\$B\$92130=\$B2)*(\$O\$2:\$O\$92130=O2)*\$H\$2:\$ H\$92130)

Is there another way to skin this cat that would substantially reduce the calculation time?

I'm wondering if MAX(IF etc. would be faster?

2. =MAX((\$B\$2:\$B\$92130=\$B2)*(\$O\$2:\$O\$92130=O2)*\$H\$2:\$ H\$92130)

KW,

To avoid having to create test data, can you briefly describe what the array formula above is intended to do?

Maud

3. When the Bn text matches the B col range AND the On text matches the O column range, grab the dates from H and find the largest date.

The B col contains concert performance names, the O column contains venue names and the H column contains dates for the various performances.

So, for each performance at a given venue, what was the last date of that performance at that venue.

Concert "Maud" may have performed 40 times at 10 different venues. In a given row that reflects the performance Maud made at a given venue, the result is the last performance date for that concert for that venue (Maud may have performed 6 times at that venue over time. Need the last date for that row's info)

row 10: Maud, Philly, dateA
row 30: Maud, Philly, dateB
etc.

I need to find the last date Maud appeared in Philly over all of the dates Maud appeared in Philly.

Make sense?

4. Hi kweaver

If you are using Excel2007 or later, you could try this vba method.
It makes a copy of the data sheet,
With this copy of the sheet, it sorts it by latest date value in column [H].
Then it removes duplicate records based on the combination Concert Name col [B] and Venue (col [O]
After removing the unwanted duplicates (i.e. essentially keeping those with the 'latest' date ) it then sorts the remaining records by Concert Name col [B] and Venue (col [O]
You can adjust the columns to suit.
I tested this with 100,000 records and it took a couple of seconds to give the results of ALL the latest performances.
I used a copy of the sheet to 'preserve' your original data.

Code:
```Sub latestDates()

ActiveSheet.Copy before:=Sheets(1)
[a1].Select

'Sort by Date, most recent first..
[a1].CurrentRegion.Sort Key1:=[H2], _

'Now remove duplicates based on Concert Name, Venue
'Where col [B]=Concert Name, col [O]=Venue City
[a1].CurrentRegion.RemoveDuplicates _
Columns:=Array([b1].Column, [o1].Column), _
[a1].Select

'Now sort remining records by Concert Name and Venue
[a1].CurrentRegion.Sort _
Key1:=[b2], Order1:=xlAscending, _

End Sub```
Please let me know if this works for you.

zeddy

5. ## The Following User Says Thank You to zeddy For This Useful Post:

kweaver (2015-02-21)

6. What a GREAT idea! I just ran it for 79K rows and it was 2-3 seconds!

I have to do this latest date with three different breakdowns, so will just modify this based on the columns and run three separate macros.

Again, what a GREAT idea! Thank you MUCH!

7. Hi kweaver

Most of the time taken is making the sheet copy!
And I'm sure you noticed that if you wanted the FIRST date rather than the LAST date, you just reverse that first date sort.

zeddy

8. KW,

Here is a different angle from Zeddy's clever code that will use auto filtering instead to find a search result as opposed to a list. Enter the concert in cell B1 and the venue in cell O1 then click search. The matching concert, venue, and Latest date will be displayed instantaneously (100,001 rows). For clarity, I have hidden the columns between B, H, and O. It is also a case insensitive search. Sorry I could not post the file as it was to big with 100,000 rows of data.

KW_venue1.png

KW_venue2.png

Code:
```Sub SearchVenue()
Application.ScreenUpdating = False
Dim rng As Range
LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = Range("\$B\$3:\$O" & LastRow)
Range("B3:O3").Select
Selection.AutoFilter
rng.AutoFilter Field:=1, Criteria1:=Range("B1")
rng.AutoFilter Field:=14, Criteria1:=Range("O1")
MaxDate = CDate(WorksheetFunction.Subtotal(4, Range("\$H\$3:\$H\$" & LastRow)))
rng.AutoFilter Field:=7, Operator:= _
xlFilterValues, Criteria2:=Array(2, MaxDate)
Application.ScreenUpdating = True
End Sub```
HTH,
Maud

9. Why not just have a cell above the table where Maud is entered or selected from a drop down and another to enter philly and then a subtotal(104 for the range) in the next cell. Have a worksheet change fire a macro to filter by the 2 criteria.and the subtotal formula would give you the answer.

Or, you could use a FINDNEXT macro with an if to stop on the max date.

10. That would require additional coding to on workbook_open to remove duplicates form 100000 to create a data validation list for both criteria. The OP is already concerned with a bogged down flow sheet. Typing the criteria can be quicker than scrolling down a long list from a dropdown. I don't know if there would be a real benefit with such a huge amount of data

11. I did say OR but since you have the data why not try a macro using advanced filter for unique.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•