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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
253
admvlad
A
V
  • Solved
Dynamic arrays, circular arguments and all I want to due is increment a previous number
Replies
3
Views
114
djclements
djclements
S
  • Solved
Individual Ranking
2
Replies
11
Views
470
sksanjeev786
S
H
  • Solved
First cell with a value of 0 after a certain row
2
Replies
10
Views
271
Fluff
Fluff
E
  • Solved
Duplicate row based on cell with text separated by semicolon
Replies
5
Views
99
JEC
JEC

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

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