Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

convert VLOOKUP to Excel macro

Posted on 2011-09-19
20
Medium Priority
?
490 Views
Last Modified: 2012-05-12

Is it possible to convert this to a Excel macro?

Use a vlookup formula:

=VLOOKUP(B2,'[other workbook.xlsx]Sheet1'!$A$1:$B$1000,2,false)
for example, assuming department ids in column B in the employee workbook, and the source data in columns A and B in the other workbook.
0
Comment
Question by:barnescs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
20 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 36560321
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36560358
Depends what you mean by 'Convert to a macro'!!

Do you mean create a macro that will populate cells with a VLOOKUP formula or do you mean create a macro that will return the same result as a VLOOKUP formula?

Please clarify.

Thanks
Rob H
0
 

Author Comment

by:barnescs
ID: 36560398
A macro that will return the same result as a VLOOKUP formula.  i have a worksheet with 2 VLOOKUPs on it and it would be easier for the users to have a macro to use instead of the 2 VLOOKUP formulas.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 36560558
Seems a bit overkill to have a macro that will replace 2 formulas.

Can you expand a bit as to why the 2 formulas are not suitable? Maybe we can find another way around it.

Thanks
Rob H
0
 

Author Comment

by:barnescs
ID: 36560703
i have users that run various reports that contain employee's name and department.  The users want to take the data that they have and then by some means match the department number on their report to a list that contains dept. numbers, department descriptions, and supervisors names.  I have written 2 VLOOKUP formulas to do this; one pulls department description, and the other the supervisor.  I was trying to make things easier for the users and thought a macro would be easier than 2 VLOOKUP formulas.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36560872
I take it they have a list of Employees' names and departments against which they are populating the 2 formulas.

Assuming the size of the list varies from report to report, the number of formula required for the whole list would be variable.

Do the users already have one copy of the formula which they are currently copying against all rows in their particular report? Is this the bit that needs making easier?

Thanks
Rob H
0
 

Author Comment

by:barnescs
ID: 36560926
The reports can vary in size from a couple of lines to over 100 lines.  This is a new project that i have been given so no, the users do not already have one copy of the formula.  I am creating this from scratch and with the level of management that will be using this, i need for it to be as simple as possible.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36561009
How will the users get the reports they require?

Will they download the data themselves into a new workbook? If so how are you intending to make sure the macro/formula is populated in the new workbook?

By "already have the formula" I meant will there be one copy of the formula in the workbook containing their data which will need copying against all lines.

Thanks
Rob H
0
 

Author Comment

by:barnescs
ID: 36561086
the users will be downloading to excel reports from various programs.  I intended to set up a workbook for them with them always copying their data into the first sheet and then the formulas/macros would be on the 2nd sheet.  They could then copy them over to the first sheet and have the information that they are needing.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36561159
So the plan is:

You will prepare a workbook for the Management User

That workbook will have:
  Sheet1 for their data which they will download and paste into the sheet
  Sheet2 previously prepared VLOOKUP formulas for copying into Sheet1

You want to make sure that the formulas previously prepared on Sheet2 don't get destroyed and are copied against sufficient lines on Sheet1 for the number of rows of data downloaded. Manual copy and paste would be an option but a click of a button to do it for them would be preferred.

Does that sound correct?

Thanks
Rob H
0
 

Author Comment

by:barnescs
ID: 36561190
yes, this sounds correct  :-)
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36561284
How about making use of a little Excel trick?

In the header row (assuming row 1) of Sheet1 I assume you will have column 1 "Name", column 2 "Department" or something similar.

In columns 3 & 4, or where the lookup formluas are going, amend the formula to include an IF statement looking at the contents of the header cells in that row:

=IF(A1="Name","Header",VLOOKUP(B1,'[other workbook.xlsx]Sheet1'!$A$1:$B$1000,2,false))

When the user has then populated their data, if they hover the mouse over the bottom right hand corner of the cell it will change to a + sign. Double click at this point and it will copy the contents of that cell down until it reaches what it thinks to be the end of the data, assumes the data in one continuous block.

As you have 2 formulas to copy down, highlight both cells and double click the bottom right of the right hand one, this will copy both down.

Basically, in the header row the IF statement will detect the header "Name" and return the appropriate header for that row otherwise it will do the lookup.

The header row cells can then be protected so that the user cannot overwrite them with the new downloaded data.

Thanks
Rob H
0
 

Author Comment

by:barnescs
ID: 36561320
With some of the reports having different formats could =IF(A1="Name","Header",VLOOKUP(B1,'[other workbook.xlsx]Sheet1'!$A$1:$B$1000,2,false))
be changed to a range say =IF(A1:A5="Name","Header"...........?
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 36561358
If the headers will always be in row 1 then:

=IF(ROW()=1,"Header",VLOOKUP(B1...

However, will the lookup values be in the same column for each different report format? Column B for the example formula.

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36561412
Sorry, think I might have misread that question. For example, the download might have rows of spurious data above the headers eg:

Date
Username
Report title
Name             Department           LOOKUP Result = Header
Data                Data                      Result = Info
Data                Data                      Result = Info

In this case the Headers would be on row 4. The headers are being downloaded with the data so might not fit in with the formulas.

Is this what the scenario could be like?

Thanks
Rob H
0
 

Author Comment

by:barnescs
ID: 36561782
the headers would always be in row 1 so i'm going to try the =IF(ROW()=1,"Header",VLOOKUP(B1...
to see if that will work.  I have a 12:30 (central time) meeting to go to so i will try this when i get back and let you know how it works.  THANKS very much for all of your help!!!!!!!  :-)
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36561887
Now 18:30 BST (GMT+1) so will be heading home soon. I will catch up with this in the morning or if I get bored at home this evening, wife and kids away during the week.

Thanks
Rob H
0
 

Author Comment

by:barnescs
ID: 36561977
sounds good.  i have gotten a little hot project to do so i will have to pull away from this for a little while to pull data together for this.  if nothing else i will be in by 5:30 am tomorrow and will try it out then while it is still nice and quiet.  Thanks for all your help.  You make things so easy to understand.  :-)
0
 

Author Comment

by:barnescs
ID: 36566188
Everything works GREAT!!!!!  Thanks for all of your help!  :-)  cindy
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36566360
Glad to be of help!!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

610 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