Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare A&B columns with C&D columns
Hello from Steved
I have 4 Columns of information Compare A&B columns with C&D columns If C&D differ from A&B display "False" What would be the Formula for the above please. Thankyou. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare A&B columns with C&D columns
"Steved" wrote:
Compare A&B columns with C&D columns If C&D differ from A&B display "False" What would be the Formula for the above please. Perhaps one of the following equivalent formulas. The following array formula (commit with ctrl-shift-Enter instead of Enter): =AND(A1:A4=C1:C4,B1:B4=D1:D4) Alternatively, the following normal formula (commit with Enter as usual): =SUMPRODUCT(AND(A1:A4=C1:C4)*AND(B1:B4=D1:D4))0 Note: These formulas display FALSE if the pair-wise columns differ. But the formulas also display TRUE if the columns do not differ. You did not specify the latter. If that is not acceptable, there is a simple fix. For example: =IF(SUMPRODUCT(AND(A1:A4=C1:C4)*AND(B1:B4=D1:D4)) 0, "") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare A&B columns with C&D columns
Hello JoeU2004
I've gone with the Below =AND(A1:A4=C1:C4,B1:B4=D1:D4) With the Above Can I have it Display "FALSE" in Red Text with a Yellow Background please, or is there another formula that will allow this. I Thankyou Steved "JoeU2004" wrote: "Steved" wrote: Compare A&B columns with C&D columns If C&D differ from A&B display "False" What would be the Formula for the above please. Perhaps one of the following equivalent formulas. The following array formula (commit with ctrl-shift-Enter instead of Enter): =AND(A1:A4=C1:C4,B1:B4=D1:D4) Alternatively, the following normal formula (commit with Enter as usual): =SUMPRODUCT(AND(A1:A4=C1:C4)*AND(B1:B4=D1:D4))0 Note: These formulas display FALSE if the pair-wise columns differ. But the formulas also display TRUE if the columns do not differ. You did not specify the latter. If that is not acceptable, there is a simple fix. For example: =IF(SUMPRODUCT(AND(A1:A4=C1:C4)*AND(B1:B4=D1:D4)) 0, "") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare A&B columns with C&D columns
"Steved" wrote:
With the Above Can I have it Display "FALSE" in Red Text with a Yellow Background please Geesh! Next, you'll be asking for the world! Just kidding.... In Excel 2003, click on Format Conditional Formatting, and select "Cell Value Is", "equal to", FALSE. Then click on Format (in the Conditional Formatting dialog box), and set Font Color (red) and Patterns (yellow). ----- original message ----- "Steved" wrote in message ... Hello JoeU2004 I've gone with the Below =AND(A1:A4=C1:C4,B1:B4=D1:D4) With the Above Can I have it Display "FALSE" in Red Text with a Yellow Background please, or is there another formula that will allow this. I Thankyou Steved "JoeU2004" wrote: "Steved" wrote: Compare A&B columns with C&D columns If C&D differ from A&B display "False" What would be the Formula for the above please. Perhaps one of the following equivalent formulas. The following array formula (commit with ctrl-shift-Enter instead of Enter): =AND(A1:A4=C1:C4,B1:B4=D1:D4) Alternatively, the following normal formula (commit with Enter as usual): =SUMPRODUCT(AND(A1:A4=C1:C4)*AND(B1:B4=D1:D4))0 Note: These formulas display FALSE if the pair-wise columns differ. But the formulas also display TRUE if the columns do not differ. You did not specify the latter. If that is not acceptable, there is a simple fix. For example: =IF(SUMPRODUCT(AND(A1:A4=C1:C4)*AND(B1:B4=D1:D4)) 0, "") |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare A&B columns with C&D columns
Thankyou.
"JoeU2004" wrote: "Steved" wrote: With the Above Can I have it Display "FALSE" in Red Text with a Yellow Background please Geesh! Next, you'll be asking for the world! Just kidding.... In Excel 2003, click on Format Conditional Formatting, and select "Cell Value Is", "equal to", FALSE. Then click on Format (in the Conditional Formatting dialog box), and set Font Color (red) and Patterns (yellow). ----- original message ----- "Steved" wrote in message ... Hello JoeU2004 I've gone with the Below =AND(A1:A4=C1:C4,B1:B4=D1:D4) With the Above Can I have it Display "FALSE" in Red Text with a Yellow Background please, or is there another formula that will allow this. I Thankyou Steved "JoeU2004" wrote: "Steved" wrote: Compare A&B columns with C&D columns If C&D differ from A&B display "False" What would be the Formula for the above please. Perhaps one of the following equivalent formulas. The following array formula (commit with ctrl-shift-Enter instead of Enter): =AND(A1:A4=C1:C4,B1:B4=D1:D4) Alternatively, the following normal formula (commit with Enter as usual): =SUMPRODUCT(AND(A1:A4=C1:C4)*AND(B1:B4=D1:D4))0 Note: These formulas display FALSE if the pair-wise columns differ. But the formulas also display TRUE if the columns do not differ. You did not specify the latter. If that is not acceptable, there is a simple fix. For example: =IF(SUMPRODUCT(AND(A1:A4=C1:C4)*AND(B1:B4=D1:D4)) 0, "") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare columns | Excel Worksheet Functions | |||
Compare Two columns | Excel Worksheet Functions | |||
Compare 4 columns? | Excel Worksheet Functions | |||
Compare two columns | Excel Discussion (Misc queries) | |||
Compare columns | Excel Discussion (Misc queries) |