PDA

View Full Version : How to auto populate an excel spreadsheet with data from another spreadsheet?



wedd
02-21-2012, 03:53 AM
How to auto populate an excel spreadsheet with data from another spreadsheet? I have EmployeeIDs on worksheet1 that are exactly the same on worksheet2, and I would like to autopopulate it with data from columns in worksheet2 Job Type and Assignment Type into worksheet1 which have the exact column names but are blank. Can this be done? If so, how can this be done? I've heard it can be done using V-Lookups, but I only know how to use simple v-lookups on a single sheet. Can you provide an example of how I can use a vlookup to do this?


Thanks for your contributions. :friends: This is a great learning curve for me.

Bob Phillips
02-21-2012, 04:28 AM
Just as simple

=VLOOKUP(Sheet2!A2, Sheet1!A1:M100,5,False)

or some variation thereof.

wedd
02-21-2012, 05:25 AM
Thanks, xld! The formula is correct. But I have over a 1000 records, and I keep getting the circular reference error. Have you a reason how I can overcome this issue...


Thanks!

wedd
02-21-2012, 05:46 AM
Hi xld, I did the formula and it just returns back a 0 in the sheet where the data is suppose to be populated.

Bob Phillips
02-21-2012, 05:53 AM
A circular reference means you are referencing the cell itself, directly or indirectly. Without seeing the workbook, it is hard to say how/why?

wedd
02-21-2012, 05:57 AM
oK, I'll send you the spreadsheet...

wedd
02-21-2012, 06:07 AM
Hi xld, I have uploaded the file. It is question 2.


Thanks!

wedd
02-21-2012, 06:16 AM
Sorry question 1

Bob Phillips
02-21-2012, 06:45 AM
It can be done but it is incredibly slow with that much data, that many array formulae.

Do you expect these assessees to be ablt to figure out a formula like that? Bit ironic that you are seeking help on an Excel spreadsheet that you will test to assess others Excel competency.

wedd
02-21-2012, 06:52 AM
Hi, xld! I am actually testing it on myself...a friend sent it to me...it is running very slowly indeed, my computer keeps crashing every time I try to run the formula...

wedd
02-21-2012, 06:54 AM
Should I follow your example you mentioned to me earlier...maybe I will use a small amount of the data...

wedd
02-21-2012, 07:03 AM
Hi xld, have an idea of a vLookup I could do to handle this problem?

Bob Phillips
02-21-2012, 07:20 AM
Mine is very slow as well.

I added an array formula (H2:Hn) to get the matching row number

=MATCH(1,('Assignment Phase'!$A$1:$A$7500=$A2)*('Assignment Phase'!$C$1:$C$7500=$D2)*('Assignment Phase'!$D$1:$D$7500=$E2),0)

and then lookup formula to get the values

=INDEX('Assignment Phase'!$E$1:$F$7500,$H2,COLUMN(A1))

wedd
02-21-2012, 07:56 AM
Thanks xld, it was displaying circular references...very similar to mine. Oh well, thanks for assisting me...2 heads are always better than 1.

Bob Phillips
02-21-2012, 08:19 AM
What were. my formulae?

D_Marcel
02-21-2012, 01:07 PM
Interesting this Array Formula, I did not know it. Out of curiosity, I tried the good old VLOOKUP to Assignment Type and Job Grade and worked very fast, the message "Calculating" wasn't even displayed. To Duplicate ID? what do you have in mind? I did a pivot table to count the occurrences, there is 1327 Employee ID with more than 1 occurrence. 50699, for example is displayed 5 times.

wedd
02-21-2012, 11:33 PM
Hi, D_Marcel! What was your VLOOKUP that worked very fast to display the Assignment Type and Job Grade? T o display the Duplicate IDs I wrote an IF formula that displayed in a column with all the occurrences of duplicate number, a "yes" would be displayed. Thanks for the ideas...

D_Marcel
02-23-2012, 03:56 AM
Hi wedd, I was wrong, I used a simple VLOOKUP:

=VLOOUKP(A2;'Assignment Phase'!$A$2:$E$7301;5;0)

But there's a problem, we also have duplicate values in 'Assignment Phase' Sheet, so the VLOOKUP only returns the first value found.

The occurrences to ID 291436, for example, is:

LTA - 1
Permanent Transfer - 1
STA - 2

How you did in this case?

wedd
02-23-2012, 04:01 AM
Hi D_Marcel! There are duplicates, I used an IF query to highlight the duplicates. But your idea seems correct and is very simple to use. Thanks!

Bob Phillips
02-23-2012, 06:28 AM
Hi wedd, I was wrong, I used a simple VLOOKUP:

=VLOOUKP(A2;'Assignment Phase'!$A$2:$E$7301;5;0)

But there's a problem, we also have duplicate values in 'Assignment Phase' Sheet, so the VLOOKUP only returns the first value found.

The occurrences to ID 291436, for example, is:

LTA - 1
Permanent Transfer - 1
STA - 2

How you did in this case?

How does that address the matter of looking up on multiple keys, as described in the instructions?

wedd
02-24-2012, 04:15 AM
Hi D_Marcel! I tested your formula(array) but it brought up errors. Have you a reason why? Did it work for you? There are suppose to be duplicates...one of the questions was to identify the duplicated data. Thanks!