• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

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

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
taskhill
Asked:
taskhill
1 Solution
 
Rob HensonIT & Database AssistantCommented:
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
 
taskhillAuthor Commented:
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
 
Alan VargaCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rob HensonIT & Database AssistantCommented:
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
 
cErasmusCommented:
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
 
Alan VargaCommented:
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now