Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
merge data
I have a worksheet with two columns: job titles and job codes.
I have 15 other worksheets that have several columns including job code, and I need to add the job title in a column next to the job code. Example of 1st workbook: Job Title Job Code President A4000 Secretary B1200 Technical Asst D3211 etc. Other 15 workbooks are set up like: Job Code Score Average Total A4000 421 321 732 D3211 320 170 642 I just need to add a column that shows Job Title next to Job Code in the 15 workbooks, pulling that data from first workbook. Help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
merge data
use index and match (needs a backwards vlookup).
assume your first sheet is called sheet1 in one of your 15 worksheets, add a new column after col A Col A new Col B row 1 A4000 =INDEX(sheet1!A:A,MATCH(A1, sheet1!B:B,0),1) row 2 B1200 drag the formula above downwards if this works, then you are all set. You can do the rest of the 14 sheets in one go, providing they are all set up quite similarly, you can select them all first before adding the column, and you will find that it does all of them at once. Save your file before you start just in case you make a mess. -- Allllen "Betsy" wrote: I have a worksheet with two columns: job titles and job codes. I have 15 other worksheets that have several columns including job code, and I need to add the job title in a column next to the job code. Example of 1st workbook: Job Title Job Code President A4000 Secretary B1200 Technical Asst D3211 etc. Other 15 workbooks are set up like: Job Code Score Average Total A4000 421 321 732 D3211 320 170 642 I just need to add a column that shows Job Title next to Job Code in the 15 workbooks, pulling that data from first workbook. Help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
merge data
Betsy,
You are using the terms worksheets and workbooks interchangably: worksheets are sheets within a workbook (a single Excle file), while workbooks are separate, individually named Excel files. If you have 15 WORKSHEETS, insert a new column B in each sheet, and use =VLOOKUP(A2,'SheetName'!A:B,2,False) in cell B2, copied down. If you have 15 WORKBOOKS, open all the files, insert a column in each sheet of each workbook, and use =VLOOKUP(A2,'[WorksBookName.xls]SheetName'!$A:$B,2,FALSE) in cell B2, copied down. HTH, Bernie MS Excel MVP "Betsy" wrote in message ... I have a worksheet with two columns: job titles and job codes. I have 15 other worksheets that have several columns including job code, and I need to add the job title in a column next to the job code. Example of 1st workbook: Job Title Job Code President A4000 Secretary B1200 Technical Asst D3211 etc. Other 15 workbooks are set up like: Job Code Score Average Total A4000 421 321 732 D3211 320 170 642 I just need to add a column that shows Job Title next to Job Code in the 15 workbooks, pulling that data from first workbook. Help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merge data in two worksheets based on key data value (like databas | Excel Worksheet Functions | |||
data merge | Excel Discussion (Misc queries) | |||
Can I merge data in 2 sheets matching rows of data by last name? | Excel Discussion (Misc queries) | |||
mail merge excludes my headers and critical data in Word merge | Excel Discussion (Misc queries) | |||
how do i get my mail merge to update the data source at each merge | Excel Discussion (Misc queries) |