Splitting a list of numbers?

[demarc]

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. The odd number is not always between the 40-50 range it could be between 50-60 or 60-70 so the formula has to be dynamic.

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

[Pete_UK]

Will you always omit the first number in the list?

Will the list always by that long, or could it be of variable length?

Pete

[Pete_UK]

The attached file shows how it can be done for your example list.

Hope this helps.

Pete

[demarc]

Hi Pete,

The list starts at 0 however I am only interested in extracting from 30. The list continues to 120 (by 10's) and is split somewhere between 60 and 90 by a number with a decimal. I guess I should have mentioned that sometimes the number I would like to split the list at is a repeat number, i.e.

30
40
50
60
60
70.....

in this instance I need to split between the two 60's. I am returning the two seperate lists in two different columns so I am trying to work out a formula for the upper portion of the list and lower, they should be reasonably similar. I can find the row number where the split needs to take place but I am having trouble saying 'from 30 take all the numbers down to this row only' and 'from this row take all the numbers below to the 120' if that makes any sense.

Thanks,
Demarc

[Pete_UK]

Might be a good idea for you to post an example workbook, maybe with different example lists in different sheets, showing before and after. The FAQ describes how to attach a file.

If you do it in the next 45 mins I'll take a look at it, but after that I'lll be in bed, so someone else might pick it up.

Pete

[demarc]

Thanks Pete,

Just got you reply, looks like it will do the job!

Cheers,
Demarc

[Pete_UK]

Not quite, as the formula in B1 is looking just for fractional values. However, if you are prepared to just manually enter the number that is the odd one into cell B1, then it should work as you described in post #4. For duplicate numbers, the second list will always include the number twice (i.e. it will split at the first duplicate)

Hope this helps.

Pete

[demarc]

Not sure if this worked but have to run. If id didn't I will upload with further explination in a few hours.

Cheers,
Demarc

[Pete_UK]

I've created a new sheet in the attached file with the necessary formulae in to generate your sub-lists.

Hope this helps.

Pete

[demarc]

Thanks Pete,

I spent days trying to sort this out.

Very much appreciated
Demarc