Ingresar números en una lista y contar cuantas veces coincide ese registro dentro de una celda dinámica

Podrías explicarlo con imágenes y ejemplos.

Cuál es tu fórmula "dinámica" que tienes en la celda "x" (puedes cambiar "X" por la celda real).

Recuerda, nos estás pidiendo ayuda, entonces ayúdanos proporcionando toda la información relevante para entender tu requerimiento. Incluso si esa información te parece "obvia", recuerda, sólo es obvia para ti porque estás familiarizado con tus datos.

Describe la secuencia de lo que va sucediendo en tu hoja.

Es decir:

Paso 1. Supongo que el rango B2:B100 empieza con valores vacíos.

Paso 2. Qué valor tienes en la celda "x"

Paso 3. Escribes un número en el rango B2:B100, por ejemplo en la celda B2, escribes el número ¿?, aquí en tu ejemplo, pones la celda y el numero que escribes. Debes poner el ejemplo completo.

¿Paso 4. qué debería suceder?

Paso 5. qué resultado presenta la celda "x"

Paso 6. qué resultado esperas y en dónde quieres poner el resultado

Paso 7. escribes otro número en el rango B2:B100, la celda B3, escribes otro número, ¿cuál número?

¿Paso 8. qué debería suceder?

Paso 9. qué resultado presenta la celda "x"

Paso 10. Qué resultado esperas y en dónde quieres poner el resultado

Sería genial si acompañas tu explicación con imágenes.

Hola Dante, muchas gracias por responder. Luego de tus observaciones entendi que no fuí nada claro lo solicitado. Paso a describir:

En el rango B2 a B100 se ingresan los números manualmente uno a uno.

La fórmula dinamica en la celda "X" o mejor dicho celdas "X" corresponden a las de un ranking (columnas H5 a H14 en la imagen). En ellas tiene solo fórmulas de buscarv (que a su vez toma los valores de otras celdas con fórmula jerarquia.eqv pero entiendo que salvo me digas lo contrario no intervienen puntualmente en lo solicitado).

Si aún no existe ningun dato ingresado en B2 a B100 no existen valores que mostrar en "X".

