# count a block of numbers, and calculate block size

#### ekalavya

##### New Member
Hi everyone,

I have approximately 100 rows (samples) and 4000 columns (Trait1, 2, ... n) that look like below.

Input
 Size 0.1 0.3 0.5 0.6 0.9 1 1.1 2.2 Sample Trait1 Trait2 Trait3 Trait4 Trait5 Trait6 Trait7 Trait8 A 0 0 0 1 1 0 0 0 B 1 1 1 0 1 1 1 1 C 0 0 1 1 1 0 0 0 D 0 0 0 0 0 0 1 1

<tbody>
</tbody>

For each sample, I want to scan the block of numbers, and report the size of the block (first row). For example, Sample A has first block of 0s of size 0.5 (0.5 - 0.0); second block of 1s of size 0.3 (0.9 - 0.6); and third block of 0s of size 1.2 (2.2 - 1.0).

Desired output
 Sample Block1 Size1 Block2 Size2 Block3 Size3 A 0 0.5 1 0.3 0 1.2 B 1 0.5 0 0 1 1.3 C 0 0.3 1 0.4 0 1.2 D 0 1 1 1.1

<tbody>
</tbody>

Please note that for sample B, which as only 1 zero, the size is also zero as it is not a block. Therefore, if there's a value by itself (count of 1), then the size should be zero.

I hope someone here can help me with this as my data size is pretty big and it is killing me to scan it by color-coding the spreadsheet.

Thank you for your generous help, as always.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
For example, Sample A has first block of 0s of size 0.5 (0.5 - 0.0);
Why is it not 0.4 (0.5 - 0.1)?

Why is it not 0.4 (0.5 - 0.1)?

Counting for this data begins with 0 so the first 'size' is value - 0.

Actually, in hindsight, I should have listed 0 as the first value in the 'Size' row to avoid confusion.

Last edited:

Replies
7
Views
594
Replies
6
Views
407
Replies
1
Views
342
Replies
10
Views
999
Replies
4
Views
293

1,217,384
Messages
6,136,274
Members
450,001
Latest member
KWeekley08

### 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.

### Which adblocker are you using?

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

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