[Solved] How to extract all unique values from a column based on multiple criterion in Excel

by


You would not be able to do this only with excel formulas and you would need a VBA solution. If your Sheet1 contains data like below,

enter image description here

and Sheet2,

enter image description here

Try this simple VBA code ,

Sub uniqueList()
Dim i As Long, j As Long, str As String
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    For j = 2 To Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(i, 1) = Sheets("Sheet2").Cells(j, 1) And Cells(1, 2) = Sheets("Sheet2").Cells(j, 2) Then
            If Cells(i, 2) <> "" Then
                str = Cells(i, 2) & " , " & Sheets("Sheet2").Cells(j, 3)
                Cells(i, 2) = str
            Else
                Cells(i, 2) = Sheets("Sheet2").Cells(j, 3)
            End If
        End If
        If Cells(i, 1) = Sheets("Sheet2").Cells(j, 1) And Cells(1, 3) = Sheets("Sheet2").Cells(j, 2) Then
            If Cells(i, 3) <> "" Then
                str = Cells(i, 3) & " , " & Sheets("Sheet2").Cells(j, 3)
                Cells(i, 3) = str
            Else
                Cells(i, 3) = Sheets("Sheet2").Cells(j, 3)
            End If
        End If
    Next j
Next i
End Sub

Your output would be,

enter image description here

solved How to extract all unique values from a column based on multiple criterion in Excel