VLOOKUP Error huge data

[3nduranc3]

Hi All!

I have a pretty big problem here, and never met something like this, although I am using Excel a lot even doing macros.

I got a Big database from SAP. Namely 862 000 rows. Each have a Material Nr. in it, looking like this:

1156985-141-09

I have another Workbook which contains certain material numbers, And i want to know which are from it in the big one also (This has only 15k rows).

So I make a + column in the big file, and hit VLOOKUP. Thefirst value is found and gives the material number back. And here comes the problem. When I copy it to other cells down, it basically gives back the first value always. How is this possible?

I tried to copy value, copy formula, x1 works not because of format, its not number. I made a new file, formatted it general, text, number whatever, it always does the same. Tried xlsm, xlsx formats even.

And not just VLOOKUP, but a simple TRIM does the same.

Could you help?

Thanks a lot!

 

[VoG]

Is calculation set to Automatic?

 

[3nduranc3]

Is calculation set to Automatic?

Where exactly can I see this?

 

[L. Howard]

Excel 2010 - Formulas tab > Calculation Options > Automatic.

Regards,
Howard

 

[3nduranc3]

Hello Howard,

Thanks a lot, it works now, it is usually set to automatic, don't know why it wasn't now. Although, it is still very slow. Probably fom the mass data.

Thanks a lot!

 

[L. Howard]

It was VOG to offer up the Auto-Calc, I just knew where to go to check.

Regards,
Howard

 

[Aladin Akyurek]

Hello Howard,

Thanks a lot, it works now, it is usually set to automatic, don't know why it wasn't now. Although, it is still very slow. Probably fom the mass data.

Thanks a lot!

What is the formula you invoke?

 

[3nduranc3]

What is the formula you invoke?

Just a simple =VLOOKUP(R2;othersheet!A:A,1,0)

But at 860k rows it was like 45 mins to calculate them all.

 

[L. Howard]

Hi 3ndurance3,

Here is a long shot attempt to speed you macro up.
Change the L column to suit your sheet and run this little code on it.

I used it on 500,000 rows and the time was less than 2 seconds.
(see the formula I was using just below the End Sub)

Option Explicit

Sub MightWork()
  Dim lRowCount&, dTimer#
  dTimer = Timer: lRowCount = Cells(Rows.Count, "E").End(xlUp).Row
  
  With Range("L1").Resize(lRowCount)
        .Formula = "=VLOOKUP(R2;othersheet!A:A,1,0): .Value = .Value"  'puts return value in cell
        '.Formula = "=VLOOKUP(R2;othersheet!A:A,1,0):                          'puts formula in cell and return value shows
  End With
  
End Sub

.Formula = "=find(right(E1,13),E1)=8": .Value = .Value

Regards,
Howard

 

[L. Howard]

Woops, missed a little note you should know.

Notice the column in the lRowCount is E.

Set that column letter to a column that will represent how far down the column you want the vlookup formulas to go.

Or you may have to hardcode the Resize(850000)??