How to extract all unique values from a column bas

问题:

I am trying to get a list of all the distinct values from a column based on a vlookup type match.

For example:

Sheet 1:

      (colA)         (colB)   (colC)
Health System Name    EMR     PMR 

System A       
System B
System C
System D

Sheet 2 (where all the data is)

Healthy System Name        Tech ID      Vendor

System A                 PMR         ClinicA
System A                 EMR         ClinicE
System A                 EMR         ClinicA
System B                 EMR         ClinicB
System B                 PMR         ClinicC
System C                 PMR         ClinicA
System C                 PMR         ClinicB  
System C                 EMR         ClinicD
System C                 PMR         ClinicD
System C                 EMR         ClinicG

I want to be able to search the name of the healthy system from colA in sheet 1 in colA of Sheet 2...and based on whether it is a PMR or EMR...return the number of unique values from the Vendor column into one cell in sheet 1 under the appropriate column.

SO under the EMR column in Sheet 1 for System A, I want the distinct values from the vendor column in sheet 2 that have the tech ID as "EMR"for System A.

In this case it would be : ClinicA, ClinicE

Any help would be greatly appreciated!

回答1:

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,

and Sheet2,

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,

回答2:

If you have the new TEXTJOIN function then enter this as an array formula with CSE.

=TEXTJOIN(", ", TRUE, IF(Sheet2!$A$2:$A$11=$F7, IF(Sheet2!$B$2:$B$11=G$6, Sheet2!$C$2:$C$11, ""), ""))

Fill right and down.

imgur seems broken right now. http://imgur.com/a/xCqQb

If you do not have access to the newer textjoin worksheet function search this site for responses to questions with the textjoin for alternatives.