A medida que se ingresan datos en B2, los valores en las columna H comienzan a reubicarse dinamicamente de acuerdo al ranking, y lo que necesito es que en la columna "E" se me contabilicen la cantidad de veces que al ingresar el registro en B, el nuevo dato colocado coincidió con la posición en donde ya se encontraba dicho dato dentro del ranking (Importante: Lo contabilizado debe corresponder a la posición dentro del ranking en donde se dió la coincidencia con el nuevo dato ingreso en B sin que este úlitmo llegue a alterar la nueva posición.

La columna E contabiliza la cantidad de veces que dicha posición del ranking (1,2,3,4,5,6,7,8,9,10) coincidió con que el dato que ya figuraba en celdas "X" fue igual al ingresado en B todo ello antes que el nuevo dato en B marque el nuevo posicionamiento por ranking.

Si por ejemplo se ingresa un nuevo valor (ejemplo:100) que aún no figura en el ranking, no deberia contabilizarse nada hasta que llegue la coincidencia de ingrear el mismo valor en B y me capture la posición en donde se encontraba 100.

Espero haber sido claro y estoy atento ante nuevas dudas para poder resolverlo.

Pd: Los numeros contabilizados en E son ilustrativos. Allí deberia ir el resultado esperado.

Tambien sería muy util, por ejemplo en la columna E, se guarde como valor(que no copie la fórmula) el número de celda Anterior (de "X") de Una Celda Modificada. Para compararlo con el actualizado. Esta puede ser luego una nueva pregunta.

Saludos!

Sobre esto ultimo hago una corrección.

"También sería muy útil, por ejemplo en la "columna D" no E, se guarde como valor(que no copie la fórmula) el número de celda Anterior (de "X") de Una Celda Modificada. Para compararlo con el actualizado. Esta puede ser luego una nueva pregunta."

Saludos

Para los resultados en la columna E, puedes utilizar la función Contar. Si

Prueba con esta fórmula en las celdas E5 a E14 y me comentas:

=CONTAR.SI($B$2:$B$200;G5)

Lo de los valores en la columna D, lo estoy revisando, porque si el ranking de un número cambia, por ejemplo, si estaba en primer lugar y pasa a segundo lugar, en el renglón "2", columna D deberá aparecer su conteo anterior, en el renglón "2" columna "E" su nuevo conteo.

Creo que estoy entendiendo lo que buscas. Dime si lo anterior es correto.

Si utilizo la fórmula contar.si me diría la cantidad de veces que se repite el número dentro del ranking, pero lo que necesito es contar las coincidencias en cada posición del ranking (no los números) en donde el numero ingresado en B (doy enter) coincide con la posición del mismo número en las columnas H (este conteo debería ser antes de que el nuevo dato ingresado en B determine las nuevas reubicaciones de posiciones) Seria conocer en que posición existen más coincidencias de acuerdo al ingreso de datos con los ya existentes.

Y lo otro era la posibilidad de que en la columna D se guarde el valor anterior de la columna H en donde exista cambio, esto sería para comparar.

Como dato adicional de esto ultimo, encontré un código pero lo malo es que lo copia con fórmula y se vuelve a calcular, debería copiarse como valor y actualizarse en cada cambio

Código de VBA: guarde el valor de celda anterior en otra celda de columna

Dim xRg As Range

Dim xChangeRg As Range

Dim xDependRg As Range

Dim xDic As New Dictionary

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim I As Long

    Dim xCell As Range

    Dim xDCell As Range

    Dim xHeader As String

    Dim xCommText As String

    On Error Resume Next

    Application.ScreenUpdating = False

    Application.EnableEvents = False

    xHeader = "Previous value :"

    x = xDic.Keys

    For I = 0 To UBound(xDic.Keys)

        Set xCell = Range(xDic.Keys(I))

        Set xDCell = Cells(xCell.Row, 7)

        xDCell.Value = ""

        xDCell.Value = xDic.Items(I)

    Next

    Application.EnableEvents = True

    Application.ScreenUpdating = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim I, J As Long

    Dim xRgArea As Range

    On Error GoTo Label1

    If Target.Count > 1 Then Exit Sub

    Application.EnableEvents = False

    Set xDependRg = Target.Dependents

    If xDependRg Is Nothing Then GoTo Label1

    If Not xDependRg Is Nothing Then

        Set xDependRg = Intersect(xDependRg, Range("C:C"))

    End If

Label1:

    Set xRg = Intersect(Target, Range("C:C"))

    If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) Then

        Set xChangeRg = Union(xRg, xDependRg)

    ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) Then

        Set xChangeRg = xDependRg

    ElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) Then

        Set xChangeRg = xRg

    Else

        Application.EnableEvents = True

        Exit Sub

    End If

    xDic.RemoveAll

    For I = 1 To xChangeRg.Areas.Count

        Set xRgArea = xChangeRg.Areas(I)

        For J = 1 To xRgArea.Count

            xDic.Add xRgArea(J).Address, xRgArea(J).Formula

        Next

    Next

    Set xChangeRg = Nothing

    Set xRg = Nothing

    Set xDependRg = Nothing

    Application.EnableEvents = True

End Sub

Muchas gracias Dante por la ayuda

Entonces no entendí.

Pero lo que necesito es contar las coincidencias en cada posición del ranking

¿A qué te refieres con "coincidencias"?

Siguiendo con tu ejemplo de la imagen que pusiste.

Explica por qué pusiste o cómo llegar a esos valores de la columna verde.

Yo te ayudo con la macro, pero antes de hacer la macro, necesito entender a qué te refieres con "coincidencias"

El conteo de cada posición y su coincidencias en único de cada posición y se va incrementando, no importa el número que sea y  que el número cambie dentro del ranking, me quedo solo con la coincidencia que pudo o no tener cada número en H dentro de cada posición del ranking con lo ingresado en B.

