extract specific values from list

**mcm1009** · 10-15-2010, 03:33 PM

Hi, I've got a list of numerical values ranging from 1 to 111, in random order. What I need done is to create another list from the original list, but have it exclude any value over 100.

Current List

2
56
33
108
12
18
101
78

Need a new list in a different column that only shows the values under 100. This new list cannot have any blank cells in between the remaining numbers.

2
56
33
12
18
78

I know an advanced filter can do something similar to this, but the issue for me is that the list of original numbers cannot have a header on it. The list I am trying to extract from is actually on the end of a much longer list.

Can this be done with just a formula and without VB? Thanks.

**jeffreybrown** · 10-15-2010, 03:40 PM

How about with advanced filter!

http://www.contextures.com/xladvfilter01.html#ApplyAF

Sorry, just re-read your post...no advanced filter.

**zbor** · 10-15-2010, 03:47 PM

here.. they are already sorted:

=IF(ROWS($A$1:A1)<=COUNTIF(A:A,"<"&100),SMALL(A:A,ROWS($A$1:A1)),"")

**MarvinP** · 10-15-2010, 03:51 PM

Hi mcm1009,

If it "is on the end" simply put 2 blank rows just above your data. Put headers in the lower blank and use Advanced Filters to another sheet. Remove the headers and you have a solutions.

I hope that all makes sense. Advanced filters take a little getting used to but the method will work.

I hope that helps.

**mcm1009** · 10-15-2010, 04:29 PM

Hi, thanks for all the quick replies, but I'm still having trouble. And I'm sure it's because of my explanation. So, here's a spreadsheet that shows what I'm trying to do.
Please reference the attached spreadsheet.

In Cells A3:A113, I have numbers 1 through 111 listed. Next to each is a rand() function. This allows me to randomly list the numbers in Column A by sorting on the random numbers in Column B. I've set up macros so this is done with the push of a button.

When I push the Random button, the numbers in Column A rearrange themselves "randomly" based on the number in Column B.

The new values in Cells A3:A72 are being used in a different spreadsheet. So what I would like to do is to take the remaining numbers ... those in Cells A73:A113 and extract only the numbers that are below the value of 100. I would like to take those remaining numbers and put them in a different column, for instance Column D .. The new list cannot contain blank cells in between the remaining numbers.

I hope this makes a bit more sense. Thanks.

**mcm1009** · 10-15-2010, 04:50 PM

zbor - Thank you very much for your formula. I'm pretty sure I got it to work like it's supposed to and it's exactly what I needed done.. Much appreciated.