Removing in column B when listed in Column A

Hi 

I have a list of text in column A 

aaaa
bbbb
ccccc
dddd
eeee
ffff etc.... 

I also have a list in column B 

zzzz
ccccc
xxxx
aaaa
ffff
gggg etc

What I need to do is remove all the entries in colum a from column B

In the example above I would have in column B left

zzzz
xxxx
gggg


Hope this makes sence and someone can help. 

Thanks
Iai

--
Message posted from http://www.ExcelForum.com

0
12/12/2003 11:00:16 AM
excel 39879 articles. 2 followers. Follow

2 Replies
439 Views

Similar Articles

[PageSpeed] 53

Iain,
I assume your colums A en B are filled with values A1:A10 and B1:B10

Put in C1 the formula : If(ISNA(VLOOKUP(B1,$A$1:$A$10,1,0)),0,1) and copy
down to C10.
Copy and paste special / values column C.
Finally sort column B and C (in the order of C).

After that the values in column B (where in column C the value = 0) are the
values you want to have ( i.e. values not also mentioned in column A).

-- 
Regards,
Auk  Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"IainG" <IainG.ybmia@excelforum-nospam.com> wrote in message
news:IainG.ybmia@excelforum-nospam.com...
> Hi
>
> I have a list of text in column A
>
> aaaa
> bbbb
> ccccc
> dddd
> eeee
> ffff etc....
>
> I also have a list in column B
>
> zzzz
> ccccc
> xxxx
> aaaa
> ffff
> gggg etc
>
> What I need to do is remove all the entries in colum a from column B
>
> In the example above I would have in column B left
>
> zzzz
> xxxx
> gggg
>
>
> Hope this makes sence and someone can help.
>
> Thanks
> Iain
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
12/12/2003 11:43:42 AM
try this macro
Sub culllist()
x = Cells(Rows.Count, "b").End(xlUp).Row
On Error Resume Next
For Each c In Range("b1:b" & x)
If Not Columns(1).Find(c) Is Nothing _
Then c.Clear
Next
Range("B1:B" & x).Sort Key1:=Range("B1"), Order1:=xlAscending
End Sub

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"IainG" <IainG.ybmia@excelforum-nospam.com> wrote in message
news:IainG.ybmia@excelforum-nospam.com...
> Hi
>
> I have a list of text in column A
>
> aaaa
> bbbb
> ccccc
> dddd
> eeee
> ffff etc....
>
> I also have a list in column B
>
> zzzz
> ccccc
> xxxx
> aaaa
> ffff
> gggg etc
>
> What I need to do is remove all the entries in colum a from column B
>
> In the example above I would have in column B left
>
> zzzz
> xxxx
> gggg
>
>
> Hope this makes sence and someone can help.
>
> Thanks
> Iain
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Don
12/12/2003 12:45:09 PM
Reply:

Similar Artilces:

cant view columns ie A B C D..
This is driving me nuts but I am sure it's an easy setting, I cant see to view the top of my spreadsheet to see the excel column names ie A C Sorry for being thic -- beatrice2 ----------------------------------------------------------------------- beatrice25's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3450 View this thread: http://www.excelforum.com/showthread.php?threadid=55476 Hi, Can you see the row numbers? If not then try Tools -> Options -> View and under Windows Options maybe you have to tjeck-mark the Row & Column Headers. Regar...

Limits on number of columns
I have a spreadsheat which will have approximately 200 columns when all is said and done. This will be used as a data doc for a merge to be performed in Word. Are there any known problems that I should be aware of in this situation? Thanks for any help or hints you may provide. The following MSKB article explains the limits for various data sources. For Excel you could use all 256 columns: HOW TO: Design and Set Up a Mail Merge Address List in Word 2002 http://support.microsoft.com/?kbid=294688#9 Milli wrote: > I have a spreadsheat which will have approximately 200 > colu...

scaling of columns: same maximum height despite different ranges
When plotting several variables on a single chart (e.g. each category on the x-axis has three clustered columns), and the value ranges of the three variables are very different, is it possible to have Excel scale each variable such that they are all represented by columns of comparable height in the graph (i.e. the maximum value of each is represented by a column of the same height)? To illustrate this rather abstract question, I have created this example chart (in Excel, then I added the values in a photo editor), in which I compare three variables (CPU rating, GPU rating and weight) ...

distilling a column of text entries
I am looking for a formula, as opposed to a VBA solution, where I have a column of text entries, but with empty cells interspersed here and there from top to bottom sort of like this for, say, cells A1:A6: House (empty cell) Car (empty cell) (empty cell) Dog ....like that with empty cells between each of the cells holding text. Is there a way, via a formula to distill the above column so that it looks like this: House Car Dog in another column or series of cells on the spreadsheet? My thanks in advance if anyone can point me in the direction of a solution! Brad Insert a row at ...

