Drag horizontally based on vertical data

[mlarson]

For the following I am trying to figure out what formula to use and how to drag it horizontally. Column A and B are given data. Columns C, D, E, F, and G need to use a formula to come up with the number shown.

When there is a 1 in B, count how many rows until there is a 1 in A. For example, the first row has a 1 for B and there are two more rows until there is a 1 for A. So, 2 is the answer. Since 2 corresponds with D, the 2 should be entered in D.

C=1 row difference
D=2 row difference
E=3 row difference
F=4 row difference
G=5 row difference

I'd also like to be able to copy and paste the formula beyond column G. Thank you very much for providing the formula that can be used to figure this out!

<table border="0" cellpadding="0" cellspacing="0" width="443"><col style="width: 48pt;" width="64" span="2"> <col style="width: 44pt;" width="59"> <col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl66" style="width: 48pt;" width="64">B</td> <td class="xl66" style="width: 44pt;" width="59">C</td> <td class="xl66" style="width: 48pt;" width="64">D</td> <td class="xl66" style="width: 48pt;" width="64">E</td> <td class="xl66" style="width: 48pt;" width="64">F</td> <td class="xl66" style="width: 48pt;" width="64">G</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">0</td> <td class="xl65" style="border-left: medium none;">1</td> <td class="xl65" style="border-left: medium none;">0</td> <td class="xl65" style="border-left: medium none;">2</td> <td class="xl65" style="border-left: medium none;">0</td> <td class="xl65" style="border-left: medium none;">4</td> <td class="xl65" style="border-left: medium none;">5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">0</td></tr></tbody></table>

 

[Andrew Poulsom]

Please explain the 4 and 5 in the first row for F and G .

 

[mlarson]

4 is in F because a 1 is in A which is 4 rows after the 1 in B.
5 is in G because a 1 is in A which is 5 rows after the 1 in B.

Thank you for taking a look at this.

 

[T. Valko]

Duplicate post:

http://www.mrexcel.com/forum/showthread.php?t=574511

 

[VoG]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).