Filter 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?


0
mmaan (3)
11/21/2006 2:17:01 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
562 Views

Similar Articles

[PageSpeed] 52

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


"mmaan" wrote:

> 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?
> 
> 
0
11/21/2006 2:41:02 PM
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

"Ron Coderre" wrote:

> 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>
> 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
> 
> 
> "mmaan" wrote:
> 
> > 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?
> > 
> > 
0
mmaan (3)
11/21/2006 3:45:02 PM
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 

"mmaan" wrote:

> 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
> 
> "Ron Coderre" wrote:
> 
> > 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>
> > 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
> > 
> > 
> > "mmaan" wrote:
> > 
> > > 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?
> > > 
> > > 
0
mmaan (3)
11/21/2006 4:00:01 PM
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)


"mmaan" wrote:

> 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?
> 
> 
0
11/21/2006 9:01:02 PM
Reply:

Similar Artilces:

ClistCtrl > How to auto-adjust columns size ?
Hi all, I have a CListCtrl in report view, two columns, hidden headerCtrl. I populate it with some items (less than 10). I'd like to programmatically auto-adjust the columns width according to their content, exactly like pressing Ctrl+'+' on a ListCtrl. Any idea ? Thanks, Dansk "Dansk" <dansk@laouilest.com> wrote in message news:e1x4VF9wHHA.276@TK2MSFTNGP06.phx.gbl... > Hi all, > > I have a CListCtrl in report view, two columns, hidden headerCtrl. > I populate it with some items (less than 10). > > I'd like to programmatically auto-adjus...

CRM 4.0 Report Deployment
Hi When I deploy a custom report (through Web UI) and run it, no data is displayed. The report header, footer, etc, display, but there is no data. To simplify the problem I created a very simple report with no parameters, which selects all from filteredsystemuser, and the problem was the same. This is in a test VPC, the whole environment is contained in the VPC. I am developing/publishing/viewing as the CRM administrator. The steps I took were: - Created a new report in Business Intelligence Development Studio using Wizard (installed in VPC) - Created a new DataSet called dsTest with a new D...

