Si tengo la siguiente tabla:

Name  | IP      | OS
------+---------+---------
host1 | 1.1.1.1 | Windows
host1 | N/A     | N/A
host1 | 1.1.1.1 | N/A
host1 | N/A     | Windows

¿Cómo puedo eliminar duplicados y mantener la fila con más valores?

La salida debería ser así:

Name  | IP      | OS
------+---------+---------
host1 | 1.1.1.1 | Windows

Intenté ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1 pero elimina todos los duplicados host1, es decir, el resultado es este

Name  | IP      | OS
------+---------+---------
host1 | 1.1.1.1 | Windows
      | N/A     | N/A
      | 1.1.1.1 | N/A
      | N/A     | Windows
1
Jojo 15 nov. 2017 a las 17:46

2 respuestas

La mejor respuesta

Esto es lo que tengo hasta ahora. Asumiendo que sus datos son como

Datos de origen

Name    IP      OS      Country
host1   1.1.1.1 Windows N/A
host1   1.1.1.1 Windows Taiwan
host1   1.1.1.1 N/A     N/A
host1   N/A     N/A     Taiwan
host2   N/A     Apple   N/A
host2   N/A     Apple   Taiwan
host2   N/A     N/A     Taiwan
host2   1.1.1.1 N/A     N/A
host2   1.1.1.1 Apple   Taiwan

Salida

Name    IP      OS      Country
host1   1.1.1.1 Windows Taiwan
host2   1.1.1.1 Apple   Taiwan

Código

Private Sub test()
    With ActiveSheet
        Dim i, startRow, endRow As Long
        Dim NACount, LessNACount As Integer
        'Set a large enough number
        LessNACount = 1000
        Dim cell As Range

        'Assuming Row 1 is title
        startRow = 2

        Dim DeleteRange, SameNARange As Range
        'Setting it a dummy range
        Set DeleteRange = .Rows(.Rows.Count)

        For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
            NACount = WorksheetFunction.CountIf(.Rows(i), "N/A")
            'Cells with N/A are more than the least row
            If NACount > LessNACount Then
                Set DeleteRange = Union(DeleteRange, .Rows(i))
            'Cells with N/A are equal than the least row
            ElseIf NACount = LessNACount Then
                Set SameNARange = Union(SameNARange, .Rows(i))
            'Cells with N/A are less than the least row, so it becomes the new least row
            Else
                'We have found at least a row, abandon them
                If Not SameNARange Is Nothing Then
                    Set DeleteRange = Union(DeleteRange, SameNARange)
                End If
                Set SameNARange = .Rows(i)
                LessNACount = NACount
            End If

            'host1 is done, goes into host2
            If .Range("A" & i).Value <> .Range("A" & i + 1).Value Then
                LessNACount = 1000
                Set SameNARange = Nothing
            End If
        Next i

        DeleteRange.Delete
    End With
End Sub
0
newacc2240 15 nov. 2017 a las 16:32

En el SQL para la extracción de datos, podría hacer algo como esto:

SELECT Name, MAX(IIF( ip <> "N/A", ip)) AS ip, MAX(IIF( os <> "N/A", os)) AS os
FROM table
GROUP BY Name
0
John Ink 15 nov. 2017 a las 16:35