How to isolate key rows in a very long table?

R

RasmusA

New Member
Joined
Oct 26, 2017
Messages
2
Dear MrExcel community,

I have a long list of data (10s of thousands of rows). Its behavioural data on a fish moving in and out of different 3 different zones (DECR, OFF, and INCR) in an aquarium. I need to separate/isolate the first row every time the fish move from one zone to another (bold in the raw data table). Right now, my only option is to process the data manually (I have no programming skills), but it will literally take 100s of hours. A colleague of mine mentioned this place and suggested I ask the community in here is there is a smarter way of doing it in excel?

I have provided an example below.

Raw data

<tbody>
[TD="align: center"]
[/TD]
[TD="align: center"] Time
 [/TD]
[TD="align: center"] INCR (out)
 [/TD]
[TD="align: center"] DECR (out)
 [/TD]
[TD="align: center"] Zone
 [/TD]
[TD="align: center"] Time
 [/TD]

[TD="align: center"] 1
 [/TD]
[TD="align: center"] 6/14/2017/3:10:24
 [/TD]
[TD="align: center"] 77,54
 [/TD]
[TD="align: center"] 50,29
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:10:55
 [/TD]

[TD="align: center"] 2
 [/TD]
[TD="align: center"] 6/14/2017/3:10:25
 [/TD]
[TD="align: center"] 77,53
 [/TD]
[TD="align: center"] 50,26
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:11:16
 [/TD]

[TD="align: center"] 3
 [/TD]
[TD="align: center"] 6/14/2017/3:10:26
 [/TD]
[TD="align: center"] 77,54
 [/TD]
[TD="align: center"] 50,25
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:11:17
 [/TD]

[TD="align: center"] 4
 [/TD]
[TD="align: center"] 6/14/2017/3:10:27
 [/TD]
[TD="align: center"] 77,53
 [/TD]
[TD="align: center"] 50,24
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:11:18
 [/TD]

[TD="align: center"] 5
 [/TD]
[TD="align: center"] 6/14/2017/3:10:28
 [/TD]
[TD="align: center"] 77,53
 [/TD]
[TD="align: center"] 50,23
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:11:20
 [/TD]

[TD="align: center"] 6
 [/TD]
[TD="align: center"] 6/14/2017/3:10:29
 [/TD]
[TD="align: center"] 77,51
 [/TD]
[TD="align: center"] 50,21
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:11:21
 [/TD]

[TD="align: center"] 7
 [/TD]
[TD="align: center"] 6/14/2017/3:14:54
 [/TD]
[TD="align: center"] 76,4
 [/TD]
[TD="align: center"] 44,95
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:16:56
 [/TD]

[TD="align: center"] 8
 [/TD]
[TD="align: center"] 6/14/2017/3:14:55
 [/TD]
[TD="align: center"] 76,37
 [/TD]
[TD="align: center"] 44,96
 [/TD]
[TD="align: center"] OFF
 [/TD]
[TD="align: center"] 15:16:57
 [/TD]

[TD="align: center"] 9
 [/TD]
[TD="align: center"] 6/14/2017/3:14:56
 [/TD]
[TD="align: center"] 76,38
 [/TD]
[TD="align: center"] 44,96
 [/TD]
[TD="align: center"] OFF
 [/TD]
[TD="align: center"] 15:16:59
 [/TD]

[TD="align: center"] 10
 [/TD]
[TD="align: center"] 6/14/2017/3:14:57
 [/TD]
[TD="align: center"] 76,39
 [/TD]
[TD="align: center"] 44,97
 [/TD]
[TD="align: center"] OFF
 [/TD]
[TD="align: center"] 15:17:00
 [/TD]

[TD="align: center"] 11
 [/TD]
[TD="align: center"] 6/14/2017/3:15:07
 [/TD]
[TD="align: center"] 76,4
 [/TD]
[TD="align: center"] 44,98
 [/TD]
[TD="align: center"] OFF
[/TD]
[TD="align: center"] 15:17:12
 [/TD]

[TD="align: center"] 12
 [/TD]
[TD="align: center"] 6/14/2017/3:15:08
 [/TD]
[TD="align: center"] 76,4
 [/TD]
[TD="align: center"] 44,96
 [/TD]
[TD="align: center"] INCR
 [/TD]
[TD="align: center"] 15:17:14
 [/TD]

[TD="align: center"] 13
 [/TD]
[TD="align: center"] 6/14/2017/3:15:09
 [/TD]
[TD="align: center"] 76,4
 [/TD]
[TD="align: center"] 44,96
 [/TD]
[TD="align: center"] INCR
 [/TD]
[TD="align: center"] 15:17:15
 [/TD]

[TD="align: center"] 14
 [/TD]
[TD="align: center"] 6/14/2017/3:15:10
 [/TD]
[TD="align: center"] 76,38
 [/TD]
[TD="align: center"] 44,95
 [/TD]
[TD="align: center"] INCR
 [/TD]
[TD="align: center"] 15:17:16
 [/TD]

[TD="align: center"] 15
 [/TD]
[TD="align: center"] 6/14/2017/3:15:11
 [/TD]
[TD="align: center"] 76,36
 [/TD]
[TD="align: center"] 44,94
 [/TD]
[TD="align: center"] INCR
 [/TD]
[TD="align: center"] 15:17:17
 [/TD]

[TD="align: center"] 16
 [/TD]
[TD="align: center"] 6/14/2017/3:15:12
 [/TD]
[TD="align: center"] 76,36
 [/TD]
[TD="align: center"] 44,95
 [/TD]
[TD="align: center"] INCR
 [/TD]
[TD="align: center"] 15:17:19
 [/TD]

[TD="align: center"] 17
 [/TD]
[TD="align: center"] 6/14/2017/3:15:13
 [/TD]
[TD="align: center"] 76,36
 [/TD]
[TD="align: center"] 44,95
 [/TD]
[TD="align: center"] INCR
 [/TD]
[TD="align: center"] 15:17:20
 [/TD]

[TD="align: center"] 18
 [/TD]
[TD="align: center"] 6/14/2017/3:15:26
 [/TD]
[TD="align: center"] 76,38
 [/TD]
[TD="align: center"] 44,8
 [/TD]
[TD="align: center"] OFF
[/TD]
[TD="align: center"] 15:17:36
 [/TD]

[TD="align: center"] 19
 [/TD]
[TD="align: center"] 6/14/2017/3:15:27
 [/TD]
[TD="align: center"] 76,39
 [/TD]
[TD="align: center"] 44,8
 [/TD]
[TD="align: center"] OFF
[/TD]
[TD="align: center"] 15:17:37
 [/TD]

[TD="align: center"] 20
 [/TD]
[TD="align: center"] 6/14/2017/3:15:28
 [/TD]
[TD="align: center"] 76,4
 [/TD]
[TD="align: center"] 44,78
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:17:39
 [/TD]

[TD="align: center"] 21
 [/TD]
[TD="align: center"] 6/14/2017/3:15:29
 [/TD]
[TD="align: center"] 76,37
 [/TD]
[TD="align: center"] 44,78
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:17:40
 [/TD]

[TD="align: center"] 22
 [/TD]
[TD="align: center"] 6/14/2017/3:15:30
 [/TD]
[TD="align: center"] 76,36
 [/TD]
[TD="align: center"] 44,76
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:17:41
 [/TD]

[TD="align: center"] 23
 [/TD]
[TD="align: center"] 6/14/2017/3:15:31
 [/TD]
[TD="align: center"] 76,37
 [/TD]
[TD="align: center"] 44,74
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:17:42
 [/TD]

[TD="align: center"] 24
 [/TD]
[TD="align: center"] 6/14/2017/3:15:32
 [/TD]
[TD="align: center"] 76,37
 [/TD]
[TD="align: center"] 44,72
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:17:44 [/TD]

</tbody>

Desired data (isolated manually)


<tbody>
[TD="align: center"]
[/TD]
[TD="align: center"] Time
 [/TD]
[TD="align: center"] INCR (out)
 [/TD]
[TD="align: center"] DECR (out)
 [/TD]
[TD="align: center"] Zone
 [/TD]
[TD="align: center"] Time
 [/TD]

[TD="align: center"] 1
 [/TD]
[TD="align: center"] 6/14/2017/3:10:24
 [/TD]
[TD="align: center"] 77,54
 [/TD]
[TD="align: center"] 50,29
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:10:55
 [/TD]

[TD="align: center"] 8
 [/TD]
[TD="align: center"] 6/14/2017/3:14:55
 [/TD]
[TD="align: center"] 76,37
 [/TD]
[TD="align: center"] 44,96
 [/TD]
[TD="align: center"] OFF
 [/TD]
[TD="align: center"] 15:16:57
 [/TD]

[TD="align: center"] 12
 [/TD]
[TD="align: center"] 6/14/2017/3:15:08
 [/TD]
[TD="align: center"] 76,4
 [/TD]
[TD="align: center"] 44,96
 [/TD]
[TD="align: center"] INCR
 [/TD]
[TD="align: center"] 15:17:14
 [/TD]

[TD="align: center"] 18
 [/TD]
[TD="align: center"] 6/14/2017/3:15:26
 [/TD]
[TD="align: center"] 76,38
 [/TD]
[TD="align: center"] 44,8
 [/TD]
[TD="align: center"] OFF
[/TD]
[TD="align: center"] 15:17:36
 [/TD]

[TD="align: center"] 20
 [/TD]
[TD="align: center"] 6/14/2017/3:15:28
 [/TD]
[TD="align: center"] 76,4
 [/TD]
[TD="align: center"] 44,78
 [/TD]
[TD="align: center"] DECR
 [/TD]
[TD="align: center"] 15:17:39 [/TD]

</tbody>

Best regards and thank you for taking the time to read my question.

Rasmus
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Hi Rasmus
Welcome to the board

Just add a new column where you check if the fish changed zone and filter.

In the example, in F2:

=(D2<>D1)

Copy down

In this column you'll have TRUE when the fish goes from one zone to another one.
Now filter with column F equal to TRUE and you have your result.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
194
admvlad
A
S
  • Solved
Individual Ranking
2
Replies
11
Views
455
sksanjeev786
S
H
  • Solved
First cell with a value of 0 after a certain row
2
Replies
10
Views
261
Fluff
Fluff
A
  • Solved
lookupifs?? lookup value so long as date value is greater than specified date
Replies
5
Views
103
Peter_SSs
Peter_SSs
I
  • Question Question
Accrual Worksheet Next Pay date Formula
Replies
0
Views
266
ItalianPlatinum
I

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top