Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

display distinct records only

Posted on 2014-12-10
8
Medium Priority
?
65 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 500 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 500 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
Industry Leaders: 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 27

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 500 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 500 total points
ID: 40493013
Select 3 column data > Goto Data > Remove duplicates.

You are done.

Kris
0
 
LVL 49

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

721 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