domingo, mayo 19, 2013

Extraer valores únicos de rangos discontinuos

Esta nota trata sobre cómo extraer valores únicos de rangos que contienen más de una columna o rangos discontinuos.

Excel tiene dos métodos incorporados para esta tarea. En Excel 2007-2013 ambos se encuentran en la pestaña Datos: Filtro Avanzado y Quitar duplicados



Filtro Avanzado nos permite hacerlo con relativa facilidad usando la posibilidad, como mostramos en este video:



Con Quitar duplicados la técnica es un poco más elaborada, ya que incluye copiar la lista a un rango apartado y allí extraer los duplicados (si estamos interesados en guardar la lista original)




Las limitaciones de estos métodos comienzan cuando queremos extraer valores únicos de rangos discontinuos o de rangos que contienen más de una columna.

Podemos hacerlo con un código relativamente sencillo, similar al que mostramos en la nota sobre listas desplegables dependientes publicada hace poco. Este código se basa en el objeto Collection. El código es el siguiente


Sub extraerUnicos_Hoja()
'extraer valores unicos de rangos de varias columnas o no continuos
'Jorge Dunkelman - JLD Excel Blog, mayo 2013

    Dim collUnicos As New Collection
    Dim vcollItem As Variant
    Dim rngCell As Range, rngDatos As Range, rngLista As Range
    Dim lCounter As Long
 
    Set rngDatos = Application.InputBox(prompt:="Seleccione rango/s con datos", Type:=8)
    Set rngLista = Application.InputBox(prompt:="Seleccione la primera celda de la lista", Type:=8)
 
    On Error Resume Next
    For Each rngCell In rngDatos
        collUnicos.Add rngCell, Cstr(rngCell)
    Next rngCell
    On Error GoTo 0
 
    lCounter = 0
    For Each vcollItem In collUnicos
        rngLista.Offset(lCounter, 0) = CStr(vcollItem)
        lCounter = lCounter + 1
    Next vcollItem
 
End Sub



Con este código definimos el rango que contiene los datos (que puede contener varias columnas o ser discontinuo, pero todos los datos deben estar en la misma hoja), definimos la celda desde donde queremos empezar a pegar la lista de registros únicos y el código la genera.

Por ejemplo, en esta matriz de 6 filas por tres columnas (18 valores) donde hay 5 valores únicos (a, b, c, d y e)



Para que este código sea realmente útil debemos agregar algunas líneas para manejar errores que pueden ocurrir durante el proceso (por ejemplo, si el usuario selecciona un rango de datos con una sola celda o si aprieta el botón Cancel del InputBox). El código completo es el siguiente

Sub extraerUnicos_Hoja()
'extraer valores unicos de rangos de varias columnas o no continuos
'Jorge Dunkelman - JLD Excel Blog, mayo 2013

    Dim collUnicos As New Collection
    Dim vcollItem As Variant
    Dim rngCell As Range, rngDatos As Range, rngLista As Range
    Dim lCounter As Long
 
    On Error GoTo errCancel 'si se aprieta Cancel
 
    Set rngDatos = Application.InputBox(prompt:="Seleccione rango/s con datos", Type:=8)
    If rngDatos.Count < 2 Then
        MsgBox "Debe seleccionar un rango con mas de dos celda", vbCritical
        Exit Sub
    End If
 
    Set rngLista = Application.InputBox(prompt:="Seleccione la primera celda de la lista", Type:=8)
    If rngLista.Count <> 1 Then
        MsgBox "Seleccione solamente una celda", vbCritical
        Exit Sub
    End If
 
    On Error Resume Next
    For Each rngCell In rngDatos
        collUnicos.Add rngCell, Cstr(rngCell)
    Next rngCell
    On Error GoTo 0
 
    lCounter = 0
    For Each vcollItem In collUnicos
        rngLista.Offset(lCounter, 0) = CStr(vcollItem)
        lCounter = lCounter + 1
    Next vcollItem
 
    Exit Sub
 
errCancel:
Exit Sub
End Sub

11 comentarios:

  1. Jorge:
    Gracias por este excelente aporte y gracias tambien por compartir su avanzado conocimiento en esta herramienta.
    Saludos desde Perú

    ResponderBorrar
  2. He corregido el error que hacía que no se extrayeran valores numéricos.

    ResponderBorrar
  3. Mariam:
    Muchas gracias por compartir sus conocimientos.

    Esta Macro me podría ir muy bien en mi proyecto si en lugar de extraer el valor único, extrajese solamente los valores que NO SE REPITAN EN 3 Listas. Lista 1: Columna A = Nº Empleado
    Columna B = Fecha Alta

    Lista 2: Columna D = Nº Empleado
    Columna E = Fecha Alta

    Lista 3: Columna G = Fecha Alta

    Puede darse el caso de que algunos empleados de cada lista estén repetidos varias veces,o que en una lista aparezcan empleados que no están en otra u otras listas. Lo que los hace únicos es la Fecha de Alta. Es decir, el empleado 100 puede estar repetido varias veces, pero cada vez tendrá una Fecha de Alta distinta. Ej. 100 1/7/2013 y 100 2/7/2013.
    Sería posible que me ayudase a lograrlo?

    Muchas gracias

    ResponderBorrar
  4. Fijate lo que pongo en el enlace Ayuda (en la parte superior de la plantilla) y ponte en contacto conmigo en forma privada.

    ResponderBorrar
  5. Jorge, gracias por este gran aporte.
    Al respecto tengo una consulta, si quisiera que en la columna siguiente al de los únicos valores, apareciera el número de veces de cada único valor, cual sería el código, es importante esto porque habrá oportunidades que se debe saber cuantas veces se repite cada valor.
    Agradesco este aporte y felicitaciones por la excelente explicación.
    Saludos

    ResponderBorrar
  6. Huho,
    podrías usar CONTAR.SI para calcular cuántas veces aparece cada valor en la lista de origen. También podrías usar la función en el código para hacerlo programáticamente.

    ResponderBorrar
  7. Fantastico como siempre

    ResponderBorrar
  8. Hola Jorge, espero no le llegue mi comentario por duplicado, pero no sé si se envió el asociado a la cuenta de Google.
    En primer lugar darle las gracias y felicitarle por sus sugerencias y ayudas.
    Esta función en concreto me pareció muy interesante, pero me gustaría darle una vuelta más y quisiera saber si sería posible obtener los valores únicos de dos columnas discontinuas.
    Utilizando el último de sus ejemplos sería como si en la columna B hubiese valores numéricos y quisiéramos obtener los valores únicos de las columnas A y C.
    Muchas gracias y un saludo

    ResponderBorrar
  9. Se puede hacer usado el código publicado en la nota. Todo lo que hay que hacer es seleccionar el primer rango y luego el segundo manteniendo apretada la tecla Ctrl.

    ResponderBorrar
  10. Este comentario ha sido eliminado por el autor.

    ResponderBorrar
  11. Gracias por los conocimientos compartidos... me ha venido de perlas el código... :)

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.