Results 1 to 8 of 8

20050907, 09:37 #1
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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?

20050907, 09:48 #2
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20050907, 09:56 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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)

20050907, 10:14 #4
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
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" ????

20050907, 10:53 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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))

20050907, 13:09 #6
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20050908, 05:30 #7
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20050908, 05:47 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Iinsert value into column L if... (2000 sr 1)