Counting contiguous rows of numbers in a spreadsheet

[JamesCT9]

Hi there,

I've not really done any excel programming before, but I'm not aware of any functions that would allow me to achieve the following:

I have data for the presence of fish (1 - present, 'blank' not present) at discreet time intervals (the columns), with each fish in the observation having its own separate row. Please see attached simplified example.fish_residency_data.jpg

'1's in a contiguous line (i.e. without a blank space separating them) are treated as a single 'residency period', so in the example Fish 1 & 2 have a single residency period (fish 2 being there for less time than fish 1), and Fish 3 has two residency periods. I need to extract residency data from this table i.e. for each fish, how many residency periods and how long is each period (how many '1's in a contiguous line).

Does anyone have a recommendation for how best to tackle this problem in excel please? Happy to provide more info if required.

Many thanks,

James

[apo]

Hi..

Something like this works on your sample data..

It uses static ranges.. but can be adapted to be dynamic (more time periods etc)..

I gotta go play basketball.. will do that if you need it later..

Please Login or Register  to view this content.

[JamesCT9]

Thanks man - hope basketball was good!

Not sure if it needs to be dynamic, the real data I use has 50 time periods, and an unknown number of rows.

Here is an section from my actual dataset: New Data example.xlsx
(the day/eve column needs to be there, but don't worry about it)

Ideally the output would give the length of each residency period in a separate cell on that same row.

If you have time to look at it again I'd be really grateful.

Thanks again,

James

[apo]

No worries.. can you show me (re attach a sample file).. exactly how you would like the end result to look like for the Sample data posted in post #3.

[JamesCT9]

Thanks once again. Example attached, it would be useful to have the output on the original dataset as I'll probably pull it all out using a pivot table thereafter.

Cheers,

James

Out example.xlsx

[apo]

Hi.. Done..

Press the "Do It!" button to see results..

Please Login or Register  to view this content.

[JamesCT9]

You're a legend - great job - thanks so much.

Could you just tell me how to paste the code/button into my own datasheet (it's the same format as the one you just did this for).

Cheers,

James

[apo]

Hi..

* Add a ActiveX CommandButton. You do this via the Developer Tab.. if this Tab is not enabled in the Ribbon Menu.. goto the Options and enable it.. it will open a whole new world to you...

* In the Developer Tab.. click on Insert > Active X Controls > CommandButton. Place your button on your sheet in the position you want with a left click.

* Then.. (Make Sure Design Mode) is enabled in the Developer Tab... and double click on the Command Button you just added.

* Paste the code from Post # 6 (not including the 1st line(Private Sub....) and the last line(End Sub)).. as it will already be there..

* Exit Design Mode (by pressing the Design mode button again).. and the button will now be active with the code attached to it..