Solved

Populate a value in one spreadsheet based on values in another spreadsheet

Posted on 2016-11-15
6
38 Views
Last Modified: 2016-11-16
Hello Experts,

I have a workbook with three sheets.  In sheet 1 I have the following values

PID          Name            Age          Address    UseCount     UseScore
4125        Jack                24            123 St              4
9248       Stacy              46             461 st              6
4587       Frank              20            191 st              7
1346       George           36            150 st              6
4516       Andy               55            25  Av              3

In  sheet 3 I have values:
PID          ValueCount            UseScore
4587               4                          28
1346               1                           3

I want to populate the UseScore in sheet 1 with the UseScore from sheet 3 if the PID matches.

There are over 27,000 values in sheet 1 and over 7,000 values in sheet 3.

I have attempted several times to accomplish this on my own but I am a novice in VBA and this exceeds my knowledge.

I am using MS Excel 2013

Please let me know if I need to provide any further details.

Thanks
0
Comment
Question by:taskhill
6 Comments
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41888265
Don't need VBA to resolve, you can use a simple VLOOKUP formula.

Asuming Sheet3 data is in columns A to C, values in Sheet1 columns A to F and you need to populate column F; put this formula in F2, also assuming headers in row 1:

=IFERROR(VLOOKUP($A2,Sheet3!A:C,3,False),"")

That says look in column A of Sheet3 for the value in A2 and return the matching value from column C (column 3 of table). If it doesn't find the value it would return an error but this covered by enclosing it within an IFERROR which turns the error to "".

Copy down as far as required in column F. You can do this quickly by double clicking the bottom right corner of cell F2 and it will copy down as far as the data in column E.
1
 

Author Comment

by:taskhill
ID: 41888756
Thank you, the VLOOKUP worked and I will accept your solution.  However, I would really like to see the VBA that can accomplish this.  I had to go through several steps and VBA scripts to get the data to the example I provided above.  Ultimately, I want to condense all of the VBA I have created into a function so the user can simply click a button to provide the data in the format they need.  

Is there a way to accomplish the above with VBA?
0
 
LVL 1

Expert Comment

by:VieleFragen
ID: 41888853
Rob is correct, a VLOOKUP formula is better because it is more efficient, but if you want the VBA code, the attached file works on the sample data you provided.  I included comments, but if you have any questions, don't be afraid to ask.

IMPORTANT: Since this is a macro-enabled workbook, you should scan it (like any other attachment) with your antivirus software before opening it, just to be safe.

Alan
28983215-sample.xlsm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

Expert Comment

by:Rob Henson
ID: 41889355
If your data in Sheet1 is set as a table rather than a standard list, the formulas in column F will be populated automatically each time the table data changes; ie if the data range grows, the formula will be copied down as far as needed.

To set the data as a table, place the cursor somewhere within the data and press Ctrl+T and follow the steps.
0
 

Expert Comment

by:cErasmus
ID: 41889461
Hi
I Have attached a workbook containing some code that should execute a bit faster than Alan's code.
Let me know if you have any questions
Elmo
EE_taskhill.xlsm
0
 
LVL 1

Expert Comment

by:VieleFragen
ID: 41890259
Elmo,

Thanks for posting your code.  I didn't include the Application settings because I thought the original poster just wanted a proof of concept.  I had tried a direct updating of the column on Sheet1, but I must have left something out because I put this together in a hurry.

I didn't realize you could use
Cells(Rows.Count, 1).End(xlUp).Row

Open in new window

to find the last cell in a row or column - that's a neat trick!  I'm glad I checked in today; I'm always excited to learn something new.

Cheers!
Alan
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

911 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

22 Experts available now in Live!

Get 1:1 Help Now