Solved

# display distinct records only

Posted on 2014-12-10
43 Views
Folks

I have the following data

server 1      100      a
server 2      200      b
server 1      100      a
server 1      400      a
server 3      1000      c
server 2      200      b

I'd like to show only the distinct rows and no duplicates for colum a like so

server 1      100      a
server 2      200      b
server 3      1000c

can anyone assist?

cheers
0
Question by:rutgermons

LVL 37

Expert Comment

ID: 40492638
How do you decide if the server1 400 a or the server1 100 a should be displayed? they are not row duplicates just two of the 3 columns.
0

LVL 37

Accepted Solution

Neil Russell earned 125 total points
ID: 40492646
IF they were all the same for each server then a simple
SELECT DISTINCT * FROM table1
would have done.
0

LVL 18

Assisted Solution

ID: 40492674
If you're just looking for the first occurence of each server, you could add another column to the right with this formula in row 2
=COUNTIF(A2:A\$2,A2)=1
Then fill the formula down and filter for TRUE on the new column
0

LVL 25

Assisted Solution

ProfessorJimJam earned 125 total points
ID: 40492745

server 1      400      a

this is also unique in your list given.

??????????

if you want the distinct rows to remain and duplicates to be deleted then  use the following code

``````Sub RemoveDuplicateRows()
Application.ScreenUpdating = False
Dim ColN As Long
Dim MyS As Worksheet: Set MyS = ActiveSheet
Dim MyR As Range: Set MyR = MyS.Cells(1, 1).CurrentRegion
Dim NumCol As Long: NumCol = MyR.Columns.Count
Dim MyArray As Variant: ReDim MyArray(0 To NumCol - 1)
For ColN = 1 To NumCol
MyArray(ColN - 1) = ColN
Next
Dim rowcount As Long, i As Long, j As Long, k As Boolean
rowcount = MyR.Rows.Count
For i = rowcount To 1 Step -1
k = 0
For j = 1 To NumCol
If MyR.Value2(i, j) <> "" Then
k = 1
Exit For
End If
Next j
If k = 0 Then
MyR.Rows(i).Delete Shift:=xlUp
End If
Next i
Application.ScreenUpdating = True
End Sub
``````
0

LVL 18

Assisted Solution

krishnakrkc earned 125 total points
ID: 40493013
Select 3 column data > Goto Data > Remove duplicates.

You are done.

Kris
0

LVL 5

Expert Comment

ID: 40493761
0

LVL 45

Expert Comment

ID: 40568993
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0