Tables join

[alexpitt]

Hi everybody, I'm new in this very helpful forum and I'm just a kid in using excel macro.

I got 2 tables like these:
TABLE A
JOB DESCJOB PACKAGE DESCPACKAGE
Job1 DescJob1 Package1 DescPackage1
Job1 DescJob1 Package2 DescPackage2
Job1 DescJob1 Package3 DescPackage3
Job1 DescJob1 Package4 DescPackage4
Job2 DescJob2 Package2 DescPackage2
Job2 DescJob2 Package4 DescPackage4
Job2 DescJob2 Package5 DescPackage5

TABLE B
PACKAGE DESCPACKAGE REQ DESCREQ
Package1 DescPackage1 Req1 DescReq1
Package1 DescPackage1 Req2 DescReq2
Package1 DescPackage1 Req3 DescReq3
Package1 DescPackage1 Req4 DescReq4
Package2 DescPackage2 Req1 DescReq1
Package2 DescPackage2 Req3 DescReq3
Package2 DescPackage2 Req5 DescReq5
Package2 DescPackage2 Req7 DescReq7
Package3 DescPackage3 Req1 DescReq1
Package3 DescPackage3 Req2 DescReq2
Package3 DescPackage3 Req4 DescReq4
Package4 DescPackage4 Req1 DescReq1
Package4 DescPackage4 Req6 DescReq6
Package5 DescPackage5 Req1 DescReq1

I need to join A and B in order to have a third table C like that
TABLE C
JOB DESCJOB PACKAGE DESCPACKAGE REQ DESCREQ
Job1 DescJob1 Package1 DescPackage1 Req1 DescReq1
Job1 DescJob1 Package1 DescPackage1 Req2 DescReq2
Job1 DescJob1 Package1 DescPackage1 Req3 DescReq3
Job1 DescJob1 Package1 DescPackage1 Req4 DescReq4
Job1 DescJob1 Package2 DescPackage2 Req1 DescReq1
Job1 DescJob1 Package2 DescPackage2 Req3 DescReq3
Job1 DescJob1 Package2 DescPackage2 Req5 DescReq5
Job1 DescJob1 Package2 DescPackage2 Req7 DescReq7
Job1 DescJob1 Package3 DescPackage3 Req1 DescReq1
Job1 DescJob1 Package3 DescPackage3 Req2 DescReq2
Job1 DescJob1 Package3 DescPackage3 Req4 DescReq4
Job1 DescJob1 Package4 DescPackage4 Req1 DescReq1
Job1 DescJob1 Package4 DescPackage4 Req6 DescReq6
Job2 DescJob2 Package2 DescPackage2 Req1 DescReq1
Job2 DescJob2 Package2 DescPackage2 Req3 DescReq3
Job2 DescJob2 Package2 DescPackage2 Req5 DescReq5
Job2 DescJob2 Package2 DescPackage2 Req7 DescReq7
Job2 DescJob2 Package4 DescPackage4 Req1 DescReq1
Job2 DescJob2 Package4 DescPackage4 Req6 DescReq6
Job2 DescJob2 Package5 DescPackage5 Req1 DescReq1

Is it possible with a VBA Macro?

Thanks in advance for your help!

alexpitt

[6StringJazzer]

Can you specify the relationships among the fields? This is really a relational database problem and not hard to solve if we understand exactly how the fields are related.

In table A, which fields uniquely combine to produce the value of the other fields? For example, do you use JOB + DESCJOB + PACKAGE to determine what DESCPACKAGE should be?

Same question for table B--do you use PACKAGE + DESCPACKAGE + REQ to determine DESCREQ?

[alexpitt]

Many thanks 6stringjazzer for your kind reply.
Answering your question...
In table A every JOB is related to one or more PACKAGE.
In table B every PACKAGE is related to one or more REQ.
The field that relates the table A with table B is PACKAGE.
DESCXXX are just attributes not key fields
In table A and B I don't have to combine or determine any field. Table A and B are given, they already exist
Many thanks again

Can you specify the relationships among the fields? This is really a relational database problem and not hard to solve if we understand exactly how the fields are related.

In table A, which fields uniquely combine to produce the value of the other fields? For example, do you use JOB + DESCJOB + PACKAGE to determine what DESCPACKAGE should be?

Same question for table B--do you use PACKAGE + DESCPACKAGE + REQ to determine DESCREQ?

[6StringJazzer]

OK, now I just need to know where these tables are located. Can you attach your file? Then I can install VBA code right in your file.

To attach a file:

1. Under the text box where you type your reply click the Go Advanced button.
2. In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
3. In the pop-up, click Add then Select to select a file from your local drive
4. Click the Upload button to upload the file
5. Click Done to attach it.

It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.

Here are step-by-step instructions with screen shots

[alexpitt]

Many thanks again
I attached my file. Table A, B and C are different sheets in the same file.
At the moment table A and B are populated with test values. They will be bigger than attached ones
You are very helpful!

[jindon]

See if tis is how you wanted.

Please Login or Register  to view this content.

[alexpitt]

Great! You're a genius, jindon! It worked perfectly at first run!
Then I take this opportunity...
If I introduce new attributes (I mean like DESCXXX) how does the code change?
I attached a new sample file
Just to understand how to modify the code if I need to introduce more attributes without changing key fields and join structure...

[kvsrinivasamurthy]

It can be done by Formulas also. Are you interested.

[alexpitt]

Of course!

[jindon]

Not really sure if this is how you wanted,

Please Login or Register  to view this content.

[alexpitt]

It works perfectly. Now I have to discover the difference between the 2 codes!