 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2006Filter data from one collum by looking up from another column
Hi, I am stuck on the following problem: I want to exclude names (or give them another color) from 1 column if they are also in another column. To illustrate this, the result from the following data: A B 1 John Peter 2 Sue Floyd 3 Peter Dan 4 Mike Chris would have to be: John Sue Mike Or in this case I could use a conditional formatting to paint A3 red. How do I do this? Try something like this: Using your posted example: Select A1:A4, with A1 as the active cell From the Excel main menu: <format><conditional formatting> Formula is: =COUNTIF($A$1:$B1,A1)>1 Click the [Format...] button and choose your settings Click the [OK] buttons Is that something you can work with? *********** Regards, Ron XL2002, WinXP > Hi, > I am stuck on the following problem: [quoted text clipped - 17 lines] > > How do I do this? Hi Ron, If I try it with my example data it works (if I replace the comma by a ";"), but if I use it on my actual data (830 rows), it does not work. It makes no sense to me. Best regards, Maarten > Try something like this: > > Using your posted example: > > Select A1:A4, Ron, If I with A1 as the active cell > From the Excel main menu: > <format><conditional formatting> [quoted text clipped - 31 lines] > > > > How do I do this? Hi Ron, I tried it once again, with a clean worksheet and now it works! I am not sure what went wrong just now. But never mind that. Thank you for your help! Best regards, Maarten > Hi Ron, > [quoted text clipped - 50 lines] > > > > > > How do I do this? If you want to exclude names, then try this: =IF(ISERR(SMALL(IF(COUNTIF($A$1:$B$4,$A$1:$A$4)=1,ROW(INDIRECT("1:"&ROWS($B$1:$B$4)))),ROWS($1:1))),"",INDEX($A$1:$A$4,SMALL(IF(COUNTIF($A$1:$B$4,$A$1:$A$4)=1,ROW(INDIRECT("1:"&ROWS($B$1:$B$4)))),ROWS($1:1)))) ctrl+shift+enter (not just enter) > Hi, > I am stuck on the following problem: [quoted text clipped - 17 lines] > > How do I do this?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.