Importing : Combining 2 excel workbooks

  • I need to merge 2 excel workbooks. Each workbook contains 1 worksheet as the result of an export from another program. The first column contains the same numbers, just not in the same order. I need to pull the correlating 7 columns 1 worksheet.
    This is the first sheet "UseRate"

    Series 03-04 02-03
    4-H 0230 14094 11025
    GROW 6045 7488
    EM 8289 4192 13093

    This is the second sheet "InvRpt". This sheet has 4 additional rows of information that will also need to be merged.

    Series Cost Min Qty Price
    4-H 0101 1 25 7
    4-H 0230 0.15 1000 0

    Thanks for the help.

  • Hi Byron,

    Welcome to OzGrid!!!

    Please help us to help you.

    The question is a bit unclear.
    Can you let us know what do you mean by "pull correlating 7 columns"?

    Also what should happen if we have 4-H more than once... how should we pull / combine the data.

    At times it happens in first few posts, that you may be a bit unclear... but do not refrain from posting... we are as eager to help you as you are to get the help :)

    Thanks: ~Yogendra

  • Sorry for the confusion. Let me try and enter this so its a little more clear. The first column in both worksheets is "Series" This contains the ID number of the publication. There will be mulitple 4-H codes but each will have its own 4-5 number extension. There are numbers on each sheet that will not match up with a number on the other sheet, but most will. The UseRate is the base report. I need to get the corresponding information from the InvRpt onto the UseRate sheet. Or the two of them together onto a new sheet.

    The first sheet is "USeRate" and looks as follows
    "Series" "03-04" "02-03" "01-02"
    4-H 0230 14094 11025 11917
    GROW 6045 7488 8109
    4-H0259L 2848 2711 0

    The second Sheet is "InvRpt" and looks as follows. After Price there are 3 more columns, Qty on Hand, Author, and Title. But to get it to look right, I left them off.
    "Series" "Cost" "Min Qty" "No. Pages "Price"
    4-H 0101 1 25 100 7
    4-H 0230 0.15 1000 40 0
    GROW 0.09 1500 12 0

    The final sheet would then have the following columns
    "Series" "03-04" "02-03" "02-01" "Cost" "Min. Qty" "No of Pages" "Price" "Qty on Hand" "Author" and "Title"

    I hope that helps clear things up

  • The first sheet is "USeRate" and looks as follows
    "Series"__ "03-04"__ "02-03"__ "01-02"
    4-H 0230__ 14094___ 11025___ 11917
    GROW ____6045_____ 7488____ 8109
    4-H0259L __2848____ 2711_____ 0

    The second Sheet is "InvRpt"

    "Series"____ "Cost"__ "Min Qty"__ "No. Pages"__ "Price"
    4-H 0101 _____1_____ 25________ 100_________ 7
    4-H 0230 _____0.15__ 1000_______ 40__________ 0
    GROW_______ 0.09__ 1500_______ 12__________ 0

    Hopefully that will make the columns and number be in the correct locatiosn. Ignore the underscores.

  • use VLOOKUP function to return the values from "InvRpt" to columns to the right of your data in "USeRate"

    Create a table of the whole range occupied by the "InvRpt" data

    in col 4 of "USeRAte" enter

    =VLOOKUP(A2,LookupTable,2,FALSE) to retirn "Cost" from "InvRpt" for the Series ID in Col A of "UseRAte"

    Hope this helps,


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!