Solved

convert VLOOKUP to Excel macro

Posted on 2011-09-19
20
464 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
  • 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
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 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 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