Digital Spy

Search Digital Spy
 

DS Forums

 
 
 

joining 2 excel spreadsheets


Reply
Thread Tools Search this Thread
Old 01-02-2013, 11:48
ffawkes
Forum Member
 
Join Date: Oct 2009
Posts: 2,752

Does anyone know how to do the following?

I have 2 spreadsheets.

First one has the following 3 columns and 30 or so rows

user id/name/department

2nd one has the following 2 columns and 30 or so rows

user id/salary

I want to merge the two into one, giving

user id/name/department/salary
ffawkes is offline   Reply With Quote
Please sign in or register to remove this advertisement.
Old 01-02-2013, 11:51
anniebrion
Inactive Member
 
Join Date: Sep 2005
Posts: 16,389
Cut'n'paste from one to the other
anniebrion is offline   Reply With Quote
Old 01-02-2013, 11:55
mac2708
Forum Member
 
Join Date: May 2009
Posts: 2,802
This any help?
http://office.microsoft.com/en-gb/ex...010095249.aspx
mac2708 is offline   Reply With Quote
Old 01-02-2013, 12:16
platelet
Forum Member
 
Join Date: May 2002
Location: GL51 0EX
Posts: 6,745
look up the user ID from one onto the other using VLOOKUP. for example on your user id/name/department sheet add a salary column and populate it with

=VLOOKUP(A:A,[Book2]Sheet1!$A$1:$B$4,2,FALSE)

where
book2 is your second s/sheet.
sheet1 is the sheet with the salary data
$A$1:$B$4 is the area containing the user id/salary data.
2 is the column to return i.e. the salary
FALSE means it has to be an exact match on userid

then just fill that formula down.
platelet is offline   Reply With Quote
Old 01-02-2013, 12:48
ffawkes
Forum Member
 
Join Date: Oct 2009
Posts: 2,752
Cut'n'paste from one to the other
the two lists don't contain exactly the same user ids, I am only interested in the ones in the first spreadsheet, so it's not quite that easy, but thanks for the suggestion.
ffawkes is offline   Reply With Quote
Old 01-02-2013, 12:50
ffawkes
Forum Member
 
Join Date: Oct 2009
Posts: 2,752

It might be, but have just spotted the next reply which looks like it might do the trick - thanks for the link though and I will still have a look
ffawkes is offline   Reply With Quote
Old 01-02-2013, 12:50
ffawkes
Forum Member
 
Join Date: Oct 2009
Posts: 2,752
look up the user ID from one onto the other using VLOOKUP. for example on your user id/name/department sheet add a salary column and populate it with

=VLOOKUP(A:A,[Book2]Sheet1!$A$1:$B$4,2,FALSE)

where
book2 is your second s/sheet.
sheet1 is the sheet with the salary data
$A$1:$B$4 is the area containing the user id/salary data.
2 is the column to return i.e. the salary
FALSE means it has to be an exact match on userid

then just fill that formula down.
THis looks like it might be what I'm looking for . I will give it a try and see if it works. many thanks

- but just a thought - wit hthe FALSE bit, how does it know to look on user id for a match and not some other column?
ffawkes is offline   Reply With Quote
Old 01-02-2013, 13:50
tealady
Forum Member
 
Join Date: Apr 2005
Location: colchester
Posts: 10,279
- but just a thought - wit hthe FALSE bit, how does it know to look on user id for a match and not some other column?
You tell it what column to look for the match in. FALSE means it looks for an exact match, otherwise, it will use the nearest value eg look for 10, it will match 9.9 if it doesn't find 10.
tealady is offline   Reply With Quote
Old 01-02-2013, 19:27
platelet
Forum Member
 
Join Date: May 2002
Location: GL51 0EX
Posts: 6,745
THis looks like it might be what I'm looking for . I will give it a try and see if it works. many thanks

- but just a thought - wit hthe FALSE bit, how does it know to look on user id for a match and not some other column?
Two parts to this.

The A:A tells it that it's column A you want to look up (so user ID in the sheet you have the formula in)

With the other sheet, the first column in the area you give ($A$1:$B$4 in my example) is the one that will be searched (so user ID in the sheet with the salaries)

oh, and if you have 30 rows you will of course need to change it to read $A$1:$B$30 so it covers the entire salary table
platelet is offline   Reply With Quote
 
Reply



Thread Tools Search this Thread
Search this Thread:

Advanced Search

 
Forum Jump


All times are GMT +1. The time now is 12:20.