Solved

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

Posted on 2016-11-15
58 Views
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
[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

LVL 33

Accepted Solution

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

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

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

LVL 33

Expert Comment

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

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
0

LVL 2

Expert Comment

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
``````
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

Question has a verified solution.

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

### Suggested Solutions

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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month8 days, 17 hours left to enroll