Spam filter in O2k?
Little desktop, W2k sp4, Outlook 2000, DSL, etc. Due to the apparent dereliction of my ISP (ATT), I need to apply a spam filter to my Email client (O2k). Would like to have Email containing the text string "Replica Watches" diverted to a junk folder or "Deleted Items". This is practical? Thx, Puddin' "Take Yo' Hand Out My Pocket (I Ain't Got Nothing What Belongs To You)!" - Rice Miller, who probably never even _heard_ of Paulson, Bernanke, etc Did you try creating a rule? If not, you'll need a spam filter. See http://www.slipstick.co...

Can I make a database in Word with Excel Data
I know this may be a "DUH" question, but... I am making a list of area dentists that I have copied and pasted of of the Dex website into Excel. I am wondering if I can take this inf and make a database in Word for printing address labels. Does anyon know if this can be done, and HOW?:confused -- Myn77 ----------------------------------------------------------------------- Myn777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2958 View this thread: http://www.excelforum.com/showthread.php?threadid=49283 For help on Word mail merge using Excel as the...

Access ODBC problem: Excel cannot get float columns
I need some help I'm trying to get Access external data from an Excel workbook, using the MS Query feature. Everything is ok except when I try to fetch some tables that ODBC refuses to get data from. The error message tells that the MS Access ODBC driver doesn't allow some columns to be transmited because of its number of characters. The most strange thing is that I can see the data from MS Query correctly, but I caannot get it back from Excel. After some tries, I thing it occurs only with real typed columns. Can anybody help me? I use Windows XP Home + Office 2000 spanish versio...

How can I Enable a Check Box based on another fields value?
Hi There, I have a form with a disabled check box. I need to enable it when a certain value ("approved") is selected from a combo box. When I am in Form Design View and I have the Check Box selected the Conditional Formatting menu item on the Format menu is greyed out. I am using Access 2003. Can you tell me what I need to do to make this work? Many thanks, David As you've discovered, Conditional Formatting isn't availabe to checkboxes. Try this: Private Sub Form_Current() If Me.YourComboBox = "Approved" Then YourCheckBox.Enabled = True Else YourChe...

data validation
Is it possible to create a similar function like data validation, from which I can select multiple options? (Background: I need a list of many names, from which I case by case can select). Not really. Maybe you could use a listbox. I added a listbox from the Forms toolbar to a worksheet. I called it "List Box 1". I rightclicked on that listbox and chose Format Control. On the Control tab, I chose Selection Type of Multi. I also pointed at the range that held the values that go in that listbox. Then I added a button from the Forms toolbar (I click the button when I'm d...

Looking for someone who can integrate CRM with GP using SCRIBE Integration tool
Hi, Is anyone out there in Sydney area who would like to help us in doing integration between CRM 4 and GP 10 through SCRIBE integration tool for a fee? We are looking for someone who has already done integration between these two applications using SCRIBE. You will have to do bit of customisations in the SCRIBE standard templates. If anyone available, you can contact me through my email badri1203@gmail.com Badri ...

Updateing data when closing forms
I am using MS Access 2000 I have a table that has several required fields. I developed a form to add records to the table. In addition to the table fields, the form has a form-close button constructed with the button wizard. When I enter a record that does not have the required information and close the form with the “Close Window” (X) button on the tool bar, the appropriate error message appears telling me that there is missing data. (Just what I want) When I use the close button on the form, the form closes with no message and without adding the record. How do I give the button on the f...

Graph Data Values
I am trying to create a pick and mix graph that shows forecast spen against actual spend by a selected business area. I somehow want to create a graph on the fly based on the selections fo example: If I choose company one and want to see the contracted data I want th graph to pick up the forcasted contracted Labour, Passthru and Othe and show the combined contracted actuals; See attached; and if i then choose company 2 then graph will pick up the releven ranges? HELP!! Attachment filename: help.zip Download attachment: http://www.excelforum.com/attachmen...

How to recursively clear all the filters applied to a folder
Hello, In Outlook 2002 I would like to recursively clear all applied filters. I remember applying a filter to a folder but it seems Outlook applied the filters to all folders. Now the only solution I found to clear all filters is to clear them one by one by selecting View > Current View > Customize... > Filter... > Clear All. Isn't there a clear all feature for all views ? JM. You could reset all views to their defaults and delete all custom views = by starting Outlook with the /cleanviews switch. But there's no magic = method for keeping all view settings except the f...

one page in booklet format
How do I tell Publisher 2000 to print just page one and 4 and then pause while i reinsert the paper to print pages 2 and 3? duff wrote: > How do I tell Publisher 2000 to print just page one and 4 and then > pause while i reinsert the paper to print pages 2 and 3? File > Print > Pages 1 to 1 > OK > Print as separate booklet = No. Flip pages. File > Print > Pages 2 to 2 > OK > Print as separate booklet = No. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

How do I import data from a SECURED website into Excel?
I can import data from a regular web page directly into Excel spreadsheet, but when I tried importing them from a secured site I encountered problems. Does anyone have any suggestions? ...

Passing Values from One Form to Another Including a Combo Box
Hi, hope someone can help with passing two values from one form to another by way of a command button. I have spent a week on various code taken from this site, but still no luck. Please ... someone help!! The form I am passing values from is called PATIENT HISTORY-Form. On this form, I need to pass a date from a field called DateSFESigned and I also need to pass information collected from a Combo box, Combo91. The command button is called Command119. The form that the values are being passed to is called Personal Habits- Form. Thank you in advance for any help on this matter. Maurita ...

Lookup Wizard changing Data Type
I am trying to make Lookups from Table Field to Table Field and generally it seems OK. However the LookupWizard is changing the Data Type from Text to Number (I guess it's looking at the ID?) Data picked from Combo Box 'looks' OK in Table view A Query view is asking for a number but displays the Text field value when a valid ID number is added. Viewing Results in FrontPage Database Wizard, and using hand coded .asp querie, it's showing the ID field value. I can see why... Any ideas? Merci. Yes, the wizard isn't the problem it's the use of Lookup Fields in ...

retrieving data from Great Plains via eConnect
Hi, I've been working with eConnect for the last two weeks and have had success with importing a variety of transactions into Great Plains. However, I'd like to query Great Plains on demand and have data returned to me. For example, given a certain field value (like customer name = "Smith"), I'd like to have all customer records returned that match that specified value. Is there any way to do this easily with eConnect? I'm not so interested in the Transaction Requester Service because that seems to be focused more on automatically returning records that have...

dynamically filtered pivot table
I'm trying to make a pivot table that will dynamically hide a section o its contents based on a boolean operator the user can set. Becaus this boolean is used in several places, I don't want to require th user to manually set the visibility parameters for the pivot table. It seems like the only way to do this effectively is to have th booleans set by a button, and have the button not only toggle th boolean, but also change the visibility in the pivot table. here's the code i've tried: Sub ToggleButton1_Click() If ToggleButton1.Caption = "Include" Then 'C...

Copy Distribution List from One Computer to Another
Hi Folks - I have seen various methods for accomplishing this, but none seem to help me. Our company uses Outlook 2003 in a NON Exchange Server environment. I have seen a method for copying distribution lists within a Exchange server environment, but it does not work in our environment. Any ideas? Thanks. Michael Michael <info@homekeyinc.com> wrote: > Hi Folks - I have seen various methods for accomplishing this, but > none seem to help me. Our company uses Outlook 2003 in a NON Exchange > Server environment. I have seen a method for copying distribution > lists with...

How do I link data to a chart from multiple worksheets?
I've created a graph that I want to show data from multiple sheets in the workbook. How do I do that? There are limits to how you can combine data from different sheets. This article describes them. http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Donna" <Donna@discussions.microsoft.com> wrote in message news:7DF7D186-9426-4B54-A322-FE485FD20529@microsoft.com... > I've created a graph that I want to...

How do I check my email on another computer?
"LalaBobo" <LalaBobo@discussions.microsoft.com> wrote in message news:EADEB55F-1548-4A75-92FF-5D119532C946@microsoft.com... Go to it? <g> -- John Blessing http://www.LbeHelpdesk.com - Help Desk software priced to suit all businesses http://www.room-booking-software.com - Schedule rooms & equipment bookings for your meeting/class over the web. http://www.lbetoolbox.com - Remove Duplicates from MS Outlook, find/replace, send newsletters Is it ISP mail (your ISP will have details) or a web based service like Yahoo or Hotmail? -- Mike Hall MS-MVP Windows ...

Extracting data from one sheet to another
I have a speadsheet with 390 rows and 80 columns. For each column, I wish to copy a range of cells and place them in rows so I can create a separate table for each item represented by the column. For instance, I would like to take the data from the range B10:B15 and place that into a range A1:E1, and then data from range C10:C15 and place into a range G1:K1, D10:D15 into I1:M1 and then B16:B21 into A2:E2, C16:C21 into G2:K2, etc, etc, etc I could do this manually, but it could take a while. I was wondering if there was an easier way to do this. Any help would be greatly appreciated. Thanks...

stationary column on a spreadsheet
I have a spreadsheet that i want to make the first column not move when i scroll to the right. And one teach a dummy. Select cell B1 Choose Window > Freeze Panes wc4ew wrote: > I have a spreadsheet that i want to make the first column not move when i > scroll to the right. And one teach a dummy. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Collaboration Data Objects (CDO) ?
Is this technology still current or is there something better to use? Can I use CDO to access Outlook on a non server operating system? Thanks Tom ...

Error importing Quicksell 2000 data
I've followed the instructions exactly as indicated in the help file. When I reach the final step where the actual import is to take place I get the error: Invalid Object Name 'VersionHistory' SELECT TOP 1 Version FROM VersionHistory ORDER BY ID DESC It appears that it's having a problem with the Access database when it tries to retrieve the latest version number of Quicksell 2000 (which is 3.01 on our system) I've used the QS2000 doctor to compact and repair the database, but the problem still exists. Any suggestions are appreciated. If you can ZIP the datab...

References omit formatting and return cell address
In two cases of references between worksheets, the formatting from the original cell does not appear in the cell that it is referenced to. Case 1: Worksheet 1, A1 contains a currency formatted number - $2,000 Worksheet 2, A1 references the Workhseet 1, A1 cell using the = sign, yet it returns 2000 (unless I manually reformat the Workksheet 2 cell to Currency Case 2: Worksheet 3, A1 contains an apartment # - e.g. 4 Worksheet 4, A1 references this cell but returns the cell address - Worksheet2,!A1' - rather than the number 4. I tried different formats for the number 4,...