Vlookup Loop

[Dani_LobP]

Hello again,

I am struggling with some huge file where i have to make some vlookup.

'Position Key
Sheets("Auto").Range("L1:L" & LastRowAuto).Copy Destination:=Sheets("Check").Range("A1")
LastRow = Range("A1").End(xlDown).Row

'Column1

Sheets("Auto").Range("Q1:Q" & LastRowAuto).Copy Destination:=Sheets("Check").Range("B1")
Range("C2:C" & LastRow) = "=VLOOKUP(A2,'Manual'!M:BQ,16,FALSE)"
Range("D2:D" & LastRow).FormulaR1C1 = "=RC[-1]=RC[-2]"

Basically there are 2 sheets with data. Around 200-300k rows each. Sometimes maybe even more.
One sheet is called Auto and the other is called Manual.

I need to compare both sheets and problem is that columns and rows are not in same order.

So what i did is copy one column that is with unique values that will help make it as the vlookup anchor (Column L from Auto sheet) and paste it in Check sheet (in column A).

And then have 24 columns to compare, so the code above will be repeated 23 times more.
First one being pasted in column B, then the vlookup will happen in column C and the check will be performed in column C.
Next column to check will be pasted in D... and so on... So each check consists of 3 columns, times 24.. you can imagine the mess with vlookup and so many rows.

I'm not very friendly with loops and similar but i'm sure this could easily be able to be coded with a loop.

My initial problem at moment is that the Vlookup formula line i have takes forever, since there are many rows and calculating everything at the same time, I'd be grateful to know some other way to make the vlookup, probably with another look checking one cell by one..

At the end the best would also be that copies whole column and paste it as values and then moves to the next column and do same process again..

Not sure if it makes sense 100%...

I'm open for feedback and looking forward to hear about some ideas in how to improve and make better what i did.

Thanks in advance.

 

[Dani_LobP]

Re: Vlookup Loop help

Anyone? :D

 

[Dani_LobP]

Re: Vlookup Loop help

Trying again... hopefully someone can help :P

 

[steve the fish]

Re: Vlookup Loop help

The problem here is, for me anyway, that its difficult to understand what you are doing, why you are doing it and its really hard to visualise your setup. Why are you doing this comparison? Doing 300k x 24 vlookups in one go will take a while. You could probably go for lunch.

 

[Dani_LobP]

Re: Vlookup Loop help

thanks for answering Steve,

I know, takes forever, testing macro with 1k or 10k rows seems fine, but the way is build it would take too long still. haven't dared to let it work with whole rows.

I have 2 files, generated by different tools. and i need to check that both files have same data. problem is that the columns and the rows are different orders.
but both share some column with unique values. which i use as the one to compare both files. so i have in the macro the column with unique values, and paste columns of one of the files, and vlookup those of the 2nd file.
makes sense?

What i try to figure out is how to implement a loop in my code, since what i currently do, is just paste a each column 1 by 1,run vlookup in new column... and then compare if both vlookup result and the column are the same.

The purpose is to find possible mismatches and then i need to fix it manually, but macro will help for sure to save time.

I think that a look that would do the vlookup, cell by cell and paste the cell result as a value and then move to the next one, will be much faster than vlookup whole column at the same time.

Makes sense?

 

[steve the fish]

Re: Vlookup Loop help

Would a concatenation of the rows work? Then you just need to test against the concatenation rather than each individual cell of each row.

 

[Dani_LobP]

Re: Vlookup Loop help

might help, i will have a try and see if that works to speed things up.
it should be possible to put columns in same order and then concatenate them all and compare rows.

 

[steve the fish]

Re: Vlookup Loop help

They dont need to be in the same order. Just need to concatenate in the same order.

 

[Dani_LobP]

Re: Vlookup Loop help

Been trying to find a way to do this with concatenate, so far looks better. But still haven't had time to finish it.

I try to make concatenate with different columns, but noticed i forgot to add separator, and can't find a way to apply separator in the way i did it.

With ActiveCell
.Formula = "= Q2 & S2 & T2 & U2 & V2 & W2 & X2 & Y2 & Z2 & AA2 & AB2 & AC2 & AD2 & AE2 & AF2 & AG2 & AI2 & AJ2 & AK2 & AL2 & AR2 & AS2 & AT2 & AU2"
End With
Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1))
ActiveSheet.Calculate

This would concatenate all those columns, but as a big long line and drag it all the way till the last row based in column to the left... not efficient for later when i actually try to Trim and undo concatenate in further step.
Need to add some separator so makes the trim easier later on.

Hopefully makes sense