Column automatically adjusts width to fit text entries
Not able to get an answer to this in General Questions Forum - so trying here: Is it possible to have Excel automatically extend the width of a column as text is entered. That is, instead of the text spilling over into the adjoining cell, the column automatically increases in width as characters are added. Answer needed for one of our teachers ASAP - college instructor claims it can be done - I am unable to find it anywhere. It cannot be done. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "redsanders"...

listing last & first names
i imported an access database, everything seemed to go well. but it wont list the last & first names at all. not even under "full name". im using the "detailed view" under "full name, its blank. do i delete it? i couldnt find a way to do that. thanks, francine ...

sort by date column
We have a worksheet that contains a colum with the date in it like 12/26/2007. We want to be able to sort by the month but anytime we try to sort it sorts by the year. Is there an easy way to do what we are trying to do? Thanks, CEV Insert a column next to this one (I assume the data is in column A). Use this formula: =MONTH(A1) and copy down Sort both columns, using the new column as sort key "CEV" <chadv@advancebkg.com> wrote in message news:fku5lh0mig@enews4.newsguy.com... | We have a worksheet that contains a colum with the date in it like | 12/26/2007. We want t...

Unhide/hide based on drop down list
I have a cell with a drop down list (using data validation, list) and it has 3 values in it: High, Medium and Low. If the user chooses "Low", the following 3 rows remain hidden. If the user chooses "Medium" or High", the following 3 rows need to unhide so they can explain their reasoning. I am new to macros, but found this macro that worked if I had only 2 choices on the list: Sub HideRows() Dim Rng As Range Set Rng = Sheets("Sheet1").Range("A3") If Rng.Value = "High" Then Rows("4:6"...

List of Codes and Display Names for Codes #2
thanks that seems to be working thank you .... :) -- hoganc ------------------------------------------------------------------------ hoganc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14980 View this thread: http://www.excelforum.com/showthread.php?threadid=266031 ...

Allow other values other than ones in Value List
Is there a way i can be permitted to add values to a drop down value list of a cell? If the value is not in the list at present i get a "The value you entered is not valid" error. I do not want to add that particular value to the list each time as it may never be used again, and the list would grow beyond the never never.... Any idea's? Corey, if this is from data validation uncheck show error alert on the error alert tab in data validation -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from...

after Not in List Event
Hello: I have a "Not in List Event" in a form, Form A, that adds data to my "look-up" table. After this data is added, I want to open another form, Form B, but only if a "Not in List Event" occurs. In other words, "How do I open a form in, perhaps, an 'After update event' with the condition that a "Not in list event" has previously occured? I ask because I want to use the new value in "Form A" as a reference to "Form B". I appreciate any help! -Gabriel M. After you add the new value to the lookup table in For...

