Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I too think I got lost in cyberspace: Formula Help
I was given a wonderful formula to identify and duplicate name entries on a
spreadsheet, and it works like a charm: =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") I also use "conditional formatting" (see below) to change all of the applicable "matches" to RED when they occur =SUMPRODUCT(--($A$4:$A$3500=$A4),--($B$4:$B$3500=$B4))1 Here is my question: Every now and then a duplicate entry is okay (as the duplicate identifies a different location address for an individual, or a different service that we are providing them). The main goal of the original formula above was to identify "accidental" entering of the same persons data more than once. My thought is to develop a third Row (Row C), and place an "x" in any cell where the duplicate names occur, but shouldn't be flagged (for the reasons described above as to why we sometimes wouldn't consider this a duplicate entry). What formula would best incorporate that additional requirement (to effectively turn off the "Duplicate Names Found" flag, and return to font to its original status). I suspect that once the "Duplicate Names Found" flag returns to "No Duplicate Names Found", the conditional formatting formula currently in place will take care of itself. Thanks much, Dan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I too think I got lost in cyberspace: Formula Help
=IF(SUMPRODUCT(--($A$4:$A$3500=$A5),--($B$4:$B$3500=$B5))1,"x","")
will place an "x" in any row where duplicate names occur. 1 Ian Black 2 John Smith x 3 John Smith x 4 Dave Brown Is this what you want? "Dan the Man" wrote: I was given a wonderful formula to identify and duplicate name entries on a spreadsheet, and it works like a charm: =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") I also use "conditional formatting" (see below) to change all of the applicable "matches" to RED when they occur =SUMPRODUCT(--($A$4:$A$3500=$A4),--($B$4:$B$3500=$B4))1 Here is my question: Every now and then a duplicate entry is okay (as the duplicate identifies a different location address for an individual, or a different service that we are providing them). The main goal of the original formula above was to identify "accidental" entering of the same persons data more than once. My thought is to develop a third Row (Row C), and place an "x" in any cell where the duplicate names occur, but shouldn't be flagged (for the reasons described above as to why we sometimes wouldn't consider this a duplicate entry). What formula would best incorporate that additional requirement (to effectively turn off the "Duplicate Names Found" flag, and return to font to its original status). I suspect that once the "Duplicate Names Found" flag returns to "No Duplicate Names Found", the conditional formatting formula currently in place will take care of itself. Thanks much, Dan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I too think I got lost in cyberspace: Formula Help
Hi Toppers!
I wasn't sure how to weave your formula into the one I have below. As a stand alone I couldn't get yours to work. I'll assume I need to weave it into mine to get it to work. By the way, the "x" mark will be placed in Column C. You did accurately reflect how I will use those "x" marks (placing them in Column C next to all occurences of the same name (I have the spreadhseet listed as last name, first name versus first name, last name). Thanks for any further suggestion. Best, Dan "Toppers" wrote: =IF(SUMPRODUCT(--($A$4:$A$3500=$A5),--($B$4:$B$3500=$B5))1,"x","") will place an "x" in any row where duplicate names occur. 1 Ian Black 2 John Smith x 3 John Smith x 4 Dave Brown Is this what you want? "Dan the Man" wrote: I was given a wonderful formula to identify and duplicate name entries on a spreadsheet, and it works like a charm: =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") I also use "conditional formatting" (see below) to change all of the applicable "matches" to RED when they occur =SUMPRODUCT(--($A$4:$A$3500=$A4),--($B$4:$B$3500=$B4))1 Here is my question: Every now and then a duplicate entry is okay (as the duplicate identifies a different location address for an individual, or a different service that we are providing them). The main goal of the original formula above was to identify "accidental" entering of the same persons data more than once. My thought is to develop a third Row (Row C), and place an "x" in any cell where the duplicate names occur, but shouldn't be flagged (for the reasons described above as to why we sometimes wouldn't consider this a duplicate entry). What formula would best incorporate that additional requirement (to effectively turn off the "Duplicate Names Found" flag, and return to font to its original status). I suspect that once the "Duplicate Names Found" flag returns to "No Duplicate Names Found", the conditional formatting formula currently in place will take care of itself. Thanks much, Dan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I too think I got lost in cyberspace: Formula Help
I just realized (I haven't had my coffee yet), that your formula was only
addressing the "conditional formatting" versus the first piece of formula which addresses the flags "No Duplicate Names Found", "Duplicate Names Found". I also wanted to address this, so that "x" marks placed in Row C would turn the flag from "Duplicate Names Found", to "No Duplicate Names Found". I did try your conditional formatting formula, and while it turned all of my fonts back (I have the matches all highlight in RED currently) to black, it did so even in the absence of an "x" placed in Row C. My thought would be that an "x" placed in the match names in Row C would do 2 things: 1) Return the flag to "no duplicate names found"-main formula 2) Return the font color to black-conditional formatting Sorry if I wasn't clearer about that. Any help would be greatly appreciated! Dan "Toppers" wrote: =IF(SUMPRODUCT(--($A$4:$A$3500=$A5),--($B$4:$B$3500=$B5))1,"x","") will place an "x" in any row where duplicate names occur. 1 Ian Black 2 John Smith x 3 John Smith x 4 Dave Brown Is this what you want? "Dan the Man" wrote: I was given a wonderful formula to identify and duplicate name entries on a spreadsheet, and it works like a charm: =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") I also use "conditional formatting" (see below) to change all of the applicable "matches" to RED when they occur =SUMPRODUCT(--($A$4:$A$3500=$A4),--($B$4:$B$3500=$B4))1 Here is my question: Every now and then a duplicate entry is okay (as the duplicate identifies a different location address for an individual, or a different service that we are providing them). The main goal of the original formula above was to identify "accidental" entering of the same persons data more than once. My thought is to develop a third Row (Row C), and place an "x" in any cell where the duplicate names occur, but shouldn't be flagged (for the reasons described above as to why we sometimes wouldn't consider this a duplicate entry). What formula would best incorporate that additional requirement (to effectively turn off the "Duplicate Names Found" flag, and return to font to its original status). I suspect that once the "Duplicate Names Found" flag returns to "No Duplicate Names Found", the conditional formatting formula currently in place will take care of itself. Thanks much, Dan |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I too think I got lost in cyberspace: Formula Help
I thought (on very limited testing earlier!) that the "No duplicates names
found" was a single, overall warning and not for each duplicate: I was obviously wrong! Perhaps a sample w/book would help .. toppers <at NOSPAMjohntopley.fsnet.co.uk Remove NOSPAM "Dan the Man" wrote: I just realized (I haven't had my coffee yet), that your formula was only addressing the "conditional formatting" versus the first piece of formula which addresses the flags "No Duplicate Names Found", "Duplicate Names Found". I also wanted to address this, so that "x" marks placed in Row C would turn the flag from "Duplicate Names Found", to "No Duplicate Names Found". I did try your conditional formatting formula, and while it turned all of my fonts back (I have the matches all highlight in RED currently) to black, it did so even in the absence of an "x" placed in Row C. My thought would be that an "x" placed in the match names in Row C would do 2 things: 1) Return the flag to "no duplicate names found"-main formula 2) Return the font color to black-conditional formatting Sorry if I wasn't clearer about that. Any help would be greatly appreciated! Dan "Toppers" wrote: =IF(SUMPRODUCT(--($A$4:$A$3500=$A5),--($B$4:$B$3500=$B5))1,"x","") will place an "x" in any row where duplicate names occur. 1 Ian Black 2 John Smith x 3 John Smith x 4 Dave Brown Is this what you want? "Dan the Man" wrote: I was given a wonderful formula to identify and duplicate name entries on a spreadsheet, and it works like a charm: =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") I also use "conditional formatting" (see below) to change all of the applicable "matches" to RED when they occur =SUMPRODUCT(--($A$4:$A$3500=$A4),--($B$4:$B$3500=$B4))1 Here is my question: Every now and then a duplicate entry is okay (as the duplicate identifies a different location address for an individual, or a different service that we are providing them). The main goal of the original formula above was to identify "accidental" entering of the same persons data more than once. My thought is to develop a third Row (Row C), and place an "x" in any cell where the duplicate names occur, but shouldn't be flagged (for the reasons described above as to why we sometimes wouldn't consider this a duplicate entry). What formula would best incorporate that additional requirement (to effectively turn off the "Duplicate Names Found" flag, and return to font to its original status). I suspect that once the "Duplicate Names Found" flag returns to "No Duplicate Names Found", the conditional formatting formula currently in place will take care of itself. Thanks much, Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula probles - so lost | New Users to Excel | |||
How do I keep a running total of pounds lost and percentage lost | Excel Discussion (Misc queries) | |||
I'm Lost!!! | New Users to Excel | |||
um... im lost | Excel Worksheet Functions | |||
I'm Lost, Need Help | Excel Worksheet Functions |