Requesting Frank Kabel's Help / Comparing 2 Tables

[carl]

I am trying to create a table based on data in 2 other
tables.

Table1 has my products listed in rows and assigned sales
people in columns. There is a 1 in the cell if the
salesperson has been assigned to sell the product.

Table1
JIM BOB JOE ED
BOX1 1 1 1
BOX2 1 1 1 1
BOX3 1 1

Table2 records sales. Only sales people that have made
sales in one of the products gets recorded on the
table2 - thus not all salespeople are listed in table2.

Table2
BOB ED
BOX1
BOX2 22 10
BOX3 10

I am trying to create a 3rd table that will have the same
rows and columns as table1, but look at table2 and tell
me if a salesperson was assigned but did not sell the
product. For example, Since JIM is assigned to sell BOX1
(Table1) but did not sell any BOX (Table2), Table3 tell ne
to check JIM/BOX1.

Table3
JIM BOB JOE ED
BOX1 check check check
BOX2 check check
BOX3 check

I think this is possible but cannot get it done.

Thank you very much (in advance).

 

[hgrove]

And if Frank actually took vacation, you'd be up the creek without
paddle? You're free to ignore this and wait for Frank to respond.

carl wrote...
...

Table1 has my products listed in rows and assigned sales people
in columns. There is a 1 in the cell if the salesperson has been
assigned to sell the product.

______JIM__BOB__JOE__ED
BOX1___1_________1___1
BOX2___1____1____1___1
BOX3________1________1

Table2 records sales. Only sales people that have made sales in
one of the products gets recorded on the table2 - thus not all
salespeople are listed in table2.

This is a questionable design. It makes using Table 2 in other formula
considerably more difficult - but not impossible.

______BOB___ED
BOX1__________
BOX2___22___10
BOX3___10_____

I am trying to create a 3rd table that will have the same rows
and columns as table1, but look at table2 and tell me if a
salesperson was assigned but did not sell the product. For
example, Since JIM is assigned to sell BOX1 (Table1) but did not
sell any BOX (Table2), Table3 tell ne to check JIM/BOX1.

______JIM___BOB__JOE__ED
BOX1_check______check_check
BOX2_check______check_____
BOX3_________________check

If Table 1 were in A2:E5 so that the JIM-BOX1 cell were B3, Table
were named Tbl2, and Table 3 were in A14:E17 with top row and lef
column both *exactly* the same as in Table 1, you could enter th
following formula for the JIM-BOX1 cell in Table 3,

B15:
=IF(AND(B3=1,ISERROR(1/(INDEX(Tbl2,0,MATCH(B$14,
INDEX(Tbl2,1,0),0)) INDEX(Tbl2,MATCH($A15,
INDEX(Tbl2,0,1),0),0)))),"check","")

then select B15 and fill down into B16:B17, then select B15:B17 an
fill right into C15:E17. This assumes Table 2 contains nonzero number
within the table

 

[carl]

thank you. i tried the formula and it excel responded with
the error message box.

=IF(AND(B3=1,ISERROR(1/(INDEX(Tbl2,0,MATCH(B$14,
INDEX(Tbl2,1,0),0)) INDEX(Tbl2,MATCH($A15,
INDEX(Tbl2,0,1),0),0)))),"check","")

Is there an operator needed somewhere ?

Sorry to bother you. Thank you in advance.

 

[hgrove]

carl wrote...

thank you. i tried the formula and it excel responded with the
error message box.

Is there an operator needed somewhere ?

...

I had tested this, and it works if you use it as written. Note that I
*intended* to have a space between the two INDEX calls. That's Excel's
range intersection operator. So try the formula again, but *DON'T*
change anything other than the cell addresses.