VLOOKUP Performance Is Taking Very Long Time To Recalculate
Jan 11, 2010
I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long. Here is what the spreadsheet is designed to do:
We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.
I have a couple shreadsheets I have created, one is for Purchase order's and has 4 different sheets. with one sheet holding addresses linked a drop down menu in the first sheet, all in the same workbook. Also there is a macro within this sheet to print to a specific printer, and a specific number of copies when CTRL p is pressed. The total file size is approx 9 mb. Opening this file takes approx 30 to 45 seconds. There are no links to any outside files, only links within the workbook itself.
The second File I have recently started having problems with (Our Quotation log) is A workbook containing 3 sheets, 2 sheets independant and one sheet linked to the second that Summarizes the first and also has cels to enter information on the person the quotation has come from. I have this sheet formatted as a table so I can sort by name etc when doing followups.
This workbook also takes approx 30 to 45 seconds to open, and on occasion longer. All workbooks are stored on my computer. The PO workbook has had the problem since I created it, although the Quotation log workbook has only had the issue this week.
I have a macro that is taking a long time, so long that I think something is wrong but I dont really have a great way to check. I want to put up a little window showing the progress either by saying "i'm on row XX" or having a progress bar.
Looks like msgbox requires action before the code continues so it is not a good thing to use if you want to get progress. My formula looks for bad values in a data file and fixed them. It takes forever. I would like to have a box there that simply tells me what row it is working on.
Clearly I dont want to hit the OK button for it to continue processing. I want it to update the message box on its own and then take away the message box when it is finished processing.
I've got a sheet which I need to analyse and split into several different sheets but the raw data format leaves a lot to be desired as there are blank rows inserted randomly between rows of data. I need the 'good' data to stay in the same order so I've written a macro to sort through the data and delete any blank rows leaving the good stuff behind.
The problem is this takes ages as there can be up to 30000 rows that need to be checked and I need to do this 5-6 times a day. I just wondered weather there was a quicker way to do this? The code I've got is detailed below:
Sub Prep2() 'Delete all blank data rows Dim Rows As Double Dim Rownum As Double Application.ScreenUpdating = False Rows = Selection.SpecialCells(xlLastCell).Row
For Rownum = 2 To Rows If Cells(Rownum, 11) "" Then GoTo NxtRownum Else
Cells(Rownum, 11).EntireRow.Delete shift:=xlUp
Rows = Rows - 1 NxtRownum: Next Rownum Application.ScreenUpdating = True End Sub
I have a data range with just under 100,000 lines by about 60 columns. In one of the final columns I used the formula =IF(COUNTIF($A$2:A2,A2)=1,1,0) in order to set a flag the first time that each unique value in Column A appears. (I know about the remove duplicates function but the data was sorted in a certain order and I needed information specifically from the first row in which each unique value appears.) The formula works - it's not very quick, but it works.
The issue I'm encountering though is that now I want to remove that formula so it doesn't keep recalculating and every technique I use either takes forever or stalls Excel entirely. I've seen the same issue in two different spreadsheets where I used the same technique. Techniques I've tried: selecting and deleting the entire column, copying and pasting values over the formula, Selecting all the cells and pressing delete, copying and pasting an empty cell over them, Selecting and cutting and pasting the cells to another workbook. I've done these while disabling automatic recalculation and even turning off Data Filtering. I just can't understand why a simple delete operation should take so long, it shouldn't matter what is in the cell because all I want to do is delete it.
The above list represents 2 columns. The left column is 400 lines long. The right column is partially complete with 2 letter codes that represent the 6 letter codes on the right. I'm trying to convert the column in the XXYZXX format to a 2 letter column and each of those 2 letters corresponds to the 6 letter column on the left. How do I do this using any method or Vlookup? How does one convert a column of text to a corresponding column of text? I don't want to have to manually type in 2 letter codes that match up the left column. What do I do instead?
My excel file consists of 15 sheets, thereof 4 that are purely graphs, the others have tables with formulas. When opening the file or changing values, the entire sheet is being recalculated every time which takes a few seconds. Is this normal or can one tell to recalculate at another stage?
The activities sheet has a date macro (double click in date cell). The workbook has a macro for the footer information.
I have to fill in data in excel sheets and I am using VLOOKUP. The sheet has upto column AF and 1048500 rows. I have copied the data table (table array used for the vlookup) after column AF and the data table has about 70000 rows. One formula is to be filled in column AF and another in column AG. Filling in the formula is taking up to 3 hours for a column!! how i can improve the processing speed without breaking the file into smaller files? There are no other formulae in the sheet - i've replaced all formulae with values.
I have created a simple spreadsheet to keep track of work hours. I simply enter in each days hours and then I get a total. I have each cell formatted for time (hh:mm). However it is annoying to have to type in the colon for each days time.
Is there a way I can just type in the three digits '9,3,0' into the cell and have it come out as '9:30'?
I'm using the excel built in function to sort columns with my macro. However since my columns have 64,000 entries it takes a while for the computer to sort it.
i always receive 200 mb file. which i has, around 10 sheets , with pivot tables and formulas.
i have to open this file update refresh and save as this file, which i am doing via vba.
however the challenge for me here is though i set xl calculation to manual at the begining and xl calculation to automatic at the end. Though i used this code, its taking close to 5 mins to complete this via Vba.
I've got 3 columns of formulas that end up doing what I need, but I'm thinking it could probably be done easier with VBA.
I have 1 book with a sheet I'll call Log & another book with a sheet I'll call Source. On the Log, column E has the first 9 characters of vendor names & /vendor number (ex: EDMUND FI/00250), along with other data out through column P. On Source, the vendor names are in C & a short code for their name is in A.
I need to compare the first 9 characters in Log col E with the first 9 characters in Source col C. When a match is found, I need to return the short code from Source col A. BUT - if there's a duplicate in either Source col C or A, instead I need to return the word VERIFY, preferably with the cell highlighted in red.
Right now, I have, on Source col L:
Code: =MID(C2,1,9) and in source M:
Code: =IF(OR(A2=A1,A2=A3,L2=L1,L2=L3),"VERIFY",A2) This gives me the first 9 characters of the vendor name in L & the short code OR VERIFY in M
Then, in Log col R, I have an array formula:
Code: =VLOOKUP(MID(E4,1,9),'[PRETICKET P ADDRESSES working.xls]Paddress'!$L:$M,2,FALSE)
This gives me either the short code or VERIFY from Source M. Also, sometimes it doesn't find a match & it returns #N/A. Then I have to do Conditional Formatting to make the VERIFY cells red & I thought I'd make the error cells be yellow (although I haven't figured out the CF for that yet)
Is there a better way to do this with VBA? I'm working with Excel 2010; just got it & still figuring it out.
I managed to put together an array formula to calculate the last date that a rep made a sale. It checks two other tabs in the workbook to find the date, and if none is found, it leaves the cell empty.
{=IF(MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A))=0,"",MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A)))}
But it takes sometimes up to 4-5 minutes to make the calculations. Is there possibly a way to simplify it so that it calculates faster, with the same results?
to calculate times worked where $D17 is start time and $E17 is finish time. Shifts can start and finish the same day ($E17>$D17) or go past midnight ($D17>$E17).
To make matters even more interesting, there are a number of variables that require adding either "and" and/or "or" conditions to this formula so I can end up with several of these nested and it gets pretty complicated.
I'd like to have a UDF so I can just enter the formula:
Attached is the mock excel spreadsheet. I want to read "sam" from the lookup column's long text "sam is good" and then write "4" in the next column. Similarly read "white" from the long text "white is tired" and write "1". And so on,,,,, For more criteria, see box highlighted in yellow,,,,I used VLOOKUP but what am able to make it work only when there is one word "sam" in the lookup column. It returns #NA when the text is "sam is good". It should write nothing if none of the criteria is met and should keep doing until the last cell in the lookup column.
Excel 2003. When the Vlookup List becomes impractably long to select data from the cell dropdown. Is there an alternative prefered method to entering data from the long list?
I have a long date and time text value in a cell such as "2013/10/02 07:43:39.39", where the fractions of a second are very important.
I can use cdate([text]) to convert the text string to a value, but only if i omit the seconds fraction: "2013/10/02 07:43:39". Otherwise i get a type mismatch error.
Is there any way i can get the whole date/time string converted to a date value? The only way i can think to do it it at the moment is to convert the date/time, then divide the seconds fraction by 86400 and add the 2 values. Is there a better way?
Secondly, i have a large number of these date strings, typically >30k lines x 7 columns. Each string is preceded and trailed by a [space] character.
I can strip these extra spaces no problem. And, i can "loop" to perform it, again no problem. But it's going to take time to perform and i'd rather not if i can avoid it.
Excel takes about 10 minutes in the saving process. When I say 10 minutes, I mean, the excel screen freezes (says not responding) for about 10 minutes, then it actually saves at the very end in the normal time any other file would take as you watch the progress bar go forward.
I know many of the common answers and have tried. reducing the calculation time (which in turn reduces the saving time).
But in my circumstance, the calculation takes a very reasonable amount of time, and you see the progress % going forward.
- I would say I have about 2000 rows, and 15 columns. - They have sumifs formulas. - They link to a different workbook. - The workbook I am working on saves to the network - the source of my sumifs are also in the same folder on the network - the recalculation takes about 10 seconds at most - i have turned off recalculate before saving, it is all on manual calcs
- when i hit save, there are no calcs being performed - there are no macros in the workbook - there are only about 2 names in the name manager - then it freezes for about 10 minutes. - then the progress bar starts moving then it saves.
What is it doing in those 10 minutes?
1 more item to note, when I break the links to the workbook and thereby removing the sumifs formulas, its a snap.
Why does the existence of the sumifs extend saving time? I would completely understand if it elongated calculation time, but if calculation is off, then why does it even worry about it when saving?
I have cells of data with a long string of numbers such as: 20090507225836. Is there any easy way to convert them to date/time format, such as below? 5/7/2009 22:58:36
I am using the below formula to distinctly count the number of customers that match the criteria that I have in Cells C7 and B10. The data is in a separate worksheet, that I am showing Named Detail of which will be changing on a monthly basis, so a pivot table does not want to be used. The detail data ranges from row 7-40,000, and the file is currently 8610KB's, and can potentially grow.
This formula works but takes an excessive amount of time for one caluclation, and I need this for multiple column and row critera. So, can this calculation be changed in order to get the same result with faster calculation time? I am using Excel 2003.
I have the VB Editor open and am manually activating different workbooks in Excel (with 20/25 modules each), the VB Editor goes through a process of maximising each and every module in the workbook I have selected before I can edit any code or, indeed, do anything in the workbook. This process takes about 8-10 seconds every time I select a different workbook in excel! I have played around with the settings "Full Module View" in the options section of the VB editor, but to no effect.
I need to ask user to input the row ("rowin") and column ("colin") address in order to activate a cell. The mose natural inputs from the user will be an integer for row and one or two alphbats for column. Say, for example, the user input (82, AY), how do I represent the cell in code? I know I want to say something like Range("AY82"). But I just could not figure out how to concatenate "colin" & "rowin" to make it AY82 to Excel.
I have a large Excel 2007 file, around 60.000KB. 54.000KB are due to one of the worksheets where I have 8760rows x 160columns with data. The calculation time is not a problem, it is very fast, it only takes 2/3 seconds. The problem is when I open or save the file, it takes around 2 minutes... it is not too much, but it becomes too long when one has to open and save it several times. It there any trick to decrease the time when openning or saving an excel file??
I'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries Mar Apr May Jun
Loans to banks Mar Apr May Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
I have a relatively small Excel workbook that is using numerous lookup and Match function combinations. I also have a number of dynamic ranges defined and a few UDFs.
Problem is I am encountering some fairly severe performance issues. Changing a single value on a sheet is taking ages.
I suspect it may be due to the workbook dynamic ranges recalculating. Is there a limit to the amount of dynamic ranges in a workbook? And when do they get recalculated? Could it be something to do with sequencing of the calculations?
