Results 1 to 9 of 9

Thread: Vlookup Loop
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup Loop

    Hello again,

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

    Code:
    '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.

  2. #2
    New Member
    Join Date
    Aug 2019
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup Loop help

    Anyone? :D

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup Loop help

    Trying again... hopefully someone can help :P

  4. #4
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,644
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default 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.
    Looking for opportunities

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?

  6. #6
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,644
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default 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.
    Looking for opportunities

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  8. #8
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,644
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Vlookup Loop help

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

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    Code:
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •