Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove selected data from a list
i have a list of sequential numbers eg 1 to 100.
I have a second list of numbers eg 5,6,23,38,90 I want to remove the numbers in the second list from the first list. Leaving me a list of numbers 1 to 100 minus the 5 numbers in the second list. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove selected data from a list
On 23 mar, 22:40, kingie wrote:
i have a list of sequential numbers eg 1 to 100. I have a second list of numbers eg 5,6,23,38,90 I want to remove the numbers in the second list from the first list. Leaving me a list of numbers 1 to 100 minus the 5 numbers in the second list. Hello, I would suggest using the vlookup formula in the column next to the "1 to 100" column. Let's say that the "1 to 100" column is column A1 to A100 and the "5 numbers" column is in "D1 to D5". The formula in range B1 would look like : =VLOOKUP(A1;$D$1:$D5;false) . This formula must be copied in column B from 1 to 100. This will return the value if it's a common value or an error #N/A if not. In a 2nd step you can also use the ISERROR function. Hope this helps |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove selected data from a list
Excel 2007 Table
Filter out selected numbers Advanced Filter http://c0718892.cdn.cloudfiles.racks...03_23_10a.xlsx http://www.mediafire.com/file/zzoygidnmzg/03_23_10a.pdf |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove selected data from a list
This macro will do what you want. I assumed your 100 numbers are in Column
A, starting in A2, and your search numbers are in Column B starting in B2. Post back if you need more. HTH Otto Sub RemoveSome() Dim rColA As Range Dim rColB As Range Dim i As Range Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp)) For Each i In rColB If Not rColA.Find(What:=i, LookAt:=xlWhole) Is Nothing Then rColA.Find(What:=i, LookAt:=xlWhole).Delete Shift:=xlUp End If Next i End Sub "kingie" wrote in message ... i have a list of sequential numbers eg 1 to 100. I have a second list of numbers eg 5,6,23,38,90 I want to remove the numbers in the second list from the first list. Leaving me a list of numbers 1 to 100 minus the 5 numbers in the second list. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create separate list of only selected data | Excel Discussion (Misc queries) | |||
How to verify selected data from drop-down list? | Excel Worksheet Functions | |||
A validated List which link to selected cells according to what is selected on the list | Excel Worksheet Functions | |||
data validation list selected from a range | Excel Discussion (Misc queries) | |||
return a data list depending on a value selected within a drop dow | Excel Discussion (Misc queries) |