Excel is there a way to eliminate search numbers from a block

TasBob's avatar - 4NJ9EUA

Excel is there a way to eliminate search numbers from a block of numbers?  A column of 1000 numbers?

Block  Search                                     Search for 018 025  865   I like to search 50 numbers or more

                        Then it would look like this

015                    015               
016                    016
017                    017
018    018               
019                    019   
025    025
027                    027
029                    029   

Both Block And Search are eliminated , This is used for my eliminating system.


You can use the countif function to isolate numbers.

I would name two Ranges for the addresses.

Call your long list of numbers something like "List".  Call your search number something like "Search"

Place this formula in the column or columns adjacent to each cell in "List"

Use the formula: If(countif(Search,[adjacent cell in "list"})=1,[adjacent cell],"")

IF your "List" is in Cells A2 to A100 and Your "Search" was in Cells B1 to D1

You would place this formula in cells B2 to B100 and get your list.

You could then name a Range called "Results" Cells B1 to B100 

In a separate Column you could use the small function to condense the list in numerical order.

Ex  Small(Output,1)  Small(output,2) and so forth to get the sorted results.

You might want to use the ifferror function to eliminate null results

Example Formula would be If(iserror(Small(Output,5),"",Small(Outlet,5)

Hope that helps

Todd's avatar - Cylon 200.jpg

<Moved to Lottery Discussion forum>

Please post in the appropriate forum ... thank you.

TasBob's avatar - 4NJ9EUA
In response to AllenB

Tks AllenB

Was not able to get it working , maybe some else could give another idea.  TKS


I did a quick worksheet that I think does what you need.  In My previous response, I did not produce the correct list because my formula was backwards.  Instead of excluding the numbers from Search, I included them.  Sorry it was backwards.

If you PM me with your email contact I will send you the file

TopEnd of thread (1 page)

Welcome Guest

Your last visit: Sun, Nov 29, 2020, 4:35 am

Log In

Log InCancel

Forgot your username?

Forgot your password?