File too big?

[kenjohnson_342]

2010 Excel
Running on HP Thin Client server

I have a 12mb file with 308,000 rows of txt and a mere 6 columns. I have one column vlookup formula.
As txt I can filter like normal. After adding a Vlookup column and a summary sheet containing 6 cells with a sumif. In my mind, not a terrible amount of formulation, zero script.

Problem is when I get to this point if I try and add anything else or simply filter out something from my data set on sheet1, it takes 30+ minutes to filter. I have even taken off the auto-calculate function in case that helps (which it hasn't).

IT consultants for my company suggest that 308,000 rows is simply too large for Excel to compute and they are trying to talk me into using smaller data-sets which is useless for my report. This is a very small piece of what I'm trying to grow this report to, but am I over-using Excel? Or is there something I'm missing which could help speed this back up to what I expect.

Thanks for any help folks!

 

[oddworld]

Try using the below UDF

1. Function VLOOKAllSheets( _
2. Look_Value As Variant, _
3. Tble_Array As Range, _
4. Col_num As Integer, _
5. Optional Range_look As Boolean)
6. ''''''''''''''''''''''''''''''''''''''''''''''''
7. 'Written by OzGrid.com
8. 'Use VLOOKUP to Look across ALL Worksheets and stops _
9. 'at the first match found.
10. 'With a small modification by Markmzz
11. '''''''''''''''''''''''''''''''''''''''''''''''''
12. Dim wSheet As Worksheet
13. Dim vFound
14.
15. On Error Resume Next
16. For Each wSheet In ActiveWorkbook.Worksheets
17. If wSheet.Name <> "Master" Then
18. With wSheet
19. Set Tble_Array = .Range(Tble_Array.Address)
20.
21. vFound = WorksheetFunction.VLookup _
22. (Look_Value, Tble_Array, _
23. Col_num, Range_look)
24.
25. End With
26.
27. If Not IsEmpty(vFound) Then Exit For
28. End If
29. Next wSheet
30. Set Tble_Array = Nothing
31. VLOOKAllSheets = vFound
32. End Function


1. You must to save your wookbook like a macro enable workbook.

And use the function like this:
Code:
=VLOOKAllSheets(A1,A$1:B$40000,2,0)

 

[shawnhet]

This link has a bunch of good suggestions IMO.

Excel 2010 Performance: Improving Calculation Performance

Cheers,