Extraer valores únicos de rangos discontinuos

domingo, mayo 19, 2013

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

6 comments:

Yavir,  24 mayo, 2013 17:46  

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

Jorge Dunkelman 24 junio, 2013 07:15  

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

Anónimo,  26 julio, 2013 15:57  

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

Jorge Dunkelman 26 julio, 2013 17:08  

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

Hugo,  29 septiembre, 2013 07:51  

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

Jorge Dunkelman 01 octubre, 2013 20:36  

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.

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP