| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Matching Names and Conditional Formatting!
Glad to hear you got it working.
"Dan the Man" wrote: Got it! Your great JMB. My office manager will love you! Dan "JMB" wrote: You should be able to select A4:B3500 and click Conditional Formatting, Formula is: =SUMPRODUCT(--($A$4:$A$3500=$A4),--($B$4:$B$3500=$B4))1 and then select your format. "Dan the Man" wrote: Thanks JMB, the matching flags worked perfectly. I understand your comments regarding unique identifiers, and I shall mention that to our office manager. Very good thought. I did move the formula around a bit to meet the needs of her spreadsheet (and upon testing it works fine): =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 Duplicates", "Duplicates") I couldn't seem to get the conditional formatting to work exactly as I'd like it however, so if you have an additional suggestion for that. My goal is the cells turn a specifc color (in Columns A & B-last and first name) when a "Match" occurs. My preference would be that ALL matches change color when the match takes place. Smith, Paul L. Doe, Leo C. Jones, Mary R. Smith, Paul L. Doe, Leo C. All the above exact matches in Column A and B should change color (both instances of Paul and both instances of Leo). Thanks again, Dan "Dan the Man" wrote: My Office Manager has a spreadhsheet containing last names, first names, addresses, etc. As the spreadsheet gets longer, she worries about accidently entering identical information (last and first names) without realizing it. What I'd like to do is the following: Put a Flag in Cell A1 which says: "Matches Found", and use conditional formatting to turn any of the matches "red" (last name and first name) for easy identification. If no matches occur, the flag in Cell A1 would say: "No Matches Found". If an identical match occurs in Column A or B Jones, Paul B. Doe, Leo Jones, Paul B. Of course I'd like the formula to be clear enough so a match is not triggered when similar but not EXACT matches occur: Jones, Paul B. Jones, Mary C. Doe, Leo T. Smith, Frank L. Smith, Mary Any suggestions? I know she will be excited if I can give her this information! Dan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matching names | Excel Discussion (Misc queries) | |||
| Sorting and matching rows of names with Socials with master list and eliminating the extra names | Excel Worksheet Functions | |||
| matching values that are not in order with conditional formatting | Excel Worksheet Functions | |||
| using conditional formatting to mark repeated names in list? | Excel Discussion (Misc queries) | |||
| Conditional Format - 2 lists of names to find matching cells. | Excel Worksheet Functions | |||
Hybrid Mode
