Help with Excel File Size... Vlookups resulting in 90MB file

[shan1234]

Hi folks,

I have created a monster Excel file that contains loads of vlookups and is 95MB and extremely slow.

I guess that vlookup creates tables in the background, hence the file size. Does anyone have any ideas on how to decrease the file size while keep the vlookup functionality?

Many thanks in advance,

Shan
shan008@hotmail.com

 

[Aladin Akyurek]

What is the look up formula you are using?

 

[shan1234]

Well, I have used various vlookup formulas; I don't think the issue is in the formula but rather in excess usage of lookups...

My worksheet contains:
- 45 worksheets with 120 columns and 500 rows each and loads of lookups
- One consolidation worksheet with 90 columns and 4000 rows
- 5 Pivot Table reports

Here are some formulas I have used:....

=IF(ISERROR(VLOOKUP($D132,DefaultBOConfig,MATCH($L$2,DefaultBOConfigHeadings,FALSE),FALSE)),"No Data",IF(VLOOKUP($D132,DefaultBOConfig,MATCH($L$2,DefaultBOConfigHeadings,FALSE),FALSE)=0,"No Data",VLOOKUP($D132,DefaultBOConfig,MATCH($L$2,DefaultBOConfigHeadings,FALSE),FALSE)))


=IF(ISERROR(VLOOKUP($B8,INDIRECT($AL8),MATCH(AP$6,INDIRECT($AM8),FALSE),FALSE)),0,VLOOKUP($B8,INDIRECT($AL8),MATCH(AP$6,INDIRECT($AM8),FALSE),FALSE))

 

[WinteE]

Shan,

Is the ISERROR function being used for the case VLOOKUP finds nothing in the search-range ?

 

[Oaktree]

One thing that would help is, instead of formulas like:

=IF(ISERROR(VLOOKUP($D132,DefaultBOConfig,MATCH($L$2,DefaultBOConfigHeadings,FALSE),FALSE)),"No Data",IF(VLOOKUP($D132,DefaultBOConfig,MATCH($L$2,DefaultBOConfigHeadings,FALSE),FALSE)=0,"No Data",VLOOKUP($D132,DefaultBOConfig,MATCH($L$2,DefaultBOConfigHeadings,FALSE),FALSE)))

where you are performing the same VLOOKUP three times, use two cells: one for =VLOOKUP($D132,DefaultBOConfig,MATCH($L$2,DefaultBOConfigHeadings,FALSE),FALSE)) and another for =IF(ISNA(cell_with_vlookup),"no data",IF(cell_with_vlookup=0,"No Data",cell_with_vlookup))

 

[shan1234]

Will that really make a huge difference? I have already created the model so don't want to do any structural changes in case it all breaks down..

Is there another way to reduce the file size?

 

[scott1928]

This is a great site on how to reduce the size of your Excel file. Lots of simple mistakes that even experienced Excel users make.

http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm[/url]

 

[Oaktree]

I'd bet that will have a significant impact on your workbook. VLOOKUPs are expensive formulas, so reducing the number of them by 2/3 is worth considering.

You might have a look at: http://www.mrexcel.com/board2/viewtopic.php?t=125196 for some other ideas...

 

[WinteE]

Another possibility is to write your own function for this. Below you find some code that I made for combining vertical and horizontal lookup.

Public Function VERT_HORIZ_ZOEKEN(Bereik As Range, ZoekVerticaal As String, ZoekHorizontaal As String) As Variant

Dim Kolom As Variant
Dim Rij As Variant
Dim KolomTeller As Integer
Dim RijTeller As Integer
Dim ZoekKolom As Integer
Dim ZoekRij As Integer

    KolomTeller = 0
    RijTeller = 0

    For Each Kolom In Bereik.Columns
        KolomTeller = KolomTeller + 1
        If UCase(Kolom.Columns.Cells(1, 1).Value) = UCase(ZoekHorizontaal) Then
            ZoekKolom = KolomTeller
        End If
    Next Kolom
    
    For Each Rij In Bereik.Rows
        RijTeller = RijTeller + 1
        If UCase(Rij.Rows.Cells(1, 1).Value) = UCase(ZoekVerticaal) Then
            ZoekRij = RijTeller
        End If
    Next Rij

    If ZoekKolom = 0 Or ZoekRij = 0 Then
        VERT_HORIZ_ZOEKEN = 0
    Else
        VERT_HORIZ_ZOEKEN = Bereik.Cells(ZoekRij, ZoekKolom).Value
    End If

End Function

Put this code in a macro-module of the Excel-file and try it out. You can select this with the functionwizard of Excel at "User defined functions" afterwards.

Erik

 

[Jadendo]

Did you consider trying to use Index and Match together, I strip out all my lookups this way....