En la imagen, me debería sumar 4 por que el 50 se encontraba en posición 2, aunque con el nuevo dato haga que cambien las posiciones. La posición 2 ya se guardó 4 coincidencias

Los valores de la columna verde serían los conteos que necesito generar, los colocados no son validos en cuando al cálculo correcto ya que justamente necesito la fórmula que los pueda contabilizar considerando lo que se ingresa en B y coincida con lo de H. Son a modo de ejemplo y para que se entienda generé la ultima imagen. 

Quedo atento si se requiere más información.

Saludos!

Sigo sin entender cómo calculas los valores de la columna verde.

Y no lo entiendo justamente porque tú no lo has explicado.

En tu ejemplo tienes en la columna verde 5,3,2,1. Puedes explicar por qué de cada uno de esos valores.

Después de que expliques cómo llegar al 5, al 3, al 2 y al 1.

Pones los valores en la columna "Historia"

Entonces capturas un 50 en la celda B15

Y vuelves a explicar qué valores deben aparecer en la columna verde. Y qué valores deben aparecer en la columna "Historia"

Pero explica por qué llegas a cada resultado. Para ti es obvio, porque conoces los datos, pero aunque sea obvio para ti, debes explicarlo, por muy simple que te parezca.

Para mi sería más sencillo de entender si pones una imagen con los datos iniciales.

Luego otra imagen con el valor 50 que estás capturando en B15.

Y otra imagen con los resultados en todas las columnas D, E y H.

No importa cuántas imágenes pongas. No hay limite de imágenes. Pero debes poner las explicaciones.

Gracias Dante por tus observaciones, intentaré explayarme mejor.

Creo que estas imagenes aclararán lo que necesito

Desde ya muy agradecido con su ayuda.

Quedo atento.

Saludos.

Hasta este punto todo claro:

Pero no entiendo por qué no re-acomodas las coincidencias.

Estás re-acomodando el 50 y el 100, pero las coincidencias permanecen en su sitio:

Después escribes un 50 en B7. En teoría tenemos 3 coincidencias del 50 y 1 coincidencia del 100, en total 4 coincidencias. Pero en lugar de escribir en la celda E5 = 3  y en la celda E6 = 1. Escribes E5=2, E6=2

¿Es correcto lo anterior?

Más preguntas. Para qué quieres saber en la celda E5 el número de coincidencias, si no sabes a qué números pertenecen. Siguiendo con tu lógica, si escribes 6 veces el número 30, en las celdas B8 a B13, tendrás en la celda E5: 3 coincidencias : 1 que era del 100, 1 que era del 50 y 1 del 30. Y en historial , celda D5 = 50.

Es así, o estoy totalmente equivocado y sigo sin entender cómo cuentas las coincidencias.

Ya casi que lo tienes.

Hago las aclaraciones y debajo la secuencia de las coincidencias e historiales paso a paso según tu ejemplo que es correcto.

En la primer imagen (que tiene indicaciones en azul) lo importante era destacar que necesito que primero que todo me genere la coincidencia en posición 2, ya que al momento de ingresar el dato era donde estaba el 50 y es en donde ocurrió.

Luego de que me registre dicha coincidencia sigue el normál proceso del propio ranking y el 50 sube a posición 1 y lo del historial.

En la imagen 1 solo anticipo que el 50 subirá a posición 1 pero las coincidencias quedan registradas en la posición donde ocurre.

Luego en la imagen 2 de tu respuesta sí estan reordenados los datos como se vería finalmente luego de todo lo anterior explicado.

Es correcto, las coincidencias permanecen en su sitio aguardando nuevos registros por ocurrir.

No importa conocer a que números correspondieron las coincidencias, sino en que posición se generaron "x" cantidad de coicidencias.