Multiple Tables in Query not Listing Data.
I cannot seem to get this correct. I am trying to run a query on two databases one of which is linked to the other. I am trying to get the query to display a 0 for the total defects counted from one table based on the date and time from another table. So far when I run the query I get only the counts from the values that are not null. Here is the SQL Statement: SELECT Pro_Total_by_Cell.Cell, Pro_Total_by_Cell.Shift, Pro_Total_by_Cell.Time, Pro_Total_by_Cell.ProTotal, Pro_Total_by_Cell.Time1, Hourly_Defects_by_Cell3.CountofFC, Control_Limit_by_Cell.UCL, IIf(IsNull([Counto...

Sharing calendar and task list
Hi, Usin Outlook with exchange backend, How can I share calendar or tasklist with everyone else, so they can have it in their folder list and see/modify it? Thanks, Alan A.M wrote: > Hi, > > Usin Outlook with exchange backend, > How can I share calendar or tasklist with everyone else, so they can > have it in their folder list and see/modify it? > > > Thanks, > Alan See my reply in microsoft.public.exchange.admin. Please don't multipost - if you need to post to multiple groups, it's best to crosspost instead, by posting a single message to a handful of...

Sort Protected Sheet with Header Columns
I'm trying to get a spreadsheet to sort by clicking on the Header cell. I found the following code and it works when the sheet is unprotected: Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Application.EnableEvents = False If Not Intersect(ActiveCell, _ Range("A13:AT13")) Is Nothing Then 'Range with column headings ActiveCell.CurrentRegion.Sort Key1:=ActiveCell, Header:=xlYes End If Application.EnableEvents = True End Sub But, when I protect the sheet and try to run the following code, nothing happens. Sub Worksheet_SelectionCh...

Original Journal Entries window should only list GL entries.
IF GL journal entries that originate in other modules can not be corrected in the Correct Journal Entry window, then they should not appear in the Original Journal Entries lookup list. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businessso...

outlook 2003 user cannot see members of distribution list
I have an outlook 2003 user who, when clicking on a distribution list or an individual name in a received email; the dialog box appears displaying the properties of the person or group, but there is nothing showing up in the "member of" tab. this is the only user I have affected. any help would be appreciated. Normally when clicking on a user of group listed in the "To" of an email the properties can be viewed showing the groups the individual is a member of, etc. Have you tried creating a new mail profile for this user? --� Milly Staples [MVP - Outlook] Post ...

Excel column widths
I created an excel sheet to track information. It's rather large. The sheet is being sent once a week to a client, and when they get it, the columns resize and some of the dates turn into #####. Why is that? What can I do to stop it? The data is fine on my computer, why is it showing larger on other people's systems? I have all the columns "autofit" so that I can fit more data into a page, so it's a tight fit. Will I just have to enlarge the columns? one thing that sometimes happens is that they don't have the same sets of fonts on their machines. Check that...

Max across columns?
I need a function that will return the max across a set of columns, instead of rows like the summary function MAX appears to do. So, I need a function that would work similarly to: (select max(5,2,9,3) from test) and return 9 Thanks -- Regards, Dave See MaxOfList() here: http://allenbrowne.com/func-09.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "David Billigmeier" <dtbill21@hotmail.com> wrote in message news:C0572061-DF28-4415-B7E...

Drop downs list on every line for a specific column?
I am entering data and would like to have a dropdown one a specifi column on every line. I have column's "ID" , "Manufacturer", "Model" I want to inport or have a list of all the Manufacturers and everytim I go on a new line I want to have the drop down so I can speed up m entering and avoid spelling error ect. Can anyone help? Kind Regards, Can-A-Fun +---------------------------------------------------------------- | Attachment filename: sheet.jpg |Download attachment: http://www.excelforum.com/attachment.php?...

How do I subtract two columns from the same total?
Hi, How do I subtract two columns with two different conditions from the same total? A B C D E Article 1 200 12 -45 Article 2 350 12 50 Column E should always contain B - C, but column D should only be subtracted if it is less than 0. Thanks in advance! Hi, Here's what I would do (there's probably a more elegant way). In Column E: =if(D2<0,B2-C2-D2,B2-C2) Hope that helps. Melinda "Lollycat" wrote: > Hi, > > How do I subtract two columns with two different c...

Merging multiple spreadsheets by matching columns
As an Access Newbie I have been at this for two days. I have a Quickbooks inventory speadsheet and Multiple vender price spreadsheets. I would like to merge the "cost" column from the Multiple vendor spreadsheets onto the Quickbooks Inventory speadsheet using "part number" column as the matching field. It seems like a simple process in my head, but be damn if I can figure out how to translate those commands in Access '07 Is the a Template available that would help? ...

Sorting multiple columns as 1 list alphabetically?
Can anyone help with a solution for something i'm trying to achieve in excel? I have a long list of names that spread many cells down as well as several columns across and i'm trying to sort the entire list into alphabetical order. I know I can sort one column at a time but that is not what i want to achieve. I need to be able to select all of the data and sort it into alphabetical order (with the a's starting in the first cell of the first column and the z's finishing in the last cell of the last column). What I really need to do is be able to link columns so that the data i...

Text to Columns Function Problem
When I use the text to columns function in Excel the column that the split data is dripped into shits up one row. Is ther a way to avoid this? I bet you misspelled "dropped" and "shifts". <vbg>. And I bet you select the whole column and the column has empty cells at the top. You can either be more specific with your selection (A3:a9999 instead of all of column A) or maybe just put some temporary values in those blank cells at the top. Actually, the only way I could reproduce this is when the usedrange of the sheet didn't include the top row(s). If I put a...

cluster column chart with data point labels
I'm trying to create a cluster column that has a label above each data point. The columns are the total number of an event that happened and then I need a label that shows what % of a goal those number of events represent. There is no relationship between the data and the % label. Jan (cluster 1) A: 34 label: 98% B: 52 label: 102% C: 23 label: 96% Feb (cluster 2) A: 36 label: 102% B: 56 label 110% C: 21 label 92% Can someone help me design this chart? Thanks. John Try one of these utilities which add labels from a worksheet range to a data series. Both are free, e...

distribution list 01-17-10
how can I restore a distribution list in contactsthat I inadverantly deleted Move it back from your Deleted Items Folder. -- Russ Valentine "mark" <mark@discussions.microsoft.com> wrote in message news:BD87682C-D228-4C3E-82CF-5A46BCDC1337@microsoft.com... > how can I restore a distribution list in contactsthat I inadverantly > deleted ...