Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

display distinct records only

Posted on 2014-12-10
8
54 Views
Last Modified: 2015-01-25
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
Comment
Question by:rutgermons
8 Comments
 
LVL 37

Expert Comment

by:Neil Russell
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

by:
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

by:Simon
Simon earned 125 total points
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 125 total points
ID: 40492745
what about

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
    MyR.RemoveDuplicates Columns:=(MyArray), Header:=xlYes
    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

Open in new window

0
 
LVL 18

Assisted Solution

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

You are done.

Kris
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40493761
0
 
LVL 46

Expert Comment

by:Martin Liss
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question