# Thread: Iinsert value into column L if... (2000 sr 1)

1. ## Re: Iinsert value into column L if... (2000 sr 1)

Wouldn't it be more logical to use 1 if there is only one occurrence of a value?

2. ## Re: Iinsert value into column L if... (2000 sr 1)

sorry, but my head int this day is FIRED!!!!
Now, the logical number for one occurrence is "1"
Sorry Hans.

3. ## Re: Iinsert value into column L if... (2000 sr 1)

In L3, enter the formula
<code>
=IF(A3=A2,"",COUNTIF(A:A,A3))
</code>
or in Italian
<code>
=SE(A3=A2,"",CONTA.SE(A:A,A3))
</code>
and fill down as far as needed. (You can double click the fill handle in the lower right corner of L3)

4. ## Re: Iinsert value into column L if... (2000 sr 1)

The formula work, but if after the last filled cell in A is a blank cell and the value is only one into A the formula return "1"
Delete the line 16 into sheet example and drag the formula into cell L16 the result is "1" ????

5. ## Re: Iinsert value into column L if... (2000 sr 1)

You should only fill down as far as the last filled row. If you really want, you can do this:

=IF(OR(ISBLANK(A3),A3=A2),"",COUNTIF(A:A,A3))

=SE(O(VAL.VUOTO(A3),A3=A2),"",CONTA.SE(A:A,A3))

6. ## Iinsert value into column L if... (2000 sr 1)

... my problem, i have this sheet with many value into column A (all value are already oredered) is possible to insert into column L the count numebr of value repeated from a equal range block?
Example:
into line 3 and 4 is present the same value 4500450961 well inert into column L the numer 2 into L3, blank the other line refered line 4

into line 11, 12 and 13 is present the same value 4500451080 well inert into column L the numer 3 into L11, blank the other line refered line 12 and 13

if the value into acolumn A is present only one insert into column L the value "0"

Tks.

7. ## Re: Iinsert value into column L if... (2000 sr 1)

Good morning Hans...
But with a macro is possible to have the same result?
Tks.

alt! peraph i have solved...Please my "Teacher" test and tell me,

Sub INSERISCE_VALORI_IN_L_PER_BLOCKS()

Dim eRow As Long
Dim ColA As Range
Dim cell As Range
Dim cNtr As Long
Dim cRow As Long
Dim val As Variant
eRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Set ColA = Range(Cells(3, 1), Cells(eRow, 1))
val = Cells(2, 1).Value
cRow = 3 'LINEA D CUI PARTIRE
cNtr = 1
For Each cell In ColA
If cell.Value = val Then
cNtr = cNtr + 1
Else
If cNtr = 1 Then
Cells(cRow, 12).Value = 0
Else
Cells(cRow, 12).Value = cNtr
End If
cRow = cell.Row
cNtr = 1
val = cell.Value
End If
Next cell
End Sub

8. ## Re: Iinsert value into column L if... (2000 sr 1)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•