Cross Reference Table Data Searching

[vdragn]

I have two tables, set up as cross reference tables. I would like to extract
data from both to create a third table. It looks like this:

a b c d e
1 name doc-1 doc-b doc-c doc-d
2 joe x
3 ellen x x
4 mike x
5 quinn x x


a b c d e
1 name job-a job-b job-c job-d
2 joe x x
3 ellen x
4 mike x x x
5 quinn x x

a b c d
1 doc first_job second_job third_job
2 doc-a
3 doc-b
4 doc-c
5 doc-d

 

[T. Valko]

Hmmm...

It may be obvious to you what the results should be but it certainly isn't
obvious to me!

 

[vdragn]

Sorry, I left the question very incomplete. Looking at this again I realize
I'd be asking for mind reading along with the Excel solution! Hopefully this
clarifies my question a little.


<First table>
a b c d e
1 name doc-a doc-b doc-c doc-d
2 joe x
3 ellen x x
4 mike x
5 quinn x x

<Second table>
a b c d e
1 name job-a job-b job-c job-d
2 joe x x
3 ellen x
4 mike x x x
5 quinn x x


On the first table, Joe is linked to doc-a. (Joe is assigned to read
document a). On the second table, Joe is linked to job-b and job-d (these
are the jobs he performs). So on the third table, job-b and job-d would be
listed next to doc-a, to indicate a job to document link. Next, Ellen is
linked to doc-a and doc-b. On the second table Ellen is linked to job-b, so
doc-a and doc-b will be linked to job-b, and so on. The results in the third
table should look like this:

<Third table>
a b c d
1 document first_job second_job third_job
2 doc-a job-b job-d
3 doc-b job-b job-d
4 doc-c job-a job-b job-c
5 doc-d job-b job-d

Alternately, a table could be made to find inconsistencies in reading
assignments (it would be a bonus to have a solution for this too);

<Fourth table>
a b c d

1 job name first doc second doc
2 job-a Mike doc-c
3 job-b Joe doc-a
4 job-b Ellen doc-a doc-b
5 job-b Mike doc-c
6 job-b Quinn doc-b doc-d
7 job-c Mike doc-c
8 job-d Joe doc-a
9 job-d Quinn doc-b doc-d

 

[T. Valko]

Ok...

Table #3 is giving me headaches! I can see the relation but trying to put
that in a formula is making my head spin. After messing around with it for
about an hour I decided to try and come up with table #4. Ah, that was much
easier *if* you have the row headers already listed:

job...... name..... first doc..... second doc
job-a
job-b
job-b
job-b
job-b
job-c
job-d
job-d

It would probably be easier to show you this via a sample file rather than
trying to explain it in writing. So, here's a sample file:

tables.xls 18kb

http://cjoint.com/?ktenHQn4Jv

There are 2 different formulas needed. Both are array formulas**.

One formula entered in cell H2 then copied down. The other formula entered
in cell I2 and copied across to J2 then down.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)