Solved

display distinct records only

Posted on 2014-12-10
8
62 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
[X]
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
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 48

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

626 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