+ Reply to Thread
Results 1 to 9 of 9

Index Match slowing filters

  1. #1
    Registered User
    Join Date
    03-12-2018
    Location
    USA
    MS-Off Ver
    Depends on the computer being used
    Posts
    14

    Index Match slowing filters

    Hi,

    I am working a workbook I inherited from another user in the company. I want to create a dynamic copy of one table based on a unique set of one column. I created a new table pasted the unique values from the column I need in the new table and used index and match to populate the table data using a formula similar to
    Please Login or Register  to view this content.
    .

    This approach does populate the needed data, but there is significant lag time when trying to use any filters.

    Is there a better way to create a copy of the original table based on a unique set of one of the columns or a better way to populate the data?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index Match slowing filters

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-12-2018
    Location
    USA
    MS-Off Ver
    Depends on the computer being used
    Posts
    14

    Re: Index Match slowing filters

    The attached file is a simplified version of the workbook in question. The first tab is the raw data. The purpose of the second tab is to bring only the unique locations based on the New ATM ID column. The third tab is intended to be the basis for exporting data to another workbook. The actual width for the first two tabs is 85 columns and the current data set is just over 1000 rows.

  4. #4
    Registered User
    Join Date
    03-12-2018
    Location
    USA
    MS-Off Ver
    Depends on the computer being used
    Posts
    14

    Re: Index Match slowing filters

    Sample File Uploaded
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index Match slowing filters

    Thanks for the file, but sheet 2 contains all errors - perhaps you deleted (or renamed?) a sheet it is referencing?

  6. #6
    Registered User
    Join Date
    03-12-2018
    Location
    USA
    MS-Off Ver
    Depends on the computer being used
    Posts
    14

    Re: Index Match slowing filters

    The errors were an oversight on my part. I neglected to replace the live data with the sample data. Please see the attached file.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-12-2018
    Location
    USA
    MS-Off Ver
    Depends on the computer being used
    Posts
    14

    Re: Index Match slowing filters

    I am guessing from the lack of response there are few suggestions for this issue.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index Match slowing filters

    I think it's more a case of you knowing your data, but forgetting that we don't.

    I have looked at your workbook and have no idea (a) where I should be looking or (b) what results you are expecting.

    Please mock up your expected results manually, if you haven't already done so.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  9. #9
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Index Match slowing filters

    Instead of using lots of live INDEX/MATCH formulas, perhaps what you need is a "Refresh" macro that will paste these as values, either on-demand e.g. by clicking a [Refresh] button, or via an event-trigger e.g. by activating that particular worksheet.

    zeddy

+ 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. Adding filters into index/match/array formula
    By MonsterLobster in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-02-2018, 05:58 PM
  2. [SOLVED] Index, Match and filters
    By Steve M in forum Excel General
    Replies: 6
    Last Post: 07-28-2017, 11:12 AM
  3. Exact Match Lookups Slowing Down Calculations
    By majormorgan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2015, 11:24 AM
  4. Index/Match Function & Filters - NodeXL
    By r.cristobal in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-17-2014, 04:33 AM
  5. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  6. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  7. Replies: 3
    Last Post: 05-02-2013, 01:31 AM

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