Solved

display distinct records only

Posted on 2014-12-10
8
47 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 25

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now