• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Faster way to match two columns

T

Tim Hanson

Member
Dose anyone have a faster way to do this Match two columns.

I have a project with a file that is about 110,000 rows and might grow to 250,000 rows and this macro is vary slow to run

Also I have never worked with files over say 10,000 rows is there anything I need be aware off for larger files?, dose code that works on smaller files always work on larger files (maybe this is a silly question but I ask it anyway)

Thanks for any help

Code: 
Sub MatchPermissionGiverAndTarget()
    Dim LastRow As Long
    Dim ws As Excel.Worksheet

  GoFast False

    Set ws = ActiveWorkbook.Sheets("Helper")
    LastRow = ws.Range("A" & ws.Rows.count).End(xlUp).Row
   
    Range("E1").EntireColumn.Insert
    Range("E1").FormulaR1C1 = "name"
   
    With ws.Range("E2:E" & LastRow)
        .Formula = "=INDEX(B:B,MATCH($D2,$B:$B,0))"
        .Value = .Value
    End With
   
    Columns("D:D").EntireColumn.Delete

    GoFast True

End Sub

Code: 
Sub GoFast(Optional Reset As Boolean = False)

' Set various application properties.
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

End Sub
 
The macro basically uses a formula. And probably that will be the major time your code could be taking.

Test this on a backup.
Code: 
Sub FindMatches()
Dim objDic As Object: Set objDic = CreateObject("Scripting.Dictionary")
Dim lngLastRow As Long: lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
Dim varSource As Variant: varSource = Range("B2:B" & lngLastRow).Value
Dim varDestn As Variant: varDestn = Range("D2:D" & lngLastRow).Value
'\\ Build source array
For i = LBound(varSource) To UBound(varSource)
  With objDic
  .comparemode = vbTextCompare
  If Not .exists(varSource(i, 1)) Then
  .Add varSource(i, 1), varSource(i, 1)
  End If
  End With
Next i
'\\ Build Destination array
For i = LBound(varDestn) To UBound(varDestn)
  With objDic
  .comparemode = vbTextCompare
  If Not .exists(varDestn(i, 1)) Then
  varDestn(i, 1) = "#N/A"
  End If
  End With
Next i
'\\ Post back the results
Range("D2:D" & lngLastRow).Value = varDestn
End Sub
 
Hi Tim ,

Not taking away anything from Shrivallabha's solution , did you check your code with finite limits for the range references , instead of using entire column references ?

1. Your code is finding out the last row of data in column A of the sheet labelled Helper.

2. Your code has the following statement :

.Formula = "=INDEX(B:B,MATCH($D2,$B:$B,0))"

which could make use of the LastRow variable , which is storing the row number of the last row with data.

The rewritten code would be :
Code: 
Sub MatchPermissionGiverAndTarget()
    Dim LastRow As Long
    Dim ws As Excel.Worksheet
    Set ws = ActiveWorkbook.Sheets("Helper")
    LastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
  
    'Range("E1").EntireColumn.Insert
    Range("E1").FormulaR1C1 = "name"
  
    With ws.Range("E2:E" & LastRow)
        .Formula = "=INDEX($B$2:$B" & LastRow & ",MATCH($D2,$B$2:$B" & LastRow & ",0))"
        .Value = .Value
    End With
  
    'Columns("D:D").EntireColumn.Delete
End Sub
where I have modified the column for calculating the LastRow variable.

What exactly is the objective of this code ; do you wish to find out if there is a match ( TRUE / FALSE result ) or do you want the matching value to be returned ? Try using VLOOKUP instead of the INDEX / MATCH combination to see what the difference in speed is.

Narayan
 
Hello Narayan

I need the matching values to be returned

I "Found this and have tried to alter it, I have not used VLOOKUP yet so I read some to see if I could get ti right, the code executes but noting is returned

Code: 
Sub HTH()
Dim ws As Excel.Worksheet
Dim LastRow As Long

Set ws = ActiveWorkbook.Sheets("Helper")
Application.ScreenUpdating = False

