filtering consecutive numbers into diff columns or cells

**fmri** · 06-27-2013, 06:02 AM

Hi all,

I have a big data that consists of 4500 elements. this represents 75 events. I also have a column that has (specific ) consecutive numbers for each event. So once the event is finished a new number begins that is not consecutive with the previous one but it is for the next one. For a simple example:
1
2
3
5
6
7
10
11
12
From 1 to 3 is event 1; from 5 to 7 is event 2; from 10 to 12 is event 3

What I am trying to do is to separate these events into different sheets or cells so I know their onset time and their ends.

I attached a real example and colored the first five events.

Book2.xlsx

I am really struggling with this data.

Thanks

AS

**XOR LX** · 06-27-2013, 06:57 AM

Hi,

Use a helper column in column F (call it e.g. "Block"). In F2, put the value 1. In F3, type this formula and copy down to the bottom:

=IF(E3=E2+1,F2,F2+1)

You should then have values from 1 to 75 in this column which correspond to your desired blocks. You can then filter for each.

Regards

**fmri** · 06-27-2013, 07:11 AM

Hi XOR LS,

it is not working. I do not know why but it gives me 0 0 0 0 for each cells in F. This may help but the consecutive numbers do not start from 1. I have 4 sheets ( some starts from 20, 30, or 15 .. ).

For example the first three events in E are Ev1 = [ 20:1:72] ; Ev2 = [ 119:1:271 ]; EV3 = [ 311:1:370 ]

**XOR LX** · 06-27-2013, 08:03 AM

I don't know about your real sheet, but it certainly works in the sample you posted in this thread. If you need it adapting/amending then you'll have to let me know a bit more about your sheet and in what way it differs from that which you posted.

Regards