| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Attn: Bob Phillips (or any expert!)... help with formula
This is a question from "Dan the Man" which I failed completely to address to
I'm admitting defeat and calling on the experts for help. Dan wants the formula below modified so that if there is an "x" in column C then the data should not be included in the calculation of duplicates. In the sample 1 below, Kent Clark would count as a duplicate ("Duplicate Names found") but Harry Potter would not. In sample 2 "No duplicate names found" would result Sample 1 Potter Harry x Kent Clark Kent Clark Potter Harry x Sample 2 Potter Harry x Kent Clark x Kent Clark x Potter Harry x Dan's formula: =IF(SUM(IF(A4:A3500&B4:B3500<"",--(MATCH(A4:A3500&B4:B3500,A4:A3500&B4:B3500,0)=ROW( A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<"")+(B4:B3500<"")0)), "No Duplicate Names Found", "Duplicate Names Found") My attempt included the following ... =IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<""&$C$4:$C$3 500<"x",--(MATCH($A$4:$A$3500&$B$4:$B$3500&$C$4:$C$3500,$A$4 :$A$3500&$B$4:$B$3500&$C$4:$C$3500,0) ..... but if this is correct, I couldn't get the remaining part of the formula to work i.e. I don't completely undertand how it works! Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bob Phillips (or any expert!)... help with formula
Hi Toppers
Does the array entered formula {=IF(C4="x","No Duplicate",IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<"" ,--(MATCH($A$4:$A$3500&$B$4:$B$3500,$A$4:$A$3500&$B$4 :$B$3500,0)=ROW($A$4:$B$3500)-MIN(ROW($B$4:$B$3500))+1),0))=SUM(--(($A$4:$A$3500<"")+($B$4:$B$3500<"")0)),"No Duplicate Names Found", "Duplicate Names Found"))} provide what is required -- Regards Roger Govier "Toppers" wrote in message ... This is a question from "Dan the Man" which I failed completely to address to I'm admitting defeat and calling on the experts for help. Dan wants the formula below modified so that if there is an "x" in column C then the data should not be included in the calculation of duplicates. In the sample 1 below, Kent Clark would count as a duplicate ("Duplicate Names found") but Harry Potter would not. In sample 2 "No duplicate names found" would result Sample 1 Potter Harry x Kent Clark Kent Clark Potter Harry x Sample 2 Potter Harry x Kent Clark x Kent Clark x Potter Harry x Dan's formula: =IF(SUM(IF(A4:A3500&B4:B3500<"",--(MATCH(A4:A3500&B4:B3500,A4:A3500&B4:B3500,0)=ROW( A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<"")+(B4:B3500<"")0)), "No Duplicate Names Found", "Duplicate Names Found") My attempt included the following ... =IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<""&$C$4:$C$3 500<"x",--(MATCH($A$4:$A$3500&$B$4:$B$3500&$C$4:$C$3500,$A$4 :$A$3500&$B$4:$B$3500&$C$4:$C$3500,0) ..... but if this is correct, I couldn't get the remaining part of the formula to work i.e. I don't completely undertand how it works! Thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bob Phillips (or any expert!)... help with formula
Roger,
Unfortunately no! With reference to my sample below, the original test is that if there is any match of A & B e.g. rows 5 & 6, then the result is "Duplicates Found" ; the original formula would also have included 4 & 7 as it doesn't test for "x" in column C. It simply reports that there are duplicates NOT what they are (which is done using CF). The revised test is to add the condition that if column C ="x" where there is a corresponding A & B match i.e. 4 & 7, then treat this as a non-match (ignore it). Hence my logic of concatenating A,B,C in the MATCH statement: what I don't full understand is this part of the formula ... ....=ROW(A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<"")+(B4:B3500<"")0)) and therefore how to adjust it if the A,B,C MATCH change I tried is the correct way. It appears the logical way to me! A B C 4 Potter Harry x 5 Kent Clark 6 Kent Clark 7 Potter Harry x "Roger Govier" wrote: Hi Toppers Does the array entered formula {=IF(C4="x","No Duplicate",IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<"" ,--(MATCH($A$4:$A$3500&$B$4:$B$3500,$A$4:$A$3500&$B$4 :$B$3500,0)=ROW($A$4:$B$3500)-MIN(ROW($B$4:$B$3500))+1),0))=SUM(--(($A$4:$A$3500<"")+($B$4:$B$3500<"")0)),"No Duplicate Names Found", "Duplicate Names Found"))} provide what is required -- Regards Roger Govier "Toppers" wrote in message ... This is a question from "Dan the Man" which I failed completely to address to I'm admitting defeat and calling on the experts for help. Dan wants the formula below modified so that if there is an "x" in column C then the data should not be included in the calculation of duplicates. In the sample 1 below, Kent Clark would count as a duplicate ("Duplicate Names found") but Harry Potter would not. In sample 2 "No duplicate names found" would result Sample 1 Potter Harry x Kent Clark Kent Clark Potter Harry x Sample 2 Potter Harry x Kent Clark x Kent Clark x Potter Harry x Dan's formula: =IF(SUM(IF(A4:A3500&B4:B3500<"",--(MATCH(A4:A3500&B4:B3500,A4:A3500&B4:B3500,0)=ROW( A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<"")+(B4:B3500<"")0)), "No Duplicate Names Found", "Duplicate Names Found") My attempt included the following ... =IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<""&$C$4:$C$3 500<"x",--(MATCH($A$4:$A$3500&$B$4:$B$3500&$C$4:$C$3500,$A$4 :$A$3500&$B$4:$B$3500&$C$4:$C$3500,0) ..... but if this is correct, I couldn't get the remaining part of the formula to work i.e. I don't completely undertand how it works! Thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bob Phillips (or any expert!)... help with formula
=IF(SUM(IF(RIGHT($A$4:$A$10&$B$4:$B$10&$C$4:$C$10) <"x",--(MATCH($A$4:$A$10&$B$4:$B$10,$A$4:$A$10&$B$4:$B$10 ,0)=ROW($A$4:$A$10)-MIN(ROW($B$4:$B$10))+1),0))=SUM(--(($A$4:$A$10<"")+($B$4:$B$10<"")0))-COUNTIF($C$4:$C$10,"x"),"No
Duplicate Names Found", "Duplicate Names Found") This APPEARS to work BUT the proviso is that the range is limited to number of non-blank entries i.e if I change $10 to $11 I get a wrong result! [because of ROW($A$4:$A$10)-MIN(ROW($B$4:$B$10))+1)?] And I cannot work how (or if) the "$A$4:$A$10&$B$4:$B$10&$C$4:$C$10" string can be enabled using dynamic ranges. "Toppers" wrote: Roger, Unfortunately no! With reference to my sample below, the original test is that if there is any match of A & B e.g. rows 5 & 6, then the result is "Duplicates Found" ; the original formula would also have included 4 & 7 as it doesn't test for "x" in column C. It simply reports that there are duplicates NOT what they are (which is done using CF). The revised test is to add the condition that if column C ="x" where there is a corresponding A & B match i.e. 4 & 7, then treat this as a non-match (ignore it). Hence my logic of concatenating A,B,C in the MATCH statement: what I don't full understand is this part of the formula ... ...=ROW(A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<"")+(B4:B3500<"")0)) and therefore how to adjust it if the A,B,C MATCH change I tried is the correct way. It appears the logical way to me! A B C 4 Potter Harry x 5 Kent Clark 6 Kent Clark 7 Potter Harry x "Roger Govier" wrote: Hi Toppers Does the array entered formula {=IF(C4="x","No Duplicate",IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<"" ,--(MATCH($A$4:$A$3500&$B$4:$B$3500,$A$4:$A$3500&$B$4 :$B$3500,0)=ROW($A$4:$B$3500)-MIN(ROW($B$4:$B$3500))+1),0))=SUM(--(($A$4:$A$3500<"")+($B$4:$B$3500<"")0)),"No Duplicate Names Found", "Duplicate Names Found"))} provide what is required -- Regards Roger Govier "Toppers" wrote in message ... This is a question from "Dan the Man" which I failed completely to address to I'm admitting defeat and calling on the experts for help. Dan wants the formula below modified so that if there is an "x" in column C then the data should not be included in the calculation of duplicates. In the sample 1 below, Kent Clark would count as a duplicate ("Duplicate Names found") but Harry Potter would not. In sample 2 "No duplicate names found" would result Sample 1 Potter Harry x Kent Clark Kent Clark Potter Harry x Sample 2 Potter Harry x Kent Clark x Kent Clark x Potter Harry x Dan's formula: =IF(SUM(IF(A4:A3500&B4:B3500<"",--(MATCH(A4:A3500&B4:B3500,A4:A3500&B4:B3500,0)=ROW( A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<"")+(B4:B3500<"")0)), "No Duplicate Names Found", "Duplicate Names Found") My attempt included the following ... =IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<""&$C$4:$C$3 500<"x",--(MATCH($A$4:$A$3500&$B$4:$B$3500&$C$4:$C$3500,$A$4 :$A$3500&$B$4:$B$3500&$C$4:$C$3500,0) ..... but if this is correct, I couldn't get the remaining part of the formula to work i.e. I don't completely undertand how it works! Thanks in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bob Phillips (or any expert!)... help with formula
Hi Toppers!
I tried the formula but couldn't get it to work on all testing parameters given the limitations you described. Hopefully someone will chime in (like Bob or Roger) with another idea that we haven't thought of. Ultimately, the master spreadsheet wil be of a longer range that 10, and I probably need it to go to at least 5000. Hopefully the Excel Gods will come up with an answer! Dan "Toppers" wrote: =IF(SUM(IF(RIGHT($A$4:$A$10&$B$4:$B$10&$C$4:$C$10) <"x",--(MATCH($A$4:$A$10&$B$4:$B$10,$A$4:$A$10&$B$4:$B$10 ,0)=ROW($A$4:$A$10)-MIN(ROW($B$4:$B$10))+1),0))=SUM(--(($A$4:$A$10<"")+($B$4:$B$10<"")0))-COUNTIF($C$4:$C$10,"x"),"No Duplicate Names Found", "Duplicate Names Found") This APPEARS to work BUT the proviso is that the range is limited to number of non-blank entries i.e if I change $10 to $11 I get a wrong result! [because of ROW($A$4:$A$10)-MIN(ROW($B$4:$B$10))+1)?] And I cannot work how (or if) the "$A$4:$A$10&$B$4:$B$10&$C$4:$C$10" string can be enabled using dynamic ranges. "Toppers" wrote: Roger, Unfortunately no! With reference to my sample below, the original test is that if there is any match of A & B e.g. rows 5 & 6, then the result is "Duplicates Found" ; the original formula would also have included 4 & 7 as it doesn't test for "x" in column C. It simply reports that there are duplicates NOT what they are (which is done using CF). The revised test is to add the condition that if column C ="x" where there is a corresponding A & B match i.e. 4 & 7, then treat this as a non-match (ignore it). Hence my logic of concatenating A,B,C in the MATCH statement: what I don't full understand is this part of the formula ... ...=ROW(A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<"")+(B4:B3500<"")0)) and therefore how to adjust it if the A,B,C MATCH change I tried is the correct way. It appears the logical way to me! A B C 4 Potter Harry x 5 Kent Clark 6 Kent Clark 7 Potter Harry x "Roger Govier" wrote: Hi Toppers Does the array entered formula {=IF(C4="x","No Duplicate",IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<"" ,--(MATCH($A$4:$A$3500&$B$4:$B$3500,$A$4:$A$3500&$B$4 :$B$3500,0)=ROW($A$4:$B$3500)-MIN(ROW($B$4:$B$3500))+1),0))=SUM(--(($A$4:$A$3500<"")+($B$4:$B$3500<"")0)),"No Duplicate Names Found", "Duplicate Names Found"))} provide what is required -- Regards Roger Govier "Toppers" wrote in message ... This is a question from "Dan the Man" which I failed completely to address to I'm admitting defeat and calling on the experts for help. Dan wants the formula below modified so that if there is an "x" in column C then the data should not be included in the calculation of duplicates. In the sample 1 below, Kent Clark would count as a duplicate ("Duplicate Names found") but Harry Potter would not. In sample 2 "No duplicate names found" would result Sample 1 Potter Harry x Kent Clark Kent Clark Potter Harry x Sample 2 Potter Harry x Kent Clark x Kent Clark x Potter Harry x Dan's formula: =IF(SUM(IF(A4:A3500&B4:B3500<"",--(MATCH(A4:A3500&B4:B3500,A4:A3500&B4:B3500,0)=ROW( A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<"")+(B4:B3500<"")0)), "No Duplicate Names Found", "Duplicate Names Found") My attempt included the following ... =IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<""&$C$4:$C$3 500<"x",--(MATCH($A$4:$A$3500&$B$4:$B$3500&$C$4:$C$3500,$A$4 :$A$3500&$B$4:$B$3500&$C$4:$C$3500,0) ..... but if this is correct, I couldn't get the remaining part of the formula to work i.e. I don't completely undertand how it works! Thanks in advance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Attn: Bob Phillips (or any expert!)... help with formula
Try this:
=IF(C1="x","",IF(MATCH(A1&B1,A1:A4&B1:B4,0),"DUP", "")) ctrl+shift+enter, not just enter "Toppers" wrote: This is a question from "Dan the Man" which I failed completely to address to I'm admitting defeat and calling on the experts for help. Dan wants the formula below modified so that if there is an "x" in column C then the data should not be included in the calculation of duplicates. In the sample 1 below, Kent Clark would count as a duplicate ("Duplicate Names found") but Harry Potter would not. In sample 2 "No duplicate names found" would result Sample 1 Potter Harry x Kent Clark Kent Clark Potter Harry x Sample 2 Potter Harry x Kent Clark x Kent Clark x Potter Harry x Dan's formula: =IF(SUM(IF(A4:A3500&B4:B3500<"",--(MATCH(A4:A3500&B4:B3500,A4:A3500&B4:B3500,0)=ROW( A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<"")+(B4:B3500<"")0)), "No Duplicate Names Found", "Duplicate Names Found") My attempt included the following ... =IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<""&$C$4:$C$3 500<"x",--(MATCH($A$4:$A$3500&$B$4:$B$3500&$C$4:$C$3500,$A$4 :$A$3500&$B$4:$B$3500&$C$4:$C$3500,0) ..... but if this is correct, I couldn't get the remaining part of the formula to work i.e. I don't completely undertand how it works! Thanks in advance. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Attn: Bob Phillips (or any expert!)... help with formula
Thanks Teethless Mama. That didn't seem to work, as it didn't account for
both matches (DUP) and non matches (NO DUP). I appreciate your input however. I won't give up until I get this one. Dan "Teethless mama" wrote: Try this: =IF(C1="x","",IF(MATCH(A1&B1,A1:A4&B1:B4,0),"DUP", "")) ctrl+shift+enter, not just enter "Toppers" wrote: This is a question from "Dan the Man" which I failed completely to address to I'm admitting defeat and calling on the experts for help. Dan wants the formula below modified so that if there is an "x" in column C then the data should not be included in the calculation of duplicates. In the sample 1 below, Kent Clark would count as a duplicate ("Duplicate Names found") but Harry Potter would not. In sample 2 "No duplicate names found" would result Sample 1 Potter Harry x Kent Clark Kent Clark Potter Harry x Sample 2 Potter Harry x Kent Clark x Kent Clark x Potter Harry x Dan's formula: =IF(SUM(IF(A4:A3500&B4:B3500<"",--(MATCH(A4:A3500&B4:B3500,A4:A3500&B4:B3500,0)=ROW( A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<"")+(B4:B3500<"")0)), "No Duplicate Names Found", "Duplicate Names Found") My attempt included the following ... =IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<""&$C$4:$C$3 500<"x",--(MATCH($A$4:$A$3500&$B$4:$B$3500&$C$4:$C$3500,$A$4 :$A$3500&$B$4:$B$3500&$C$4:$C$3500,0) ..... but if this is correct, I couldn't get the remaining part of the formula to work i.e. I don't completely undertand how it works! Thanks in advance. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bob Phillips (or any expert!)... help with formula
Hi John
I know you have excellent knowledge of Excel, and therefore there must be something that I am not understanding about the problem, but with my formula and your data, I get No Duplicates Duplicate Names Found Duplicate Names Found No Duplicates which is what I thought your wanted. Your formula returns me No Duplicates Found for all 4 lines. Note, my No Duplicates result, is the result of the first test, i.e. if there is an X in column C, don't carry out any further testing. The message can be changed to anything else or Null. Or is it that you need to show some form of message, that the result is a duplicate, but an allowed duplicate? If so then my formula obviously would not work. -- Regards Roger Govier "Toppers" wrote in message ... =IF(SUM(IF(RIGHT($A$4:$A$10&$B$4:$B$10&$C$4:$C$10) <"x",--(MATCH($A$4:$A$10&$B$4:$B$10,$A$4:$A$10&$B$4:$B$10 ,0)=ROW($A$4:$A$10)-MIN(ROW($B$4:$B$10))+1),0))=SUM(--(($A$4:$A$10<"")+($B$4:$B$10<"")0))-COUNTIF($C$4:$C$10,"x"),"No Duplicate Names Found", "Duplicate Names Found") This APPEARS to work BUT the proviso is that the range is limited to number of non-blank entries i.e if I change $10 to $11 I get a wrong result! [because of ROW($A$4:$A$10)-MIN(ROW($B$4:$B$10))+1)?] And I cannot work how (or if) the "$A$4:$A$10&$B$4:$B$10&$C$4:$C$10" string can be enabled using dynamic ranges. "Toppers" wrote: Roger, Unfortunately no! With reference to my sample below, the original test is that if there is any match of A & B e.g. rows 5 & 6, then the result is "Duplicates Found" ; the original formula would also have included 4 & 7 as it doesn't test for "x" in column C. It simply reports that there are duplicates NOT what they are (which is done using CF). The revised test is to add the condition that if column C ="x" where there is a corresponding A & B match i.e. 4 & 7, then treat this as a non-match (ignore it). Hence my logic of concatenating A,B,C in the MATCH statement: what I don't full understand is this part of the formula ... ...=ROW(A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<"")+(B4:B3500<"")0)) and therefore how to adjust it if the A,B,C MATCH change I tried is the correct way. It appears the logical way to me! A B C 4 Potter Harry x 5 Kent Clark 6 Kent Clark 7 Potter Harry x "Roger Govier" wrote: Hi Toppers Does the array entered formula {=IF(C4="x","No Duplicate",IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<"" ,--(MATCH($A$4:$A$3500&$B$4:$B$3500,$A$4:$A$3500&$B$4 :$B$3500,0)=ROW($A$4:$B$3500)-MIN(ROW($B$4:$B$3500))+1),0))=SUM(--(($A$4:$A$3500<"")+($B$4:$B$3500<"")0)),"No Duplicate Names Found", "Duplicate Names Found"))} provide what is required -- Regards Roger Govier "Toppers" wrote in message ... This is a question from "Dan the Man" which I failed completely to address to I'm admitting defeat and calling on the experts for help. Dan wants the formula below modified so that if there is an "x" in column C then the data should not be included in the calculation of duplicates. In the sample 1 below, Kent Clark would count as a duplicate ("Duplicate Names found") but Harry Potter would not. In sample 2 "No duplicate names found" would result Sample 1 Potter Harry x Kent Clark Kent Clark Potter Harry x Sample 2 Potter Harry x Kent Clark x Kent Clark x Potter Harry x Dan's formula: =IF(SUM(IF(A4:A3500&B4:B3500<"",--(MATCH(A4:A3500&B4:B3500,A4:A3500&B4:B3500,0)=ROW( A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<"")+(B4:B3500<"")0)), "No Duplicate Names Found", "Duplicate Names Found") My attempt included the following ... =IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<""&$C$4:$C$3 500<"x",--(MATCH($A$4:$A$3500&$B$4:$B$3500&$C$4:$C$3500,$A$4 :$A$3500&$B$4:$B$3500&$C$4:$C$3500,0) ..... but if this is correct, I couldn't get the remaining part of the formula to work i.e. I don't completely undertand how it works! Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is anyone a SUM(IF formula expert (array formulas)? | Excel Worksheet Functions | |||
| A challenge for a real Excel Expert (Bob Phillips for instance) | Excel Discussion (Misc queries) | |||
| another EXPERT LEVEL FORMULA from me | Excel Discussion (Misc queries) | |||
| ATTN: Mr. Bob Phillips | Excel Worksheet Functions | |||
| MVP...Formula expert needed!!! | Excel Worksheet Functions | |||
Linear Mode
