PDA

View Full Version : Splitting a list of numbers?



demarc
12-20-2012, 05:59 AM
Hi all,

I am trying to sort some data in this format:

20
30
40
41.7
50
60
70

and I need function that will pull out from 30 - 41.7 and then 41.7-70. Note that the first number needed is not the 20 but the 30 and the last number needed is the last number in the column.

I need to identify what row the odd number out is in (I am doing this using Mod(A1,10)<>0) and then pull out all numbers in the column from that row back up to the 30. Then in a separate list I need from 41.7 until the end of the column of numbers, i.e.

30
40
41.7

and

41.7
50
60
70

Thanks in advance for your help.

Demarc

CodeNinja
12-21-2012, 08:04 AM
Demarc,
Great formula question... Best I can come up with is:

For greater than and equal to 41.7:
=IF(ISERR(INDEX($A1:$A$7,MATCH(41.7,$A$1:$A$7,1))),"",INDEX($A1:$A$7,MATCH(41.7,$A$1:$A$7,1)))
Enter that formula (assuming A1-A7 is your range) and drag down

For Greater than 20 but less than or equal to 41.7:
=IF(INDEX(INDEX(A1:$A$7,8-ROWS(A1:A7)),MATCH(41.7,INDEX(A1:$A$7,8-ROWS(A1:A7)),1)) <30,"",INDEX(INDEX(A1:$A$7,8-ROWS(A1:A7)),MATCH(41.7,INDEX(A1:$A$7,8-ROWS(A1:A7)),1)))

Again enter that formula and drag down.... The second formula has a blank row for the 20... if you want to avoid that, you could include an index to eliminate the less than 30, but I got a bit bored and went the easy route with an if/else to just blank it out.

Hope this helps....

david000
12-21-2012, 07:02 PM
Sub CopyFilter()
Dim LastRow As Long
Dim Rng As Range

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A1").Resize(LastRow - 1)


Rng.AdvancedFilter xlFilterCopy, Range("b1:c2"), Range("d1")

End Sub


If you have a list of numbers in column A and gave the list a title, then copied that exact title to B1 and C1 and in B2 wrote >=30, C2 <=40.7 after you run the code you get the new list in D1 etc.

Bob Phillips
12-22-2012, 03:35 AM
Try these two array formulae

=IFERROR(INDEX($A$1:$A$10,SMALL(IF(($A$1:$A$10<>"")*($A$1:$A$10<=41.7),ROW($A$1:$A$10)),ROW($A1))),"")

=IFERROR(INDEX($A$1:$A$10,SMALL(IF(($A$1:$A$10<>"")*($A$1:$A$10>=41.7),ROW($A$1:$A$10)),ROW($A1))),"")