Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
combining different size tables
I have two tables of different length - table 1 has 44K rows by 8 columns,
table 2 has 180 rows by 8 columns. The first column in each table is a unique ID such that all 180 IDs are part of the first table. I want to run a process that looks at each row in table 2, compares the ID to the rows in table 1, and if a match is found, it appends the row from table 2 to the corresponding row in table 1. I would end up with one consolidate table where 180 rows would have all 16 columns. Is there a simple way to do this? Thanks for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
combining different size tables
Hey ronm
just that I understand it correctly; The table you have in the end starts with the columns of table 2 and then with the columns of table 1. Do you need to do that once, or is it a recurring process? Carlo "ronm" wrote: I have two tables of different length - table 1 has 44K rows by 8 columns, table 2 has 180 rows by 8 columns. The first column in each table is a unique ID such that all 180 IDs are part of the first table. I want to run a process that looks at each row in table 2, compares the ID to the rows in table 1, and if a match is found, it appends the row from table 2 to the corresponding row in table 1. I would end up with one consolidate table where 180 rows would have all 16 columns. Is there a simple way to do this? Thanks for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
combining different size tables
Hi,
Firstly back up you workbook because you have an enormous amount of data to process and there is a possibility of a crash. Assuming:- Your table 1 with 44,000 rows is in Sheet1. Your table 2 with 180 rows is in Sheet2. Your data is in first 8 columns (A to H) You have column headers in the first row. Copy the following formula and paste it into cell I2 of Sheet1:- =VLOOKUP($A2,Sheet2!$A$2:$H$27,COLUMN()-8,FALSE) (If you dont have column headers then still paste the formula into cell I2 and then copy it up to the previous row.) Drag the formula across to column P (Or copy and paste across). You could now just copy the range I2:P2 and paste down to the bottom of your data. However, due to the amount of data you have, I suggest that you do it one column at a time to prevent a crash if your resources cannot handle the massive amount of data. When you have copied and pasted one column, select the entire column and Copy, Paste Special, Values over top of itself and then repeat the process for the next column. When all columns finished (including Copy, Paste Special, Values), to remove all the #N/As where there were no matches:- Select all the columns from I to P. Select Edit-Replace and insert #N/A in Find what and leave Replace blank then click Replace all. Regards, OssieMac "ronm" wrote: I have two tables of different length - table 1 has 44K rows by 8 columns, table 2 has 180 rows by 8 columns. The first column in each table is a unique ID such that all 180 IDs are part of the first table. I want to run a process that looks at each row in table 2, compares the ID to the rows in table 1, and if a match is found, it appends the row from table 2 to the corresponding row in table 1. I would end up with one consolidate table where 180 rows would have all 16 columns. Is there a simple way to do this? Thanks for any help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
combining different size tables
Hi Carlo,
Actually, just the opposite - I want the final table to be all 44k rows from the first table but where there is a match of IDs, I would have the 2nd table row appended to the first. table 1 xxxxxxxx table 2 yyyyyyyy final xxxxxxxx xxxxxxxxyyyyyyyy xxxxxxxx I need to do this once a month and would like to automate it. ronm "Carlo" wrote: Hey ronm just that I understand it correctly; The table you have in the end starts with the columns of table 2 and then with the columns of table 1. Do you need to do that once, or is it a recurring process? Carlo "ronm" wrote: I have two tables of different length - table 1 has 44K rows by 8 columns, table 2 has 180 rows by 8 columns. The first column in each table is a unique ID such that all 180 IDs are part of the first table. I want to run a process that looks at each row in table 2, compares the ID to the rows in table 1, and if a match is found, it appends the row from table 2 to the corresponding row in table 1. I would end up with one consolidate table where 180 rows would have all 16 columns. Is there a simple way to do this? Thanks for any help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
combining different size tables
Hi ronm,
Your answer to Carlo is the format that I interpretted that you wanted and my previous reply should do that. However, if you want to automate it, I can do that too. However, would you like to test what I have posted and if it returns what you want then reply and I'll automate the process with a macro for you. I won't use vlookup if doing it in a macro but knowing that the format is correct is a good start. Also, do you need instructions to install a macro? Regards, OssieMac "ronm" wrote: Hi Carlo, Actually, just the opposite - I want the final table to be all 44k rows from the first table but where there is a match of IDs, I would have the 2nd table row appended to the first. table 1 xxxxxxxx table 2 yyyyyyyy final xxxxxxxx xxxxxxxxyyyyyyyy xxxxxxxx I need to do this once a month and would like to automate it. ronm "Carlo" wrote: Hey ronm just that I understand it correctly; The table you have in the end starts with the columns of table 2 and then with the columns of table 1. Do you need to do that once, or is it a recurring process? Carlo "ronm" wrote: I have two tables of different length - table 1 has 44K rows by 8 columns, table 2 has 180 rows by 8 columns. The first column in each table is a unique ID such that all 180 IDs are part of the first table. I want to run a process that looks at each row in table 2, compares the ID to the rows in table 1, and if a match is found, it appends the row from table 2 to the corresponding row in table 1. I would end up with one consolidate table where 180 rows would have all 16 columns. Is there a simple way to do this? Thanks for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Two tables into One | Excel Worksheet Functions | |||
Combining Tables | Excel Discussion (Misc queries) | |||
Combining 2 tables | Excel Discussion (Misc queries) | |||
Combining 2 pivot tables | Excel Discussion (Misc queries) | |||
Combining Tables | Excel Discussion (Misc queries) |