Assigning same numbers to different cells

[onaga]

Dear all,

i would like to assign the same number to different cells but following a certain order:
-Check B2:B250,000; if cells are bounded by zeros up and down; assign 1 to the adjacent cells in C, check the next cells bounded by zeros and assign 2 to the adjacent cells in C, continue in that order..otherwise leave as blank..see example below. how can I do this?

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width:48pt" span="2" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt; width:48pt" align="right" height="20" width="64">0</td> <td class="xl63" style="border-left:none;width:48pt" width="64"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">0</td> <td class="xl63" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">22</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">55</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">39</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">40</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">12</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">41</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">90</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">25</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">0</td> <td class="xl63" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">9</td> <td class="xl63" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">12</td> <td class="xl63" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">34</td> <td class="xl63" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">0</td> <td class="xl63" style="border-top:none;border-left:none"> </td> </tr> </tbody></table>

 

[taigovinda]

Welcome to the board!

Try this formula and copy down:

Excel Workbook
	A	B
1	Header	Header
2	5	0
3	0
4	22	1
5	15	1
6	37	1
7	0
8	0
9	14	2
10	12	2
11	19	2
12	0
Sheet1

Cell Formulas
Range	Formula
B2	=IF(A2=0,"",IF(B1="",MAX($B$1:B1)+1,N(B1)))

 

[VoG]

Try this macro

Sub atest()
Dim LR As Long, i As Long, j As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 3 To LR
    With Range("B" & i)
        If .Value <> 0 Then
             If .Offset(-1).Value = 0 Then j = j + 1
            .Offset(, 1).Value = j
        End If
    End With
Next i
End Sub

 

[onaga]

Thank you thank you
Works great!!!

 

[Aladin Akyurek]

Dear all,

i would like to assign the same number to different cells but following a certain order:
-Check B2:B250,000; if cells are bounded by zeros up and down; assign 1 to the adjacent cells in C, check the next cells bounded by zeros and assign 2 to the adjacent cells in C, continue in that order..otherwise leave as blank..see example below. how can I do this?

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width:48pt" span="2" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt; width:48pt" align="right" height="20" width="64">0</td> <td class="xl63" style="border-left:none;width:48pt" width="64"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">0</td> <td class="xl63" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">22</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">55</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">39</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">40</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">12</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">41</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">90</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">25</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">0</td> <td class="xl63" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">9</td> <td class="xl63" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">12</td> <td class="xl63" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">34</td> <td class="xl63" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" align="right" height="20">0</td> <td class="xl63" style="border-top:none;border-left:none"> </td> </tr> </tbody></table>

Also, assuming headers in row 1...

In B2 enter and copy down:

=IF(A2,LOOKUP(BigNum,CHOOSE({1,2},0,LOOKUP(BigNum,$B$1:B1)))+IF(N(A1)=0,1,0),"")

BigNum is defined as referring to:

=9.99999999999999E+307