|
Capitalize Text if 2 lists are equal
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 "PROPER"case. If the lists are not equal I want the text to be "UPPER" case. Can I use a standard formula or an array formula to do this? Thanks for any help! -- elevdown ------------------------------------------------------------------------ elevdown's Profile: http://www.excelforum.com/member.php...o&userid=29668 View this thread: http://www.excelforum.com/showthread...hreadid=493762 |
Capitalize Text if 2 lists are equal
In 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) "elevdown" wrote in message ... 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 "PROPER"case. If the lists are not equal I want the text to be "UPPER" case. Can I use a standard formula or an array formula to do this? Thanks for any help! -- elevdown ------------------------------------------------------------------------ elevdown's Profile: http://www.excelforum.com/member.php...o&userid=29668 View this thread: http://www.excelforum.com/showthread...hreadid=493762 |
Capitalize Text if 2 lists are equal
Bob Phillips Wrote: In 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) "elevdown" wrote in message ... 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 "PROPER"case. If the lists are not equal I want the text to be "UPPER" case. Can I use a standard formula or an array formula to do this? Thanks for any help! -- elevdown ------------------------------------------------------------------------ elevdown's Profile: http://www.excelforum.com/member.php...o&userid=29668 View this thread: http://www.excelforum.com/showthread...hreadid=493762 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: http://www.excelforum.com/member.php...o&userid=29668 View this thread: http://www.excelforum.com/showthread...hreadid=493762 |
Capitalize Text if 2 lists are equal
Every 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) "elevdown" wrote in message ... Bob Phillips Wrote: In 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) "elevdown" wrote in message ... 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 "PROPER"case. If the lists are not equal I want the text to be "UPPER" case. Can I use a standard formula or an array formula to do this? Thanks for any help! -- elevdown ------------------------------------------------------------------------ elevdown's Profile: http://www.excelforum.com/member.php...o&userid=29668 View this thread: http://www.excelforum.com/showthread...hreadid=493762 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: http://www.excelforum.com/member.php...o&userid=29668 View this thread: http://www.excelforum.com/showthread...hreadid=493762 |
Capitalize Text if 2 lists are equal
Bob Phillips Wrote: Every 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) "elevdown" wrote in message ... Bob Phillips Wrote: In 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) "elevdown" wrote in message ... 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 "PROPER"case. If the lists are not equal I want the text to be "UPPER" case. Can I use a standard formula or an array formula to do this? Thanks for any help! -- elevdown ------------------------------------------------------------------------ elevdown's Profile: http://www.excelforum.com/member.php...o&userid=29668 View this thread: http://www.excelforum.com/showthread...hreadid=493762 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: http://www.excelforum.com/member.php...o&userid=29668 View this thread: http://www.excelforum.com/showthread...hreadid=493762 (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 & 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 "--" 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: http://www.excelforum.com/member.php...o&userid=29668 View this thread: http://www.excelforum.com/showthread...hreadid=493762 |
Capitalize Text if 2 lists are equal
I 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 http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH Bob Phillips (remove nothere from email address if mailing direct) "elevdown" wrote in message ... Bob Phillips Wrote: Every 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) "elevdown" wrote in message ... Bob Phillips Wrote: In 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) "elevdown" wrote in message ... 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 "PROPER"case. If the lists are not equal I want the text to be "UPPER" case. Can I use a standard formula or an array formula to do this? Thanks for any help! -- elevdown ------------------------------------------------------------------------ elevdown's Profile: http://www.excelforum.com/member.php...o&userid=29668 View this thread: http://www.excelforum.com/showthread...hreadid=493762 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: http://www.excelforum.com/member.php...o&userid=29668 View this thread: http://www.excelforum.com/showthread...hreadid=493762 (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 & 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 "--" 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: http://www.excelforum.com/member.php...o&userid=29668 View this thread: http://www.excelforum.com/showthread...hreadid=493762 |
| All times are GMT +1. The time now is 03:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com