joining 2 excel spreadsheets

ffawkesffawkes Posts: 4,489
Forum Member
✭✭✭
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

Comments

  • user123456789user123456789 Posts: 16,589
    Forum Member
    ✭✭
    Cut'n'paste from one to the other :confused:
  • plateletplatelet Posts: 26,358
    Forum Member
    ✭✭✭
    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.
  • ffawkesffawkes Posts: 4,489
    Forum Member
    ✭✭✭
    anniebrion wrote: »
    Cut'n'paste from one to the other :confused:

    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.
  • ffawkesffawkes Posts: 4,489
    Forum Member
    ✭✭✭
    mac2708 wrote: »


    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
  • ffawkesffawkes Posts: 4,489
    Forum Member
    ✭✭✭
    platelet wrote: »
    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?
  • tealadytealady Posts: 26,262
    Forum Member
    ✭✭✭
    ffawkes wrote: »
    - 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.
  • plateletplatelet Posts: 26,358
    Forum Member
    ✭✭✭
    ffawkes wrote: »
    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$[highlight]30[/highlight] so it covers the entire salary table
Sign In or Register to comment.