CODEWITHSUNDEEP
excelvba
devuser
devuser

Reputation: 171

Compare two columns if another two columns are matching in excel macro

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

Upvotes: 1

Views: 13872

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

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:

  1. The main idea is to determine result with formula and then rewrite formula with its result value.
  2. 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 returns True if we found, say A2 and B2 in any row of range C2:D6, e.g. C3 and D3.
  3. .Value = .Value part rewrites formulas with theirs results
  4. How it works? Formula concatenates columns A and B and searches result in concatenation of columns C and D.
  5. 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.

Upvotes: 1

mcalex
mcalex

Reputation: 6798

  1. Does it need to be in vba? 2. What do you do to show there is a match or half-match?

Create a new column in F that is a concatenation of A & B. Do the same in column G to concatenate the values in C & D.

Then use a formula that uses VLookup to see if looking up the concatenation of A & B returns an 'N/A' error. If this is false, you have a matching column.

So, the formula for F2 is 

'=concatenate(A2, B2)'

the formula for G2 is 

'=concatenate(C2, D2)'

and the formula to do your vlookup is 

'=IF(ISNA(Vlookup(concatenate(a2, b2), $F$2:$G$<LastRowOfData>, 2, FALSE)), "", "Matches")

If it needs to be in vba, you could set up a macro to perform these steps.

Upvotes: 0

Related Questions