Combine tables

[22strider]

Hello Friends

Could you please check if you can help me with the following:

I have two tables Table1 "Part Number" and Table2 "Manufacturer Part Number".

Table1 has following columns:
PartNumber, Manufacturer Name, ManufacturerP/N

Table2 has following columns:
ManufacturerP/N, Manufacturer Name, Description Lifecycle

As you may notice 'ManufacturerP/N' is common field between the two tables. I need to combine them into one table based on the ManufacturerP/N so that the result table appears like the following:

PartNumber,Manufacturer Name, ManufacturerP/N, Description, Lifecycle

Each part number can have more than one ManufacturerP/N associated with it and a manufacturerP/N can be associated to more than one PartNumbers.

Due to some IT restriction I am not able to download html maker at work. So I am trying to describe table structures as clearly as possible.

Thanks
Rajesh

 

[ReaperX]

You mention that each part number can have more than one ManufacturerP/N associated with it. If the PartNumbers that have multiple ManufacturerP/Ns are listed on multiple rows, as opposed to having multiple ManufacturerP/Ns in the column, then you could add two columns to the first table and use VLOOKUP.

=VLOOKUP(Reference to ManufacturerP/N, Table2, 2, 0) <-- Would return column 2 where it could match ManufacturerP/N, i.e. Manufacturer Name
=VLOOKUP(Reference to ManufacturerP/N, Table2, 3, 0) <-- Description Lifecycle

 

[22strider]

Hello Reaperx

Thanks for your reply. In this case I am looking for VBA code because I am preparing a template and further data analysis will follow this merging the tables. I have prepared code for that analysis already.

The problem with VLOOKUP in this case is that it always returns the first match. So if I am looking up a manufacturer part number and trying to return P/N I will keep getting the P/N that is appearing first in the list.

Thanks
Rajesh

 

[ReaperX]

With VBA it would be pretty easy, just add all of the items to a multidimensional array. Otherwise you could do it the long method and have a for loop run each time you do a search.

 

[22strider]

Would you mind giving the code?

Thanks
Rajesh

 

[22strider]

bump

 

[22strider]

Hello Friends

Any idea how to go about this one?

Thanks
Rajesh