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

x
?
Solved

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

Posted on 2016-11-15
6
Medium Priority
?
74 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
[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
6 Comments
 
LVL 33

Accepted Solution

by:
Rob Henson earned 2000 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 2

Expert Comment

by:Alan Varga
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
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: 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
 
LVL 1

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 2

Expert Comment

by:Alan Varga
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

636 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