如何基于Excel中的多个条件从列中提取所有唯一值 [英] How to extract all unique values from a column based on multiple criterion in Excel
问题描述
我试图基于vlookup类型匹配从列中获取所有不同值的列表.
I am trying to get a list of all the distinct values from a column based on a vlookup type match.
例如:
工作表1:
(colA) (colB) (colC)
Health System Name EMR PMR
System A
System B
System C
System D
第2张(所有数据都存放在这里)
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
我希望能够从工作表2的colA的工作表1的colA中搜索健康系统的名称...并基于它是PMR还是EMR ...从供应商列在工作表1中相应列下的一个单元格中.
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.
对于系统A,工作表1的EMR列下的SO,我希望工作表2的供应商列中的值与系统A的技术ID为"EMR"不同.
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.
在这种情况下,它将是:ClinicA,ClinicE
In this case it would be : ClinicA, ClinicE
任何帮助将不胜感激!
推荐答案
您将无法仅使用excel公式来执行此操作,并且需要VBA解决方案.如果您的Sheet1
包含以下数据,
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,
和Sheet2
尝试这个简单的VBA代码,
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
您的输出将是
这篇关于如何基于Excel中的多个条件从列中提取所有唯一值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!