Combining Two tables into One

[SunMatrix]

I need to combine two tables into one depending on which value is in a
certain cell. I'll try to explain what I need by using an example:

Table 1:
Role1 Role2 Role3
Rate1 x
Rate2 x x
Rate3 x

Table 2:
Job1 Job2 Job3
Role1 x
Role2 x x
Role3 x x

New Table I would like Excel to Create is a Job v Rate table. Obviously
there is some overlap (Job1 has all three roles where two of the roles has
the same rate. Excel needs to just report this once).

So table 3 should look like:

Job1 Job2 Job3
Rate1 x
Rate2 x x
Rate3 x x

The tables are quite a bit bigger and the first two tables are likely to
change so I don't want to do this manually. I'm only a basic user of excel
so I'm hoping someone here can help.

Tina

 

[Edmonton]

I need to combine two tables into one depending on which value is in a
certain cell. I'll try to explain what I need by using an example:

Table 1:
Role1 Role2 Role3
Rate1 x
Rate2 x x
Rate3 x

Table 2:
Job1 Job2 Job3
Role1 x
Role2 x x
Role3 x x

New Table I would like Excel to Create is a Job v Rate table. Obviously
there is some overlap (Job1 has all three roles where two of the roles has
the same rate. Excel needs to just report this once).

So table 3 should look like:

Job1 Job2 Job3
Rate1 x
Rate2 x x
Rate3 x x

The tables are quite a bit bigger and the first two tables are likely to
change so I don't want to do this manually. I'm only a basic user of excel
so I'm hoping someone here can help.

Tina

Tina
You need to use array formulay. I am giving you link that has a very
good description with example. Try that:

http://www.exceluser.com/explore/arrays1.htm