Results 1 to 8 of 8
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  2. #2
    Gold Lounger
    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.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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)

  4. #4
    Gold Lounger
    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" ????

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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))

  6. #6
    Gold Lounger
    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.

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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •