如果另外两列在excel宏中匹配,则比较两列 [英] Compare two columns if another two columns are matching in excel macro
问题描述
如果另外两列匹配,我想比较Excel中的两列。
ABCD
$ 10 1234D $ 40 100D
$ 20 1235D $ 10 1234D
$ 30 122D $ 20 1235D
$ 40 1222D $ 30 1222D
首先我需要比较col A和C如果在col C中找到匹配项,那么需要比较B和D是匹配的。例如,我有$ 10在A,其ID为1234D.i需要在C中比较相同的值。如果我在C中找到$ 10,但其ID不是1234D,我需要显示在另一列中不匹配。
我可以像下面这样匹配A和C,但是我在比较B和D之后感到困惑,我是新来的vba,欣赏如果有人帮我
函数Find_Matches()
Dim CompareRange As Variant,SelectionRange As Variant,x As Variant,y As Variant
'比较选择。
表格(菜单)激活
设置SelectionRange =范围(A2:A6)
设置CompareRange =范围(C2:C6)
'循环选择中的每个单元格,并将其与CompareRange中的每个单元格比较为
'。
对于每个x在SelectionRange
对于每个y在CompareRange
如果x = y然后x.Offset(0,4)= True
下一个y
下一个x
结束函数
blockquote>
我需要以任何方式使用vba。
使用此宏:
Sub Find_Matches()
Dim rng As Range
Set rng = Sheets(Menu)。Range A2:A6)
将rng.Offset(,4)'在列E
.FormulaArray == ISNUMBER(MATCH(& rng.Address&) &|&& _
rng.Offset(,1).Address&,& rng.Offset(,2).Address& _
&|&& rng.Offset(,3).Address&,0))
.Calculate
.Value = .Value
End With
End Sub
它写入结果( True
或 False
in列 E
)。
说明:
- 主要思想是用公式确定结果,然后用其结果值重写公式。
- 我们所做的是在
E2:E6
数组公式
= ISNUMBER(MATCH($ A $ 2:$ A $ 6& | &安培; $ B $ 2:$ B $ 6,$ C $ 2:$ C $ 6& | &安培; $ D $ 2:$ D $ 6,0))
- 如果我们发现A2 $,则返回
和True
c $ c>和B2
在任何范围的行C2:D6
,例如C3D3
。 -
.Value = .Value
部分用他们的结果重写公式 - 它是如何工作的?公式连接列
A
和B
,搜索会导致列的连接C
和D
。 - 为什么我们使用
& | &
在公式中?想象下面的说明:
ABCD
$ 101 234D $ 10 1234D
技术上,级联 A1& B1
和 C1& D1
给您相同的结果: $ 101234D
,但我们清楚地看到没有匹配。这就是为什么当连接值时,我使用 |
作为分度计的原因: A1& | &安培; B1
返回 $ 101 | 234D
和 C1& | &安培; D1
返回 $ 10 | 1234D
,它们不一样,因为我们需要。
I want to compare two columns in excel if another two columns are matching.
A B C D
$10 1234D $40 100D
$20 1235D $10 1234D
$30 122D $20 1235D
$40 1222D $30 1222D
First I need to compare col A and C If any matches find in col C then I need to compare B and D are matching. Example I have $10 in A and its ID is 1234D.i need to compare the same value in C. If I found $10 in C but its id is not 1234D I need to show that un matching one in another column.
I can match A and C as below .but I'm confusing on how to compare B and D after that?i'm new to excel vba and appreciate if any one help me to do this.
Function Find_Matches()
Dim CompareRange As Variant, SelectionRange As Variant, x As Variant, y As Variant
' compare the selection.
Sheets("Menu").Activate
Set SelectionRange = Range("A2:A6")
Set CompareRange = Range("C2:C6")
' Loop through each cell in the selection and compare it to
' each cell in CompareRange.
For Each x In SelectionRange
For Each y In CompareRange
If x = y Then x.Offset(0, 4) = True
Next y
Next x
End Function
I need to use vba any way.
Use this macro:
Sub Find_Matches()
Dim rng As Range
Set rng = Sheets("Menu").Range("A2:A6")
With rng.Offset(, 4) ' write result in column E
.FormulaArray = "=ISNUMBER(MATCH(" & rng.Address & "&""|""&" & _
rng.Offset(, 1).Address & "," & rng.Offset(, 2).Address & _
"&""|""&" & rng.Offset(, 3).Address & ",0))"
.Calculate
.Value = .Value
End With
End Sub
it writes result (True
or False
in column E
).
Explanation:
- The main idea is to determine result with formula and then rewrite formula with its result value.
- What we do is writing in
E2:E6
array formula
=ISNUMBER(MATCH($A$2:$A$6 & "|" & $B$2:$B$6,$C$2:$C$6 & "|" & $D$2:$D$6,0))
- it returnsTrue
if we found, sayA2
andB2
in any row of rangeC2:D6
, e.g.C3
andD3
. .Value = .Value
part rewrites formulas with theirs results- How it works? Formula concatenates columns
A
andB
and searches result in concatenation of columnsC
andD
. - Why we're using
& "|" &
in formula? Imagine following situaltion:
A B C D
$101 234D $10 1234D
technically, concatenation of A1 & B1
and C1 & D1
gives you the same result: $101234D
, but we clearly see that there is no match. That's why I'm using |
as delimeter when concatenating values: A1 & "|" & B1
returns $101|234D
and C1 & "|" & D1
returns $10|1234D
and they are not the same, as we need it.
这篇关于如果另外两列在excel宏中匹配,则比较两列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!