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.

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.

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.

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.

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.