Count unique values using pivot table

Question

Please help me to create an Excel pivot table that would count the number of unique values in the following table. I've the following table with server racks connection cables and their types:

Source SDevice  Dest DDevice Cable_tape 
1.1    node1    2.1  switch1 Fiber
1.1    node2    2.2  switch2 Copper
2.1    server1  3.7  switch1 SAN
2.1    node12   1.1  switch5 Fiber

I would like to create a pivot table that would calculate total numbers for each cable type based on unique end to end direction so the output will look similar to the following:

From  To     Copper  SAN  Fiber
1.1   2.1                   2
1.1   2.2      1            
2.1   3.7             1

If source and destination exists in opposite direction it should appear only once in the result (like 1.1 > 2.1 and then 2.1 > 1.1). Is it possible to achive this using pivot tables? Thanks in advance for support.

Which values must be unique? Does the whole line need to be unique or is it just one column in which you want to find unique results? — ClaireLandis
By unique values I've meant Source and Destination rack numbers, — PaVliK

Zack Zack · Accepted Answer · 2018-07-11T15:34:27

Assuming your Source and Dest columns are always parsable as numbers, you could add a new column that calculates your path...something like =MIN([@Source],[@Dest]) & " -> " & MAX([@Source],[@Dest]).

Then, your pivot table would just have this field as the rows, and the cable type as the column:

However, this doesn't take into account values not parsable as numbers (e.g. 1.1.1 or values that are the same when parsed as numbers but semantically different (e.g. 1.1 and 1.10).

To get around this, you could create 2 user-defined functions in VBA, and call them from Excel. I'm not sure if you're familiar at all with VBA (I know your question certainly wasn't tagged VBA. A good introduction to writing VBA functions in Excel can be found here. You'll want functions like the following: Option Explicit

    Public Function StringMax(rng As Excel.Range, Optional ignoreCase As Boolean = True) As Variant
        Dim cell As Excel.Range, maxValue As String, comp As VbCompareMethod

        If ignoreCase Then
            comp = vbTextCompare
        Else
            comp = vbBinaryCompare
        End If

        ' make sure the range you're passing in only has 1 column
        If rng.Columns.Count > 1 Then
            StringMax = CVErr(XlCVError.xlErrNA)
        Else
            ' loop through cells in the range, checking if each one is > the current max
            StringMax = CStr(rng.Cells(1, 1).Value)
            For Each cell In rng.Cells
                If VBA.StrComp(CStr(cell.Value), StringMax, comp) > 0 Then
                    StringMax = CStr(cell.Value)
                End If
            Next cell
        End If

    End Function

    Public Function StringMin(rng As Excel.Range, Optional ignoreCase As Boolean = True) As Variant
        Dim cell As Excel.Range, maxValue As String, comp As VbCompareMethod

        If ignoreCase Then
            comp = vbTextCompare
        Else
            comp = vbBinaryCompare
        End If

        ' make sure the range you're passing in only has 1 column
        If rng.Columns.Count > 1 Then
            StringMin = CVErr(XlCVError.xlErrNA)
        Else
            ' loop through each cell in the range, checking if each one is < the current max
            StringMin = CStr(rng.Cells(1, 1).Value)
            For Each cell In rng.Cells
                If VBA.StrComp(CStr(cell.Value), StringMin, comp) < 0 Then
                    StringMin = CStr(cell.Value)
                End If
            Next cell
        End If

    End Function

Count unique values using pivot table

1 Answers