LastRow = ws.Range("B" & ws.Rows.count).End(xlUp).Row
Range("E1").EntireColumn.Insert
Range("E1").FormulaR1C1 = "name"

With ws.Range("E2" & LastRow).Offset(, 1)
    .Formula = "=VLOOKUP(B2 & LastRow,D2 & LastRow:D,1,FALSE)"
    .Value = .Value
    .SpecialCells(xlCellTypeConstants, 16).Offset(, -1).Copy Range("D" & Rows.count).End(xlUp).Offset(1)
End With

Application.ScreenUpdating = True

End Sub
 
Shrivallabha,

Is it possible to alter your code so that it matches like the code below, it is essentially the same as the original code I posted that you wrote the original macro for but it matches across sheets
I do not understand what you did to atempt it myself)

Thank you

Code: 
Sub MatchPermissionGiverAndbushSource()
    Dim lastRow As Long
    Dim ws As Excel.Worksheet
  
    Set ws = ActiveWorkbook.Sheets("Helperbush")
    lastRow = ws.Range("A" & ws.Rows.count).End(xlUp).Row
  
    Columns("E:O").Insert Shift:=xlToRight
  
    With ws.Range("E2:O" & lastRow)
        .Formula = "=INDEX('Helper'!A:A,MATCH($B2,'Helper'!$B:$B,0))"
        .Value = .Value
    End With
  
    'Columns("D:D").EntireColumn.Delete 'Source'!
  
End Sub

I tried this but not enough

Code: 
Dim varSource As Variant: varSource = Sheets("Helper").Range("B2:B" & lngLastRow).Value
Dim varDestn As Variant: varDestn = Sheets("Helperbush").Range("B2:B" & lngLastRow).Value
 
Last edited:
Hi Tim ,

The problem is in this line :

.Formula = "=VLOOKUP(B2 & LastRow,D2 & LastRow:D,1,FALSE)"

The Formula method requires a string which evaluates to an Excel worksheet formula ; thus if you had something like :

.Formula = "=VLOOKUP(B2,D2:D100,1,FALSE)"

it would work. Since you wish to use the LastRow variable to define the endpoint of the lookup range , instead of the static D100 , you will need to change it somewhat. However , my second point is that your original formula looked up each value in column D with the data in column B ; however here you are trying to lookup each value in column B with the data in column D ; which is correct ?

Assuming that your original formula was what you wanted , the VLOOKUP version would be :

.Formula = "=VLOOKUP($D2,$B$2:$B" & LastRow & ",1,False)"

Narayan
 
Narayan, thanks for all your help!

I am trying to lookup each value in column B with the data in column D and then return the matched value.

I tried
Code: 
.Formula = "=INDEX($B$2:$B" & LastRow & ",MATCH($D2,$B$2:$B" & LastRow & ",0))"
and it was a little bit faster but on 40,000 rows it took about 4 minutes to run.

I am continuing to try and get
Code: 
Formula = "=VLOOKUP($D2,$B$2:$B" & LastRow & ",1,False)"
to work, right know it runs but returns nothing.

Tim
 
Tim,

In the code posted in post #6, it looks like you are trying to get the sliding matches. This can be done with some changes in the code but is that your clear and complete requirement or there are still some things which you've not considered?
 
Hello, shrivallabha I am trying to use your code in two different situations the original post and also where I need to return not just the matching name from col B but the cells on the same matching row from cols C through col K from sheet("Helper")

I am trying to replace this

Code: 
Sub MatchPermissionGiverAndbushSource()
    Dim lastRow As Long
    Dim ws As Excel.Worksheet
   
GoFast False

    Set ws = ActiveWorkbook.Sheets("Helperbush")
    lastRow = ws.Range("A" & ws.Rows.count).End(xlUp).Row
   
    Columns("E:O").Insert Shift:=xlToRight
   
    With ws.Range("E2:O" & lastRow)
        .Formula = "=INDEX('Helper'!A:A,MATCH($B2,'Helper'!$B$2:$B" & lastRow & ",0))"
        .Value = .Value
    End With
End Sub


Thank you
 
You must log in or register to reply here.
Back
Top