software

I want to compare 2 lists on separate worksheets - (A2:A9) on both
sheets. The lists contain text. If the lists are equal I want the text
in cell A1 on the second worksheet to be quot;PROPERquot;case. If the lists are
not equal I want the text to be quot;UPPERquot; case. Can I use a standard
formula or an array formula to do this? Thanks for any help!--
elevdown
------------------------------------------------------------------------
elevdown's Profile: www.excelforum.com/member.php...oamp;userid=29668
View this thread: www.excelforum.com/showthread...hreadid=493762In another cell

Sheet2!C2: =IF(A2=Sheet1!A2,PROPER(A2),UPPER(A2)

and copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;elevdownquot; gt; wrote in
message ...
gt;
gt; I want to compare 2 lists on separate worksheets - (A2:A9) on both
gt; sheets. The lists contain text. If the lists are equal I want the text
gt; in cell A1 on the second worksheet to be quot;PROPERquot;case. If the lists are
gt; not equal I want the text to be quot;UPPERquot; case. Can I use a standard
gt; formula or an array formula to do this? Thanks for any help!
gt;
gt;
gt; --
gt; elevdown
gt; ------------------------------------------------------------------------
gt; elevdown's Profile:
www.excelforum.com/member.php...oamp;userid=29668
gt; View this thread: www.excelforum.com/showthread...hreadid=493762
gt;

Bob Phillips Wrote:
gt; In another cell
gt;
gt; Sheet2!C2: =IF(A2=Sheet1!A2,PROPER(A2),UPPER(A2)
gt;
gt; and copy down
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;elevdownquot; gt;
gt; wrote in
gt; message ...
gt; gt;
gt; gt; I want to compare 2 lists on separate worksheets - (A2:A9) on both
gt; gt; sheets. The lists contain text. If the lists are equal I want the
gt; text
gt; gt; in cell A1 on the second worksheet to be quot;PROPERquot;case. If the lists
gt; are
gt; gt; not equal I want the text to be quot;UPPERquot; case. Can I use a standard
gt; gt; formula or an array formula to do this? Thanks for any help!
gt; gt;
gt; gt;
gt; gt; --
gt; gt; elevdown
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; elevdown's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29668
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=493762
gt; gt;Thanks, but won't this capitalize text in the list, if it is not equal?
What I want to do is to capitalize the text only in cell A1 if the lists
are not equal.--
elevdown
------------------------------------------------------------------------
elevdown's Profile: www.excelforum.com/member.php...oamp;userid=29668
View this thread: www.excelforum.com/showthread...hreadid=493762Every row matches?

=IF(SUMPRODUCT(--(A2:A9=Sheet2!A2:A9))=8,PROPER(A1),UPPER(A1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;elevdownquot; gt; wrote in
message ...
gt;
gt; Bob Phillips Wrote:
gt; gt; In another cell
gt; gt;
gt; gt; Sheet2!C2: =IF(A2=Sheet1!A2,PROPER(A2),UPPER(A2)
gt; gt;
gt; gt; and copy down
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;elevdownquot; gt;
gt; gt; wrote in
gt; gt; message ...
gt; gt; gt;
gt; gt; gt; I want to compare 2 lists on separate worksheets - (A2:A9) on both
gt; gt; gt; sheets. The lists contain text. If the lists are equal I want the
gt; gt; text
gt; gt; gt; in cell A1 on the second worksheet to be quot;PROPERquot;case. If the lists
gt; gt; are
gt; gt; gt; not equal I want the text to be quot;UPPERquot; case. Can I use a standard
gt; gt; gt; formula or an array formula to do this? Thanks for any help!
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; elevdown
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; elevdown's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=29668
gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=493762
gt; gt; gt;
gt;
gt;
gt; Thanks, but won't this capitalize text in the list, if it is not equal?
gt; What I want to do is to capitalize the text only in cell A1 if the lists
gt; are not equal.
gt;
gt;
gt; --
gt; elevdown
gt; ------------------------------------------------------------------------
gt; elevdown's Profile:
www.excelforum.com/member.php...oamp;userid=29668
gt; View this thread: www.excelforum.com/showthread...hreadid=493762
gt;

Bob Phillips Wrote:
gt; Every row matches?
gt;
gt; =IF(SUMPRODUCT(--(A2:A9=Sheet2!A2:A9))=8,PROPER(A1),UPPER(A1))
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;elevdownquot; gt;
gt; wrote in
gt; message ...
gt; gt;
gt; gt; Bob Phillips Wrote:
gt; gt; gt; In another cell
gt; gt; gt;
gt; gt; gt; Sheet2!C2: =IF(A2=Sheet1!A2,PROPER(A2),UPPER(A2)
gt; gt; gt;
gt; gt; gt; and copy down
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;elevdownquot; gt;
gt; gt; gt; wrote in
gt; gt; gt; message
gt; ...
gt; gt; gt; gt;
gt; gt; gt; gt; I want to compare 2 lists on separate worksheets - (A2:A9) on
gt; both
gt; gt; gt; gt; sheets. The lists contain text. If the lists are equal I want
gt; the
gt; gt; gt; text
gt; gt; gt; gt; in cell A1 on the second worksheet to be quot;PROPERquot;case. If the
gt; lists
gt; gt; gt; are
gt; gt; gt; gt; not equal I want the text to be quot;UPPERquot; case. Can I use a
gt; standard
gt; gt; gt; gt; formula or an array formula to do this? Thanks for any help!
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; elevdown
gt; gt; gt; gt;
gt; gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; elevdown's Profile:
gt; gt; gt; www.excelforum.com/member.php...oamp;userid=29668
gt; gt; gt; gt; View this thread:
gt; gt; gt; www.excelforum.com/showthread...hreadid=493762
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt; Thanks, but won't this capitalize text in the list, if it is not
gt; equal?
gt; gt; What I want to do is to capitalize the text only in cell A1 if the
gt; lists
gt; gt; are not equal.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; elevdown
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; elevdown's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29668
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=493762
gt; gt;

(Yes, I want all 8 cells in each list to match exactly)
OK, I tried this. Below is the two formulas that I tried. The first
formula compares the lists on two separate worksheets: It doesn't work
- it always displays in UPPER case whether the lists are the same or
not. The second formula I brought the two lists to the same worksheet
for comparison (columns A amp; B) and the formula works fine!
=IF(SUMPRODUCT(--(Sheet1!A2:A9=Sheet2!A2:A9))=8,UPPER(A1),PROPER(A1 ))
=IF(SUMPRODUCT(--(A2:A9=B2:B9))=8,UPPER(A1),PROPER(A1))
Can you expain why the comparison of two separate worksheets did not
work? Can you also explain what the quot;--quot; does in the formulas above?
I am thinking I need to copy the lists to the same worksheet (don't
want to if I don't have to!)
Thanks so much for your help!--
elevdown
------------------------------------------------------------------------
elevdown's Profile: www.excelforum.com/member.php...oamp;userid=29668
View this thread: www.excelforum.com/showthread...hreadid=493762I cannot explain the problem as I don't get it. I can get PROPER or UPPER
accordingly when the data is on separate sheets.

As for the --, see www.xldynamic.com/source/xld.SUMPRODUCT.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;elevdownquot; gt; wrote in
message ...
gt;
gt; Bob Phillips Wrote:
gt; gt; Every row matches?
gt; gt;
gt; gt; =IF(SUMPRODUCT(--(A2:A9=Sheet2!A2:A9))=8,PROPER(A1),UPPER(A1))
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;elevdownquot; gt;
gt; gt; wrote in
gt; gt; message ...
gt; gt; gt;
gt; gt; gt; Bob Phillips Wrote:
gt; gt; gt; gt; In another cell
gt; gt; gt; gt;
gt; gt; gt; gt; Sheet2!C2: =IF(A2=Sheet1!A2,PROPER(A2),UPPER(A2)
gt; gt; gt; gt;
gt; gt; gt; gt; and copy down
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;elevdownquot; gt;
gt; gt; gt; gt; wrote in
gt; gt; gt; gt; message
gt; gt; ...
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I want to compare 2 lists on separate worksheets - (A2:A9) on
gt; gt; both
gt; gt; gt; gt; gt; sheets. The lists contain text. If the lists are equal I want
gt; gt; the
gt; gt; gt; gt; text
gt; gt; gt; gt; gt; in cell A1 on the second worksheet to be quot;PROPERquot;case. If the
gt; gt; lists
gt; gt; gt; gt; are
gt; gt; gt; gt; gt; not equal I want the text to be quot;UPPERquot; case. Can I use a
gt; gt; standard
gt; gt; gt; gt; gt; formula or an array formula to do this? Thanks for any help!
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; elevdown
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; elevdown's Profile:
gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=29668
gt; gt; gt; gt; gt; View this thread:
gt; gt; gt; gt; www.excelforum.com/showthread...hreadid=493762
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Thanks, but won't this capitalize text in the list, if it is not
gt; gt; equal?
gt; gt; gt; What I want to do is to capitalize the text only in cell A1 if the
gt; gt; lists
gt; gt; gt; are not equal.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; elevdown
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; elevdown's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=29668
gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=493762
gt; gt; gt;
gt;
gt; (Yes, I want all 8 cells in each list to match exactly)
gt; OK, I tried this. Below is the two formulas that I tried. The first
gt; formula compares the lists on two separate worksheets: It doesn't work
gt; - it always displays in UPPER case whether the lists are the same or
gt; not. The second formula I brought the two lists to the same worksheet
gt; for comparison (columns A amp; B) and the formula works fine!
gt; =IF(SUMPRODUCT(--(Sheet1!A2:A9=Sheet2!A2:A9))=8,UPPER(A1),PROPER(A1 ))
gt; =IF(SUMPRODUCT(--(A2:A9=B2:B9))=8,UPPER(A1),PROPER(A1))
gt; Can you expain why the comparison of two separate worksheets did not
gt; work? Can you also explain what the quot;--quot; does in the formulas above?
gt; I am thinking I need to copy the lists to the same worksheet (don't
gt; want to if I don't have to!)
gt; Thanks so much for your help!
gt;
gt;
gt; --
gt; elevdown
gt; ------------------------------------------------------------------------
gt; elevdown's Profile:
www.excelforum.com/member.php...oamp;userid=29668
gt; View this thread: www.excelforum.com/showthread...hreadid=493762
gt;