Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A change in my "Duplicate Names" formula please?
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 | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
How to change "automax" to "autosum"? (probablyl wrong terminology) | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
=IF(D13="PAID","YES","NO") Can I change fonts colour | Excel Discussion (Misc queries) |