Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how 2 merge 2 tables (worksheets)
if i have a table/sheet like this:
id# first last address 1 bob a ny 2 ron s la 3 dan r ca and a different one: id# email phone 1 212 3 718 is it posible to merege so where ever Id# sheet1 = Id# sheet 2 it will auto add in that row the cells with the data? example of final result i need: id# first last address email phone 1 bob a ny 212 2 ron s la 3 dan r ca 718 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how 2 merge 2 tables (worksheets)
=INDEX(TABLE YOU ARE REFERENCING,MATCH(THE ID # FROM CURRENT TABLE,THE COLUMN
IN REFERENCED TABLE THAT HOLDS THE ID #),MATCH(THE COLUMN HEADER YOU WANT,THE ROW IN REFERENCED TABLE THAT HAS YOUR COLUMN HEADERS)) So, if the first table you listed is on Sheet 1, in A1:C3 and your result table is on Sheet2, say, A1:E4, where e-mail address is column E: =INDEX(Sheet1!$A$1:$C$3,MATCH($A2,Sheet1!$A$1:$A$3 ),MATCH(E$1,Sheet1!$A$1:$C$1)) returns the e-mail address. Absolute values have been set to allow you to copy this formula all the way down and to the right. "adam" wrote: if i have a table/sheet like this: id# first last address 1 bob a ny 2 ron s la 3 dan r ca and a different one: id# email phone 1 212 3 718 is it posible to merege so where ever Id# sheet1 = Id# sheet 2 it will auto add in that row the cells with the data? example of final result i need: id# first last address email phone 1 bob a ny 212 2 ron s la 3 dan r ca 718 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how 2 merge 2 tables (worksheets)
i didnt get it
if my first table is a1:d4 with tha same example i put before and my second table on sheet 2 is a1:c3 what do i do? (take notice that in sheet 2 one of the id# is missing. (the number of rows on the sheets are diff) "Sean Timmons" wrote: =INDEX(TABLE YOU ARE REFERENCING,MATCH(THE ID # FROM CURRENT TABLE,THE COLUMN IN REFERENCED TABLE THAT HOLDS THE ID #),MATCH(THE COLUMN HEADER YOU WANT,THE ROW IN REFERENCED TABLE THAT HAS YOUR COLUMN HEADERS)) So, if the first table you listed is on Sheet 1, in A1:C3 and your result table is on Sheet2, say, A1:E4, where e-mail address is column E: =INDEX(Sheet1!$A$1:$C$3,MATCH($A2,Sheet1!$A$1:$A$3 ),MATCH(E$1,Sheet1!$A$1:$C$1)) returns the e-mail address. Absolute values have been set to allow you to copy this formula all the way down and to the right. "adam" wrote: if i have a table/sheet like this: id# first last address 1 bob a ny 2 ron s la 3 dan r ca and a different one: id# email phone 1 212 3 718 is it posible to merege so where ever Id# sheet1 = Id# sheet 2 it will auto add in that row the cells with the data? example of final result i need: id# first last address email phone 1 bob a ny 212 2 ron s la 3 dan r ca 718 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how 2 merge 2 tables (worksheets)
Since you want your resulting table to have the additional rows that are in
table 1, you would want to put the formula into the first table, not the second. You would add the e-mail and phone column headers to the first table and put the formula there. Change the SHEET1 sheet name reference to whatever the name of your second tab is and ensure you are capturing your entire table with the cell references. "adam" wrote: i didnt get it if my first table is a1:d4 with tha same example i put before and my second table on sheet 2 is a1:c3 what do i do? (take notice that in sheet 2 one of the id# is missing. (the number of rows on the sheets are diff) "Sean Timmons" wrote: =INDEX(TABLE YOU ARE REFERENCING,MATCH(THE ID # FROM CURRENT TABLE,THE COLUMN IN REFERENCED TABLE THAT HOLDS THE ID #),MATCH(THE COLUMN HEADER YOU WANT,THE ROW IN REFERENCED TABLE THAT HAS YOUR COLUMN HEADERS)) So, if the first table you listed is on Sheet 1, in A1:C3 and your result table is on Sheet2, say, A1:E4, where e-mail address is column E: =INDEX(Sheet1!$A$1:$C$3,MATCH($A2,Sheet1!$A$1:$A$3 ),MATCH(E$1,Sheet1!$A$1:$C$1)) returns the e-mail address. Absolute values have been set to allow you to copy this formula all the way down and to the right. "adam" wrote: if i have a table/sheet like this: id# first last address 1 bob a ny 2 ron s la 3 dan r ca and a different one: id# email phone 1 212 3 718 is it posible to merege so where ever Id# sheet1 = Id# sheet 2 it will auto add in that row the cells with the data? example of final result i need: id# first last address email phone 1 bob a ny 212 2 ron s la 3 dan r ca 718 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how 2 merge 2 tables (worksheets)
Used Pivot Table, VBA and
Excel 2003 Lists. No formulas used. Data can be located anywhere, can have odd rows and columns, be in random order and incomplete. Dynamic data ranges provided for easy editing. http://www.freefilehosting.net/download/3d94b |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how 2 merge 2 tables (worksheets)
sean can u give me the exact formula to put in sheet1 cell E2
acording the example i gave?(so it will brind the data on sheet 2 to sheet1) i added the 2 headers in sheet 1 "Sean Timmons" wrote: Since you want your resulting table to have the additional rows that are in table 1, you would want to put the formula into the first table, not the second. You would add the e-mail and phone column headers to the first table and put the formula there. Change the SHEET1 sheet name reference to whatever the name of your second tab is and ensure you are capturing your entire table with the cell references. "adam" wrote: i didnt get it if my first table is a1:d4 with tha same example i put before and my second table on sheet 2 is a1:c3 what do i do? (take notice that in sheet 2 one of the id# is missing. (the number of rows on the sheets are diff) "Sean Timmons" wrote: =INDEX(TABLE YOU ARE REFERENCING,MATCH(THE ID # FROM CURRENT TABLE,THE COLUMN IN REFERENCED TABLE THAT HOLDS THE ID #),MATCH(THE COLUMN HEADER YOU WANT,THE ROW IN REFERENCED TABLE THAT HAS YOUR COLUMN HEADERS)) So, if the first table you listed is on Sheet 1, in A1:C3 and your result table is on Sheet2, say, A1:E4, where e-mail address is column E: =INDEX(Sheet1!$A$1:$C$3,MATCH($A2,Sheet1!$A$1:$A$3 ),MATCH(E$1,Sheet1!$A$1:$C$1)) returns the e-mail address. Absolute values have been set to allow you to copy this formula all the way down and to the right. "adam" wrote: if i have a table/sheet like this: id# first last address 1 bob a ny 2 ron s la 3 dan r ca and a different one: id# email phone 1 212 3 718 is it posible to merege so where ever Id# sheet1 = Id# sheet 2 it will auto add in that row the cells with the data? example of final result i need: id# first last address email phone 1 bob a ny 212 2 ron s la 3 dan r ca 718 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how 2 merge 2 tables (worksheets)
Sean,
When i put the formula =INDEX(Sheet2!B:B,MATCH(A3,Sheet2!A:A)) in sheet 1 cell E2 it worked good. The only problem is that sheet 2 wont/dosent have all id# like sheet 1 has and when i put the formula, even though there there was no id#2 on sheet 2 it copied the value from the above cell instead of leaving cell blank "Herbert Seidenberg" wrote: Used Pivot Table, VBA and Excel 2003 Lists. No formulas used. Data can be located anywhere, can have odd rows and columns, be in random order and incomplete. Dynamic data ranges provided for easy editing. http://www.freefilehosting.net/download/3d94b |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how 2 merge 2 tables (worksheets)
Sean,
When i put the formula =INDEX(Sheet2!B:B,MATCH(A3,Sheet2!A:A)) in sheet 1 cell E2 it worked good. The only problem is that sheet 2 wont/dosent have all id# like sheet 1 has and when i put the formula, even though there there was no id#2 on sheet 2 it copied the value from the above cell instead of leaving cell blank "Sean Timmons" wrote: Since you want your resulting table to have the additional rows that are in table 1, you would want to put the formula into the first table, not the second. You would add the e-mail and phone column headers to the first table and put the formula there. Change the SHEET1 sheet name reference to whatever the name of your second tab is and ensure you are capturing your entire table with the cell references. "adam" wrote: i didnt get it if my first table is a1:d4 with tha same example i put before and my second table on sheet 2 is a1:c3 what do i do? (take notice that in sheet 2 one of the id# is missing. (the number of rows on the sheets are diff) "Sean Timmons" wrote: =INDEX(TABLE YOU ARE REFERENCING,MATCH(THE ID # FROM CURRENT TABLE,THE COLUMN IN REFERENCED TABLE THAT HOLDS THE ID #),MATCH(THE COLUMN HEADER YOU WANT,THE ROW IN REFERENCED TABLE THAT HAS YOUR COLUMN HEADERS)) So, if the first table you listed is on Sheet 1, in A1:C3 and your result table is on Sheet2, say, A1:E4, where e-mail address is column E: =INDEX(Sheet1!$A$1:$C$3,MATCH($A2,Sheet1!$A$1:$A$3 ),MATCH(E$1,Sheet1!$A$1:$C$1)) returns the e-mail address. Absolute values have been set to allow you to copy this formula all the way down and to the right. "adam" wrote: if i have a table/sheet like this: id# first last address 1 bob a ny 2 ron s la 3 dan r ca and a different one: id# email phone 1 212 3 718 is it posible to merege so where ever Id# sheet1 = Id# sheet 2 it will auto add in that row the cells with the data? example of final result i need: id# first last address email phone 1 bob a ny 212 2 ron s la 3 dan r ca 718 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel VBA Merge Workbook tables Central table | Excel Discussion (Misc queries) | |||
Help :Merge 2 employee information tables | Excel Discussion (Misc queries) | |||
joing (merge) tables by key field | Excel Worksheet Functions | |||
HOW TO MERGE TWO TABLES | Excel Worksheet Functions | |||
Merge Pivot Tables | Excel Discussion (Misc queries) |