Solved

Number Unique Values Concatenated

Posted on 2011-09-13
5
207 Views
Last Modified: 2012-05-12
I have the following data and want to ount up the number of unique thk + rad combinations.  I should end up with 4 in this example.  How can I do this?

thk      rad1      rad2      rad3      rad4      rad5      rad6
0.118      0.125                              
0.118      0.18      0.136      0.125                  
0.118      0.125                              
0.394      0.125      

If they were not formatted as shown above, we would have this data:
.118 .125
.118 .18
.118 .136
.118 .125
.118 .125
.394 .125
I get 4 unique values here                        
 unique.xls
0
Comment
Question by:munch007
5 Comments
 
LVL 4

Expert Comment

by:SafetyFish
ID: 36530473
First, apply an advanced filter (Data>Filter> Select Unique Records Only Checkbox)

Then, use the subtotal function in your summation box. The first argument, 3, lets the subtotal function know that it is giving a "CountA" of all the visible records (database term for row).

See attached
unique.xlsx
0
 

Author Comment

by:munch007
ID: 36530501
I cannot apply a filter to do this.  Not an option.
0
 
LVL 4

Expert Comment

by:SafetyFish
ID: 36530664
The functionality of excel when it comes to getting distinct values is relatively limited, out of the box. You could write a short macro for this if you're up to it, unfortunately I'm too busy today. Maybe someone else will write you one. Good luck.

0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 250 total points
ID: 36531092
I am assuming that
1) the data is being parsed from some text file and that you can successfully import it such that the rad(x) values are in individual columns
2) there is no distinction between rad1...rad6; they are merely placeholders for "rad" (radius).

I have attached sample VBA code that worked for me with your sample data.  It has a section commented "Import text file" which one could insert code there to import/parse the data as part of the routine, but in this instance, it is processing data already imported and inserted into columns with the labels you described.

Sub UniquePairs()
    Dim cell As Object
    Dim rng As Range
    Dim c, intLastRow As Integer
    Dim strModAdd As String

    ' Import Text File

    ' Convert to Two-column range
    Range("A2").Select
    intLastRow = Selection.End(xlDown).Row
    Range("A2", Selection.End(xlDown)).Select
    Set rng = Selection
    For Each cell In rng
        For c = 2 To 6 'check each adjacent column
            If cell.Offset(0, c).Value <> "" Then
                'add value pair to last row
                Range("A" & intLastRow + 1) = cell.Value
                Range("B" & intLastRow + 1) = cell.Offset(0, c).Value
                cell.Offset(0, c).Value = ""
                intLastRow = intLastRow + 1
            End If
        Next c
    Next cell
    Range("C:G").EntireColumn.Delete
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.Offset(0, 1)).Select
    strModAdd = Selection.Address
    Range(strModAdd).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("C1" _
        ), Unique:=True
    Range("A:B").EntireColumn.Delete 'optional
End Sub

Open in new window

0
 
LVL 5

Accepted Solution

by:
slycoder earned 250 total points
ID: 36531138
Here is my version, very similar . . .
Output is put in sheet 2 with a summary (xCounter)


Public Sub FindUniqueRads()
    Dim xRads(2, 1 To 100)
    Dim xArrayPos As Integer
    Dim xFindDup As Integer
    Dim xCounter As Integer
   
    ' Position in A2
    Sheets(1).Select
    Range("a2").Select
   
    xArrayPos = 1
    ' Load Values in array
    Do While ActiveCell.Value <> ""
        For xCol = 1 To 6
            ' Skip Empty cells
            If ActiveCell.Offset(0, xCol).Value <> "" Then
                xRads(1, xArrayPos) = ActiveCell.Value
                xRads(2, xArrayPos) = ActiveCell.Offset(0, xCol).Value
                xArrayPos = xArrayPos + 1
                ' Debug.Print xRads(1, xArrayPos), xRads(2, xArrayPos)
            End If
        Next xCol
       
        ' Jump down one row
        ActiveCell.Offset(1, 0).Select
    Loop
   
    ' Remove duplicates
    For idx = UBound(xRads, 2) To LBound(xRads, 2) Step -1
        ' Skip empty elements
        If xRads(1, idx) <> "" Then
            ' Cycle through the rest of the elements and remove duplicate
            For xFindDup = idx - 1 To 1 Step -1
                ' Blank out the Array element if it's a duplicate
                If xRads(1, xFindDup) = xRads(1, idx) And xRads(2, xFindDup) = xRads(2, idx) Then
                    xRads(1, idx) = ""
                    xRads(2, idx) = ""
                End If
            Next xFindDup
           
            ' If it survived the removal procedure, display
            If xRads(1, idx) <> "" Then
                Debug.Print xRads(1, idx), xRads(2, idx)
            End If
        End If
    Next idx
   
    ' output if necessary
    Sheets(2).Select
    Range("a2").Select
    xCounter = 0
    For idx = LBound(xRads, 2) To UBound(xRads, 2)
        ' Skip empty elements
        If xRads(1, idx) <> "" Then
            xCounter = xCounter + 1
            ActiveCell.Value = xRads(1, idx)
            ActiveCell.Offset(0, 1).Value = xRads(2, idx)
            Debug.Print xRads(1, idx), xRads(2, idx)
            ' Jump down one row
            ActiveCell.Offset(1, 0).Select
        End If
       
        Range("E1").Value = xCounter & " unique items found"
       
    Next idx
   
End Sub
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

Title # Comments Views Activity
Input Macro 8 22
macro for each dropdown 15 44
Excel for Mac - How make those Tabs larger? 2 30
Mac Excel column treating text as date 2 27
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

939 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

10 Experts available now in Live!

Get 1:1 Help Now