Compare multiple Excel columns

Question

Compare multiple Excel columns

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

From the following previous image I want to create a macro that does the following:

What I want is to make a cycle that runs through the rows of my data: compare row1 of column A with row1 of column G and compare row1 of column B with row1 of column I if it is true stores the value of row1 of column H in a variable

and advance to the next row in columns G, H, I. Now make the same comparison row1 A with row 2 G and row 1 B with row2 I and if true add the value of column H above with the current column and so on.

That is to say

-8403 = -8403 AND 1 = 1 Then var = 9
-8403 = -8403 AND 1 = 1 Then var = 9 + 11

I have the following code that compares the stocks in column A in column G and it works perfectly but now I want to compare more columns.

Sub ObtenerCoincidencias()
    Dim ClaveCat As Range
    Dim ClaveExi As Range

    Dim x As Object
    Dim y As Object


    Set ClaveExi = Range("A2:A18")
    Set ClaveCat = Range("G2:G350")


    For Each x In ClaveExi
        For Each y In ClaveCat
            If x = y Then
            y.Interior.Color = RGB(255, 204, 0)
            End If
        Next y
    Next x
End Sub

Some suggestions to improve my code and achieve the task I require, thank you in advance.

vba excel

asked by Silvestre Silva 21.02.2018 в 23:23

source

2 answers

2

There is another solution less complicated than VBA and the full power of Excel and Relational Databases is used.

Convert each table as data as Format as Table .

Create a id field by concatenating [CLAVE]&[NUM ALMACEN] in each table.

Create a relationship between tables using [id] as a common key of the two tables.

Insert a dynamic table and play with its data: D

answered by 22.02.2018 в 01:52

When I run the "help" command in Git Bash I get the error "cmd: command not found" Android: Error in requestAudioFocus ()

score 1 · Accepted Answer

I hope I have understood correctly.

I did the tour in a different way so that your data ranges are not fixed.

With the data extract of the image that I attached, the result of the variable (var) that is increasing gave 20.

Here the code:

Sub ObtenerCoincidencias()

Dim ultimaFilaIzq As Integer
Dim ultimaFilaDer As Integer
Dim i As Integer
Dim d As Integer
Dim var As Integer

var = 0
ultimaFilaIzq = Range("A" & Rows.Count).End(xlUp).Row
ultimaFilaDer = Range("G" & Rows.Count).End(xlUp).Row

For i = 1 To ultimaFilaIzq

    For d = 1 To ultimaFilaDer

        If Range("A" & i).Value = Range("G" & d).Value And Range("B" & i).Value = Range("I" & d).Value Then
            var = var + Range("H" & d).Value
        End If

    Next d

Next i
MsgBox var

End Sub