+ Reply to Thread
Results 1 to 9 of 9

Can VBA improve the performance of this spreadsheet

  1. #1
    Forum Contributor
    Join Date
    05-27-2014
    Posts
    141

    Can VBA improve the performance of this spreadsheet

    Hi All,

    I have a spreadsheet that is running very slowly, the formula in column G and the array formulas in column J-M on Sheet - Route allocation are the main cause for is its poor performance

    After the array's have finished working the end result is matching a person and a postcode that is available without duplicating, but these formulas only filter down which gives a match at 65%

    Can VBA increase the speed and accuracy? I have attached the sheet and sample data

    Sheet: Route Allocation
    The Formula in Column G after dismissing certain pretext will look at Column J-M to find a matched name with out duplicating
    Array Formulas in 'Route Allocation'J:M match Names against poscodes in sheet 'DA Avialble Coverage' this need to carry down to 750 rows

    Sheet: DA Available Coverage
    Data from 'DA Schedule' and 'Postcode by DA' are crossed reference to produce the data in 'DA Availabe Coverage'

    Sheet: Postcode by DA
    is a list of Names with postcodes each name can be listed 5 times
    Column A Matches the name against the names in 'DA Schedule' and if an X is present in column I

    Sheet: DA Schedule
    A basic rota where X represent the day a person is working. Column I reveals who is working dependant on the day of the week

    Sheet: Postcode by DSP
    an information page that revelas who is covering postcodes

    Any information would be grateful

    Many Thanks

    Danny
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Can VBA improve the performance of this spreadsheet

    1) I've added a KEY column to the Postcode by DA sheet that indexes the sheet based on the E3 value of the DA Available Coverage sheet

    2) The DA Available Coverage sheet columns A:B have new formulas that use the new index added above, eliminating all those arrays.

    3) The Route Allocation sheet has a new formula in column J to grab the first match name for each cluster, copied down.

    4) The Route Allocation sheet has a new formula in column K2 to grab the rest of the matching names, copied down and across through column M


    Speed is now near-on instantaneous.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    05-27-2014
    Posts
    141

    Re: Can VBA improve the performance of this spreadsheet

    Hi there,

    Thank you very much for that speed is as you said near-on instantaneous, but can I increase accuracy. I have updated the names and availability to just 4 names over three postcodes and I have noticed two faults.

    On Route Allocation I have just 4 lines of matchable data,

    H31 DA16 1
    H32 DA16 2
    H33 DA16 3
    H34 DA16 3

    The Matchable names in Postcodes by DSP
    Alexandru Ionut/CRLS/100454857 DA16 1
    Alexandru Ionut/CRLS/100454857 DA16 2
    Alexandru Ionut/CRLS/100454857 DA16 3
    Amir Jamak/CRLS/100445752 DA16 1
    Amir Jamak/CRLS/100445752 DA16 2
    Amir Jamak/CRLS/100445752 DA16 3
    Bayram Yediayli/CRLS/100445753 DA16 1
    Bayram Yediayli/CRLS/100445753 DA16 2
    Bayram Yediayli/CRLS/100445753 DA16 3
    Rhys Griffiths/CRLS/100479577 DA16 3


    Problem 1 - with data from Sample Data 1st stage
    Now to test accuracy I set up 3 names all with DA16 1, DA16 2 and DA16 3 as a preference and a 4th name with DA16 3 only. The End result should be a different name on each line. Accurately the formulas recognise the first three postcodes and match a different name to each one except for the 4th line where no name is mentioned - I noticed in column M their is no fourth match for DA16 3 and I assume their would be. If I manually enter Rhys Griffiths/CRLS/100479577 in column M for the DA16 3 postcode it does match Rhys Griffiths/CRLS/100479577.

    Is there a reason why column M did not work

    Problem 2 - with data from Sample Data 2st stage
    This time around the postcodes for matching appear much further down the list in row 301 on Route Allocation and I can not understand why the formula's only match 1 person to 1 postcode, should it not be able to match all the postcodes like it does when at the front of the list.

    Thank you for your help so far
    Attached Files Attached Files

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Can VBA improve the performance of this spreadsheet

    I can't follow the question because the attached workbook doesn't seem to match your details. I see nothing in H31:H34, not sure what to look at.

  5. #5
    Forum Contributor
    Join Date
    05-27-2014
    Posts
    141

    Re: Can VBA improve the performance of this spreadsheet

    My Apologies, I have cleaned up the data and realised some of my trouble, the names must always be alphabetically listed. I am trying to increase accuracy when postcodes are duplicated

    When matching single postcodes it works well (Now I am sorting the names in Postcode by DA), but I need to try and increase accuracy when a postcode is listed a second or third time.

    1st Stage, based on availability from Postcode by DA, there are 9 individual postcodes and from this stage the formula's return a 100% match

    2nd Stage, based on availability from Postcode by DA, there are 9 triplicated postcodes and 27 Names all with the correct information to be able to list 27 matches however only 12 are listed

    -------
    The Point of the File is to accurately match names from Postcode by DA into Route Allocation

    Calculations are made in DA Schedule to find any persons name with an X in the current day of the week
    Manual Entry here is normal for names and X's against the day of the week

    All the Names are list in Postcode by DA and can be listed upto three times with different postcodes, an X will be present in column A
    as a direct result of the day of the week
    Manual Entry here is normal for names postcodes

    The DA Available Coverage is the final list from Postcode by DA excluding any DA where an X is not present in column A of Postcode by DA
    This lists the persons name for each time they have a different postcode
    No Manual Entry here is made

    On Route Allocation Data is copied into Columns A:B, from here Column C identifies the postcode by sub sector for matching
    I need to accurately match as many names as possible against the relevant postcodes in column C, Postcodes maybe duplicated
    but the names must not
    Attached Files Attached Files

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Can VBA improve the performance of this spreadsheet

    First correction, Route Allocation K2, then copied down and across through M:

    =IFERROR(VLOOKUP($C2, OFFSET('DA AVAILABLE COVERAGE'!$A$1, MATCH(J2, 'DA AVAILABLE COVERAGE'!$B:$B, 0), , 1500, 2), 2, 0), "")

  7. #7
    Forum Contributor
    Join Date
    05-27-2014
    Posts
    141

    Re: Can VBA improve the performance of this spreadsheet

    That Formula worked a charm thank you, I have started to streamline some of the formula's and I was wondering if/how I can go about removing the Postcode by DA sheet. Currently DA Available Coverage filters out the available information from Postcodes by DSP. I am trying to see if I can get DA Available Coverage to look at DA Schedule to find those names with an X in column M and list all four postcodes THAT WILL BE in columns I:L

    I have placed an array in DA Available Coverage A20:30that works but the accuracy drops.

    Could you advise on a way that I might be able to get DA Available Coverage to list the names and all four postcodes directly from the DA Schedule in they have an X or even get Route Allocation G:J
    to find the information direct from DA Schedule

    Many Thanks for your help
    Attached Files Attached Files
    Last edited by gassiusmax; 12-22-2014 at 02:55 AM.

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Can VBA improve the performance of this spreadsheet

    AS it appears your need in this thread has completely changed, I'd suggest closing this thread and starting a new one. Your current workbook doesn't seem to present your actual new need very clearly. I would recommend a workbook that ONLY has the source sheets you want to use and a manually mocked up "results" sheet you're trying to get automated.

  9. #9
    Forum Contributor
    Join Date
    05-27-2014
    Posts
    141

    Re: Can VBA improve the performance of this spreadsheet

    Ok Thank you, sorry for troubling you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 07-22-2013, 09:50 PM
  2. [SOLVED] Improve Performance of Spreadsheet with Picture Links - GOLF!
    By a0123957 in forum Excel General
    Replies: 1
    Last Post: 05-30-2013, 06:40 PM
  3. improve performance
    By david90 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2013, 01:12 AM
  4. How to improve the performance of a looping UDF
    By johnnycanuck in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-03-2011, 01:39 PM
  5. [SOLVED] PageBreak problem. How to improve performance?
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-22-2005, 09:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1