Solved

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

Posted on 2016-11-15
6
33 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 31

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 31

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

747 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

12 Experts available now in Live!

Get 1:1 Help Now