Solved

Automate double vlookup using VBA

Posted on 2016-09-28
11
73 Views
Last Modified: 2016-10-12
Hi Experts - 

I am reposting this question as this question did not get any responses from the experts. The moderator requested to delete the question and repost it.

The question is simple - I want to automate the vlookup function using VBA.
But the code should have option to specify the lookup value, range and relative column reference by the user through inputbox.

The code can be similar to the one given below taken from this link.
http://stackoverflow.com/questions/38639249/vlookup-in-vba-excel-vlookup

But, if data is very large normal vlookup will be very slow and so I want to use the double vlookup method as given in the below link. For this I need the following 2 adjustments to the below macro -

1 - Need to add Original order column and fill it with continuous numbers equal to number of items excluding header. This is required if we need to go back to the original order in which the data was given.

2 - Ask user if data is sorted and if the answer is no ask whether to sort it on the lookupvalue column. This is required as the double vlookup will only work on sorted columns.

3 - I also need vlookup error trapping like #N/A etc. in the code.

Sub VlookupMacro()
Dim subject As Range
Dim sheet As Range
Dim clm As Long
Set subject = Application.InputBox(prompt:="select Subject to be looked up", Type:=8)
Set sheet = Application.InputBox(prompt:="select tab/range", Type:=8)
Set myResults = Application.InputBox("Please select on the spreadsheet the first cell where you want your lookup results to start:", Type:=8)
clm = Application.InputBox(prompt:="Relative Column Reference", Type:=1)
'Change the range to the cell into which you want the formula placed.
'Range(myResults, myResults.Offset(FinalRow - FirstRow)).Formula = "=VLOOKUP(" & subject.Address(0, 0) & "," & sheet.Address(0, 0, xlA1, 1) & "," & clm & ",False)"
[b]Range(myResults, myResults.Offset(FinalRow - FirstRow)).Formula = "=IF" & "(VLOOKUP(" & subject.Address(0, 0) & "," & sheet.Address(0, 0, xlA1, 1) & "," & clm & ",True)" & "=" & Range(subject.Address(0, 0)).Value & ", " & "VLOOKUP(" & subject.Address(0, 0) & "," & sheet.Address(0, 0, xlA1, 1) & "," & clm & ",True)"[/b]
End Sub

Open in new window


I have attached an example sheet so that it will be easier to suggest the solutions.

Double Vlookup Method - https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/

Experts, please write a response for this and don't again make this as a neglected question.

Thanks in advance
VBA_Vlookup_Example.xlsm
0
Comment
Question by:Vijayendra S. Murthy
  • 4
  • 4
  • 3
11 Comments
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
What Excel version you are using?
If you are dealing with a large data set, I would suggest you go for Power Query as an alternative method for VlookUp.
0
 

Author Comment

by:Vijayendra S. Murthy
Comment Utility
@Subodh Tiwari (Neeraj) - Thanks for your response.

I use MS Excel 2007 and use the Vlookup function often as my work is related to data. I will not always work with large data, but as I said earlier I use Vlookup function often. So If I get a generic VBA code to automate this, my work will become more easy.

Kindly suggest some solution for this.

Thanks
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 250 total points
Comment Utility
Why do you need VBA to input the Vlookup Formula on the Lookup Sheet while you can simply place the formula directly?

Anyways while I really don't understand the your purpose behind this, please find the attached with a button on Lookup Sheet called "VlookUp".
When this button is clicked, you will be prompted to input a Lookup Value, Lookup Table, Formula Cell, Column Index and Column on Input Sheet to have the original sort order.

The sorting is needed on Input Sheet not on Lookup Sheet. The code will place the sequence numbers on the Input Sheet starting from Row2 so that you can sort that particular column in Ascending order to get the original sort order back.
VBA_Vlookup_Example.xlsm
0
 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 250 total points
Comment Utility
please see attached.

Option Explicit
Sub MACRObyJIMJAM()

  ' By ProfessorJimJam  Answer to Question 28972914  October 3rd 2016
