0

I have the following two columns:

Con.By  Prod
   A     1
   A     1
   A     2
   A     2
   B     1
   B     1
   B     2
   B     2

I can easily get the unique values either in column Con.By or column Prod. However, my requirement is to get the unique values in Column Prod for each of the unique values in column Con.By. Hence my intended output for the above columns of data would be:

Con.By  Prod
   A     1
         2
   B     1
         2

I am not even sure where to begin. I tried writing a VBA code to filter column Con.By for each value individually and then find unique values in the Prod column. However, this method doesn't work as my Con.By column contains way too many entries and some of which may change with time.

What is the best way to get the output I require? Are there excel formulas or does it require VBA coding?

  • This is precisely the output a Pivot Table would give, with both Con.By and Prod added to the Row Labels. – XOR LX Oct 16 '15 at 7:38
0

You can try this method. It makes use of a User Defined Class to help in the collection of unique items in the second column.

The code, in both the Regular and Class Modules, makes use of the fact that when you try to add a member to the collection that has the same Key as an existing member, a 457 error will be generated.

You can see in the code where to make changes to account for differences in your worksheet and ranges for the Source (Src) and Results (Res).

You must RENAME the Class Module cConBy. After you Insert Class Module, F4 opens the Property Window. Change the Name Parameter there.

Class Module


Option Explicit
Private pConBy As String
Private pProd As String
Private pProds As Collection

Private Sub Class_Initialize()
    Set pProds = New Collection
End Sub

Public Property Get ConBy() As String
    ConBy = pConBy
End Property
Public Property Let ConBy(Value As String)
    pConBy = Value
End Property

Public Property Get Prod() As String
    Prod = pProd
End Property
Public Property Let Prod(Value As String)
    pProd = Value
End Property

Public Function AddProd(Value As String)
    On Error Resume Next
    pProds.Add Value, CStr(Value)
    On Error GoTo 0
End Function

Public Property Get Prods() As Collection
    Set Prods = pProds
End Property

Regular Module


Option Explicit
Sub UniqueConBy()
    Dim cCB As cConBy, colCB As Collection
    Dim wsSrc As Worksheet, wsRes As Worksheet, rRes As Range
    Dim vSrc As Variant, vRes() As Variant
    Dim I As Long, J As Long, K As Long
    Dim lRowCount As Long

'Source and results location
Set wsSrc = Worksheets("Sheet1")
Set wsRes = Worksheets("Sheet1")
    Set rRes = wsRes.Cells(1, 5)
With wsSrc
    vSrc = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(columnsize:=2)
End With

'Collect and consolidate the data
Set colCB = New Collection
On Error Resume Next
For I = 2 To UBound(vSrc, 1)
    Set cCB = New cConBy
    With cCB
        .ConBy = vSrc(I, 1)
        .Prod = vSrc(I, 2)
        .AddProd .Prod
        lRowCount = lRowCount + 1
        colCB.Add cCB, CStr(.ConBy)
        Select Case Err.Number
            Case 457
                With colCB(CStr(.ConBy))
                    lRowCount = lRowCount - .Prods.Count - 1
                    .AddProd cCB.Prod
                    lRowCount = lRowCount + .Prods.Count
                End With
                Err.Clear
            Case Is <> 0
                MsgBox "Error: " & Err.Number & vbTab & Err.Description
                Stop
        End Select
    End With
Next I
On Error GoTo 0

'Create results array
ReDim vRes(0 To lRowCount, 1 To 2)

'column labels
For I = 1 To UBound(vRes, 2)
    vRes(0, I) = vSrc(1, I)
Next I

'populate the array
For I = 1 To colCB.Count
    With colCB(I)
        K = K + 1
        vRes(K, 1) = .ConBy
        vRes(K, 2) = .Prods(1)
        For J = 2 To .Prods.Count
            K = K + 1
            vRes(K, 2) = .Prods(J)
        Next J
    End With
Next I

Set rRes = rRes.Resize(UBound(vRes, 1) + 1, UBound(vRes, 2))
With rRes
    .EntireColumn.Clear
    .Value = vRes
    With .Rows(1)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    .EntireColumn.AutoFit
End With

End Sub

EDIT:

An alternative method, which comes close to what you want but gives a slightly different output, would be to simply use the Remove Duplicates option on the Data Ribbon / Data Tools Tab. You would select both columns A and B.

Ensure your data is sorted before applying this method (sorting would not be necessary using the VBA method).

With your posted data, the results would look like:

enter image description here

You could use conditional formatting to eliminate the duplicate entries in Column A. eg: use a Formula of =$A2=$A1 and format the text color to be the same color as the background. The Con.By value would still be there, but would not be visible.

enter image description here

  • Thank you for your response, but I am seriously overwhelmed by it. So much so that I am not even able to test it. Here I was expecting a formula on the lines of Remove duplicates for each unique value in a column. This is the most VBA code I have seen at a time. I am only highlighting my incompetence in coding. – Achyutha Mohan Oct 15 '15 at 20:18
  • @AchyuthaMohan Not quite sure what to do with your response. I will post an alternative method, but it won't give you quite the output you want. – Ron Rosenfeld Oct 16 '15 at 2:15
0

Try the following recipe, which I think is easier to understand but perhaps not as automation friendly as Ron's answer.

  1. Assuming Con.By is in column A and Prod is in column B, in a another column (say C), concatenate the two columns with a separator e.g. "_":

    =A2&"_"&B2 which is the same as =CONCATENATE(A2,"_",B2)

  2. Using your example the output will be like A_1 etc. Copy column C and Paste Values only into column D.

  3. Highlight Column D and using the ribbon menu select Data -> Remove Duplicates. Column D will look like: A_1 A_2

  4. To split the data back into two separate columns, use the ribbon menu and select Data -> Text to Columns. Choose `Delimited

0

Try the following recipe, which I think is easier to understand but perhaps not as automation friendly as Ron's answer.

  1. Assuming Con.By is in column A and Prod is in column B, in a another column (say C), concatenate the two columns with a separator e.g. "_":

    =A2&"_"&B2 which is the same as =CONCATENATE(A2,"_",B2)

  2. Using your example, the output will be like A_1 etc. Copy column C and Paste Values only into column D.

  3. Highlight Column D and using the ribbon menu select Data -> Remove Duplicates. Column D will look like: A_1 A_2

  4. To split the data back into two separate columns, use the ribbon menu and select Data -> Text to Columns. Choose Delimited as the first option and Other as the second. Your delimiter is _ in this case.

This will give you results close to what you want.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.