En la imagen 3 de tu respuesta:
Se ingresa el 50 en B. Antes de ese momento en posición 1 del ranking existian almacenadas solo 1 coincidencia, ahora con el nuevo 50 se cumple una nueva coincidencia en posición 1 en donde el número ingresado en B ya existia en posición 1. Por ende suma 2 coincidencias.

Y la celda en donde está el 100 (posición 2) por el momento sigue sin nuevas coincidencias hasta que el número que se encuentre en dicha posición cuando se ingrese un nuevo dato en B coincidan.

Es correcta tu cuenta de coincidencias en lo indicado en imagen 3, solo que no necesito que se recuerde a qué números pertenecieron.
Obviamente si es necesario que se genere un cálculo auxiliar que registre en total de que números corresponden las coincidencias no hay problema, mientras siga a lógica que describo.

Necesito saber en cada celda de E en que posición se dieron más cantidad de coincidencias independientemente del número que lo haya generado. Solo eso.

Saludos! y sigo atento.

De acuerdo, reviso los últimos ejemplos y preparo el código para que lo pruebes.

Pon el siguiente código en los eventos de tu hoja.

Funciona cuando agregas números en la columna B de uno en uno.

Pero si eliminas un número de la columna B, tendrías que especificar cómo se debe borrar del conteo de coincidencias y si afecta la columna de Historial.

Option Explicit
'
Dim d As Variant, e As Variant, g As Variant
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim rng As Range
  '
  Set rng = Intersect(Target, Range("B2:B100"))
  If Not rng Is Nothing Then
    d = Range("D5:D14").Value 'Historial
    e = Range("E5:E14").Value 'Coincidencias
    g = Range("G5:G14").Value 'Números
  End If
End Sub
'
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, rng1 As Range, cell As Range
  Dim i As Long, j As Long, fila As Long
  Dim dic As Object
  Set dic = CreateObject("Scripting.Dictionary")
  '
  If Target.CountLarge > 1 Then Exit Sub
  Set rng1 = Range("B2:B100")
  Set rng = Intersect(Target, rng1)
  If Not rng Is Nothing Then
    For Each cell In rng1
      If cell.Value <> "" Then
        dic(cell.Value) = dic(cell.Value) + 1
      End If
    Next
    '
    For i = 1 To UBound(g, 1)
      If g(i, 1) <> "" Then
        If g(i, 1) = Target.Value Then 'aquí ocurrió la coincidencia
          If dic.exists(g(i, 1)) Then
            If dic(g(i, 1)) > 1 Then
              e(i, 1) = e(i, 1) + 1
            End If
          End If
        End If
      End If
      If g(i, 1) <> Range("G" & i + 4).Value Then
        d(i, 1) = g(i, 1)
      End If
    Next
  End If
  Range("D5:D14").Value = d
  Range("E5:E14").Value = e
  d = Range("D5:D14").Value 'Historial
  e = Range("E5:E14").Value
  g = Range("G5:G14").Value
End Sub

Hola Dante, estuve probando la macro y funciona de mil maravillas!!, hace exactamente lo que tiene que hacer. Desde ya que estoy muy agradecido y la valoración es EXCELENTE.

Respondiendo a tus comentarios:

-Funciona cuando agregas números en la columna B de uno en uno.

¿Que cambiaría en la macro si la columna B en donde ingreso los datos estuviera en otra hoja denominada "BASE"?.

-Pero si eliminas un número de la columna B, tendrías que especificar cómo se debe borrar del conteo de coincidencias y si afecta la columna de Historial.

En esto no hay problema ya que el conteo de coincidencias y la columna historial deberian borrarse cuando no existan datos en B. 

Excelente trabajo!

. Encantado de ayudarte. Gra cias por comentar.

Hola Dante, como estas?.

Realice una nueva consulta denominada "Almacenar celdas dinámicas según correspondencia", me gustaria saber si lo podrías revisar, ya que es algo similar a esto pero cambia en algunos detalles.

Sería de gran ayuda. Obviamente sin compromiso.

Muchas gracias.

Saludos.