Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    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?
    Last edited by kweaver; 2015-02-20 at 18:53.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    =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. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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], _
        Order1:=xlDescending, Header:=xlYes
    
    '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), _
        Header:=xlYes
    [a1].Select
    
    'Now sort remining records by Concert Name and Venue
    [a1].CurrentRegion.Sort _
        Key1:=[b2], Order1:=xlAscending, _
        Key3:=[o2], Order2:=xlAscending, Header:=xlYes
    
    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. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi kweaver

    So glad you liked that!
    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. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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. #8
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    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.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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. #10
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    I did say OR but since you have the data why not try a macro using advanced filter for unique.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

Posting Permissions

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