vba 根据另一列vba excel中的条件复制唯一列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13049817/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy unique list based on criteria in another column vba excel
提问by Nico Nick
I want to use VBA to extract a unique, ordered list, subject to conditions in another column. So, I have two columns A, B.
我想使用 VBA 提取一个唯一的有序列表，受另一列中的条件约束。所以，我有两列A，B。
A B
========
a FALSE
b FALSE
c TRUE
a TRUE
b FALSE
c TRUE
Should result in a list
应该导致一个列表
C
==
a
c
I'm very, very new to VBA, so any help would be appreciated.
我对 VBA 非常非常陌生，所以任何帮助将不胜感激。
Oh, and the second list will be updated with every change to the first, so needs to be wiped to ensure there are no leftovers, if, for example, the second "a" is set to FALSE.
哦，第二个列表将随着第一个列表的每次更改而更新，因此需要擦除以确保没有剩余，例如，如果第二个“a”设置为 FALSE。
采纳答案by Trace
What do you think of this? Add the MS Scripting library first.
你觉得这怎么样？首先添加 MS 脚本库。
Option Explicit
选项显式
Sub Test()
Dim oRange As Range
Dim dict As Dictionary
Dim vArray As Variant
Dim vItem As Variant
Dim sKey As String
Dim sValue As String
Dim iCompare_TRUE As Integer
Dim lCnt As Long
Dim lCnt_Rows As Long
Set dict = New Dictionary
Set oRange = ThisWorkbook.Sheets(1).Range("A1:B6")
For lCnt = 1 To oRange.Rows.Count
sKey = oRange(lCnt, 1)
sValue = oRange(lCnt, 2)
iCompare_TRUE = StrComp(sValue, "True")
If Not dict.exists(sKey) And iCompare_TRUE = 0 Then
With dict
.Add sKey, sValue
End With
End If
Next lCnt
ReDim vArray(1 To dict.Count)
vArray = dict.Keys
lCnt_Rows = UBound(vArray) + 1
Set oRange = ThisWorkbook.Sheets(1).Range(Cells(1, 3), Cells(lCnt_Rows, 3))
oRange.Value = Application.Transpose(vArray)
End Sub
回答by Tim
Here's a formula-only approach. Whether it's practical depends on your circumstances, but I have tested it with a sample of data similar to the one in the original question:
这是一种仅限公式的方法。它是否实用取决于您的情况，但我已经使用类似于原始问题中的数据样本对其进行了测试：
- Insert a blank row at the top of the spreadsheet to serve as a header row.
- Create a new formula column that will list the elements of column "A" only if column "B" is true. For example, place the following formula in cell D2, then copy it down:
=IF(B2,A2,""). - Now you can apply the technique described in the second page linked by t.thielemans above.
- 在电子表格的顶部插入一个空白行作为标题行。
- 创建一个新的公式列，该列仅在“B”列为真时才列出“A”列的元素。例如，在放置电池D2下面的公式，然后复制下来：
=IF(B2,A2,"")。 - 现在您可以应用上面 t.thielemans 链接的第二页中描述的技术。
One potential disadvantage of this approach is that the blank cells returned by the formula when column B is "FALSE" don't disappear--you'll still have a blank result in your filtered view.
这种方法的一个潜在缺点是，当 B 列为“FALSE”时，公式返回的空白单元格不会消失——您的过滤视图中仍然会有空白结果。
I'll copy the reference here for convenience: Getting unique values in Excel by using formulas only
为方便起见，我将在此处复制参考： 仅使用公式在 Excel 中获取唯一值
