want to findout dropout

[younker1983]

bro.. i have one problem.. in excel

i have 2 different spreadsheets
sheet 1 and sheet 2
in sheet 1 i have suppouse following data


Name-- Cust.Code -- Prod. --value --year

-- a -- 001 -- 24 -- 100 -- 2004
-- b -- 002 -- 26 -- 240 -- 2004
-- a -- 001 -- 33 -- 120 -- 2004
-- a -- 001 -- 21 -- 200 -- 2004
-- c -- 003 -- 26 -- 240 -- 2004
-- b -- 002 -- 33 -- 120 -- 2004
-- d -- 004 -- 24 -- 100 -- 2004



in sheet 2

Name -- Cust.Code -- Prod. -- value -- year

-- a -- 001 -- 24 -- 100 -- 2005
-- d -- 004 -- 26 -- 240 -- 2005
-- a -- 001 -- 33 -- 120 -- 2005
-- e -- 005 -- 26 -- 240 -- 2005
-- a -- 001 -- 21 -- 200 -- 2005
-- c -- 003 -- 26 -- 240 -- 2005
-- d -- 004 -- 24 -- 100 -- 2005

in this situation i want to find out the dropouts of sheet 1. (that persons which are not present the sheet 2)how can i compare these sheets?
in this case i want the result as


-- Name -- Cust.Code -- Prod. -- value -- Year
-- b -- 002 -- 26 -- 240 -- 2004
-- b -- 002 -- 33 -- 120 -- 2004

Thanx & Regards
Manu Verma

[Bob Phillips]

You could just use conditional formatting, with a formula of

=MATCH(1,($B2=Sheet2!=$B$2:$B$200)*($C2=Sheet2!$C$2:$C$200),0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"younker1983" <younker1983.2cinze_1155536404.3876@excelforum-nospam.com>
wrote in message
news:younker1983.2cinze_1155536404.3876@excelforum-nospam.com...
>
> bro.. i have one problem.. in excel
>
> i have 2 different spreadsheets
> sheet 1 and sheet 2
> in sheet 1 i have suppouse following data
>
>
> Name-- Cust.Code -- Prod. --value --year
>
> -- a -- 001 -- 24 -- 100 -- 2004
> -- b -- 002 -- 26 -- 240 -- 2004
> -- a -- 001 -- 33 -- 120 -- 2004
> -- a -- 001 -- 21 -- 200 -- 2004
> -- c -- 003 -- 26 -- 240 -- 2004
> -- b -- 002 -- 33 -- 120 -- 2004
> -- d -- 004 -- 24 -- 100 -- 2004
>
>
>
> in sheet 2
>
> Name -- Cust.Code -- Prod. -- value -- year
>
> -- a -- 001 -- 24 -- 100 -- 2005
> -- d -- 004 -- 26 -- 240 -- 2005
> -- a -- 001 -- 33 -- 120 -- 2005
> -- e -- 005 -- 26 -- 240 -- 2005
> -- a -- 001 -- 21 -- 200 -- 2005
> -- c -- 003 -- 26 -- 240 -- 2005
> -- d -- 004 -- 24 -- 100 -- 2005
>
> in this situation i want to find out the dropouts of sheet 1. *(that
> persons which are not present the sheet 2)*how can i compare these
> sheets?
> in this case i want the result as
>
>
> -- Name -- Cust.Code -- Prod. -- value -- Year
> -- b -- 002 -- 26 -- 240 -- 2004
> -- b -- 002 -- 33 -- 120 -- 2004
>
> Thanx & Regards
> Manu Verma
>
>
> --
> younker1983
> ------------------------------------------------------------------------
> younker1983's Profile:
http://www.excelforum.com/member.php...o&userid=37467
> View this thread: http://www.excelforum.com/showthread...hreadid=571281
>