Dim LookupValue As Range
Dim LookupArray As Range
Dim COLINDEX As Long
Dim firstrow As Integer
Dim finalrow As Integer
Dim ResultRange As Range
Dim NotFound As String
NotFound = """Not Found"""
On Error Resume Next
Set LookupValue = Application.InputBox(prompt:="select LookupValue to be looked up", Type:=8)
On Error GoTo 0
If LookupValue Is Nothing Then
MsgBox " you cancelled it"
Exit Sub
End If
On Error Resume Next
Set LookupArray = Application.InputBox(prompt:="select tab/range", Type:=8)
On Error GoTo 0
If LookupArray Is Nothing Then
MsgBox " you cancelled it"
Exit Sub
End If
Dim OutPut As Integer
OutPut = MsgBox("Is the first column of LookupRange sorted?", vbYesNo, "Question of Sort")
If OutPut = 7 Then  '7 is No  &  6 is Yes
MsgBox "LookupRange Sort is Complete!"
With LookupArray
.Sort Key1:=.Columns(1), Header:=xlNo
End With
Else
End If
On Error Resume Next
Set ResultRange = Application.InputBox("Please select on the range first cell where you want your lookup results to start:", Type:=8)
On Error GoTo 0
If ResultRange Is Nothing Then
MsgBox " you cancelled it"
Exit Sub
End If
On Error Resume Next
COLINDEX = Application.InputBox(prompt:="Relative Column Reference", Type:=1)
On Error GoTo 0
If COLINDEX < 1 Then
MsgBox " you cancelled column index number required number"
Exit Sub
End If
On Error GoTo SingleCell:
 finalrow = Range(ResultRange.Offset(0, -1), ResultRange.Offset(0, -1).End(xlDown)).Rows.Count
On Error GoTo 0
Range(ResultRange, ResultRange.Resize(finalrow)).Formula = "=IF(" & "VLOOKUP(" & LookupValue.Address(0, 0) & "," & LookupArray.Address(True, True, xlA1, False) & "," & 1 & ",True)" & "=" & LookupValue.Address(0, 0) & "," & "VLOOKUP(" & LookupValue.Address(0, 0) & "," & LookupArray.Address(True, True, xlA1, False) & "," & COLINDEX & ",True)" & "," & NotFound & ")"
SingleCell:
ResultRange.Formula = "=IF(" & "VLOOKUP(" & LookupValue.Address(0, 0) & "," & LookupArray.Address(True, True, xlA1, False) & "," & 1 & ",True)" & "=" & LookupValue.Address(0, 0) & "," & "VLOOKUP(" & LookupValue.Address(0, 0) & "," & LookupArray.Address(True, True, xlA1, False) & "," & COLINDEX & ",True)" & "," & NotFound & ")"
End Sub

Open in new window

EE.xlsm
0
 

Author Comment

by:Vijayendra S. Murthy
Comment Utility
Thanks Neeraj & ProfessorJimJam for your replies. Both of the solutions are great and I will select both as solution.

I got one small doubt regarding the above process. I re-read the Charles page, but couldn't get the correct answer for that.

My doubt is that for the above process, should both the columns be sorted or just the column with the lookup value. Kindly clarify.

Thanks
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
You're welcome.
Only sheet with the lookup table should be sorted.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
you are welcome.
0
 

Author Comment

by:Vijayendra S. Murthy
Comment Utility
Thank you both of you for clearing my doubt.

@Neeraj - I request one more update to your code. When I used your code with around 160K, after the sort order input box, my excel hangs or it becomes very slow.

@ProfessorJimJam - Sir, your code also works fine but can you update a Original order as requested in previous comment.

Can't we just put consecutive numbers for both the input and lookup columns in the rightmost blank column and then sort?

Also, Can the code be extended for multiple workbooks i.e selecting input workbook and mapping workbbok. The inputbox works only for the active workbook.

I know that I am asking for more effort and time of yours, but please don't feel bad. I am a newbie in VBA and so exploring various methods for doing things.

I had another question related to this - automate vlookup using dictionary but following the same above mentioned process.

I had asked both questions previously, but got no responses, so I reposted one now and got your responses and if possible kindly look into this and provide a method for doing this.

Thanks Once Again
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
The code will take time for that much of data.
Try to add DoEvents line before each line of code which place the formula on the sheet.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
@Vijayendra S. Murthy

it is simple.  use a helper column next to the data and put 1 and then fill series 1,2,3,4,5,6,6,7,8,9,10 and so on.

then whenever you have run your macro and you want to return to original sort, then simply sort the 1,2,3,4 column back to ascending order and it will move back original state.
0
 

Author Comment

by:Vijayendra S. Murthy
Comment Utility
Hi Experts - Thanks for your response.

Can you please look into my other questions about extending this to multiple workbooks and using Dictionary to do vlookup.

Thanks once again for all your help.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now