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!

 

[Aladin Akyurek]

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

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

Try to sort column A on othersheet, if possible, and invoke:

=IF(VLOOKUP(R2,othersheet!A:A,1,1)=R2,VLOOKUP(R2,othersheet!A:A,1,1),"Not Found")

Replace comma with semi-colon if you are on a European system

 

[L. Howard]

The formula in my post #9 should be this.
I had some typos, and change ,'s to ; if needed.

.Formula = "=VLOOKUP(R2;othersheet!A:A,1,0)": .Value = .Value

I'm thinking once the formulas are in place, vlookup will still take a long time.

I have the feeling I am barking up the wrong tree.

Regards,
Howard