if a have two set of tables in two different sheets,,

i.e.

session1 - Table1(sheet1)
email Attend
yes
yes
no

session2 - Table2(sheet2)
email Attend
no
yes
no

can i create a new table at a new sheet showing me who didnt attend the two
sessions,,

help will be highly appreciated,,

With email address in A1,

=AND(VLOOKUP(A1,Table1,2,0)="yes",VLOOKUP(A1,Table 2,2,0)="yes")

and copy down.

"Kent - Tech Evangelist" wrote:

if a have two set of tables in two different sheets,,

i.e.

session1 - Table1(sheet1)
email Attend
yes
yes
no

session2 - Table2(sheet2)
email Attend
no
yes
no

can i create a new table at a new sheet showing me who didnt attend the two
sessions,,

help will be highly appreciated,,

thanks jmb,,

uuhmm,, as i tried it,, it doesnt work anymore if the email was arranged
differently,,

i.e. if was listed before ,,

"JMB" wrote:

With email address in A1,

=AND(VLOOKUP(A1,Table1,2,0)="yes",VLOOKUP(A1,Table 2,2,0)="yes")

and copy down.

"Kent - Tech Evangelist" wrote:

if a have two set of tables in two different sheets,,

i.e.

session1 - Table1(sheet1)
email Attend
yes
yes
no

session2 - Table2(sheet2)
email Attend
no
yes
no

can i create a new table at a new sheet showing me who didnt attend the two
sessions,,

help will be highly appreciated,,

with the fourth Vlookup argument set to false (or 0), it should not matter
what order the tables are in as it will find an exact match (and return #N/A
if no match is found). I am assuming the email addresses are in the first
column of your table and yes/no is in the second column. Also, with text you
have to be wary that there are no trailing spaces as Vlookup will not
recognize a match with extra spaces in the data.


"Kent - Tech Evangelist" wrote:

thanks jmb,,

uuhmm,, as i tried it,, it doesnt work anymore if the email was arranged
differently,,

i.e. if was listed before ,,

"JMB" wrote:

With email address in A1,

=AND(VLOOKUP(A1,Table1,2,0)="yes",VLOOKUP(A1,Table 2,2,0)="yes")

and copy down.

"Kent - Tech Evangelist" wrote:

if a have two set of tables in two different sheets,,

i.e.

session1 - Table1(sheet1)
email Attend
yes
yes
no

session2 - Table2(sheet2)
email Attend
no
yes
no

can i create a new table at a new sheet showing me who didnt attend the two
sessions,,

help will be highly appreciated,,

I'd do something like this:

Create a new sheet (call it sheet3)
Put Email in A1 (just as a header)
Copy the 2 lists into column A of this new sheet (one under the other)
(Don't include the headers when you copy--just the raw data)

Then select that range (A1:A###)
data|filter|advanced filter
Copy to another location
List range: (should be entered (a1:A###)
copy to: B1
Check Unique records only box

Now you have a list of unique names in column B.
Delete column A (we're done with it).

In B1, put: On Table 1
In C1, put: On Table 2

In B2, put this formula:
=isnumber(match(a2,sheet1!a:a,0))

In C2, put this formula:
=isnumber(match(a2,sheet2!a:a,0))

And drag down as far as column A extends.

Now select columns A:C and do Data|Filter|Autofilter.

You can filter to show the Falses in any column to see where its missing.

You could even add another column
=countif(b2:c2,true)
to count the number of times that person showed up.

Then filter to show 0, 1 or 2.




Kent - Tech Evangelist wrote:

if a have two set of tables in two different sheets,,

i.e.

session1 - Table1(sheet1)
email Attend
yes
yes
no

session2 - Table2(sheet2)
email Attend
no
yes
no

can i create a new table at a new sheet showing me who didnt attend the two
sessions,,

help will be highly appreciated,,

--

Dave Peterson