Solved

convert VLOOKUP to Excel macro

Posted on 2011-09-19
20
456 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
Comment Utility
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:barnescs
Comment Utility
yes, this sounds correct  :-)
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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
Comment Utility
Everything works GREAT!!!!!  Thanks for all of your help!  :-)  cindy
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Glad to be of help!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

771 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

11 Experts available now in Live!

Get 1:1 Help Now