VBA data compare

[newbie777]

Hi all,

this is my first post so pardon me if it is not complete. You can ask me for more info if needed.

So i have basically data in consecutive rows that i need to compare with data in a column

If the data of each cell in each row exists in the column, then i need the row to be red. Obviously, if one missing cell, i need it to be green.

The data that i need to check is from C10:BN300
The column that i need to check against is BX:BX

Please note that i need to check each cell in each row against this column as every row is treated. And the aim is to check if this row is all good or not.


Can anyone please help me with this?

 

[newbie777]

Hi mate,

Just one more question for my educational purpose. So it is not really necessary unless you like writing scripts and educating others. I have the same template but with a slightly different design. The list of the stock exchanges are in one cell separated by one comma. I heard from one colleague that it would be easier and better to use array with this structure rather than the design i sent you.

I added the document to the below path, is it something you can do? I just want to know how to do it this way.

https://www.dropbox.com/s/9r205iim138ijkf/Test2.xlsx?dl=0

Many thanks,

 

[Nine Zero]

Yeah no problem, I enjoy doing this because it helps expand my own knowledge.

I have looked at the second file you sent me and this code should work.

This code will
1. Look through to see how many cells are used in C column
2. Loop through each cell and split it into an array at the comma
3. then it loops once again through the array and looks through the E column and looks to see if it can find a match at least once increasing a counter by 1
4. then it compares the final counter to the size of the array
5. if its a match, that means it found every single one, therefore you wil have a "True" if not then its "False"

Sub Newbie777()
Dim StockArray() As String
Dim lrow, arrSize, counter As Integer
Dim r As Range
Dim e As Variant


lrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row
For Each r In Range("C2:C" & lrow)
    counter = 0
    StockArray = Split(r.Value, ",")
    arrSize = UBound(StockArray) + 1
    'Debug.Print (arrSize)
        For Each e In StockArray
            If Application.WorksheetFunction.CountIf(Range("E:E"), e) > 0 Then
            counter = counter + 1
            End If
        Next e
    If counter = arrSize Then
        r.Offset(, 1).Value = "True"
    Else
        r.Offset(, 1).Value = "False"
    End If
Next r
End Sub

Let me know if you have any questions

 

[newbie777]

Apologies for my late reply. I traveled for work and just came back.

Yes it makes sense to me now. And I understood the code!

Many thanks for the help!

 

[newbie777]

Hi Nine zero,

I have another task if you can please + explanation I will really appreciate it.

I have two columns in two sheets (4 columns). Basically what i want to do is to compare between the concatenation of the two columns in each sheets.

So compare A2&B2 in sheet 1 with A2&B2 in sheet 2.

Whatever in sheet1 and not in sheet two, we copy A2 in sheet3 and B2 in sheet 3 and in the cell next to it add
Whatever in sheet2 and not in sheet one, we copy A2 in sheet3 and B2 in sheet3 and in the cell next to it drop.

All under each other.

In the below link a live example so it would be more clear.

Many thanks in advance.
https://www.dropbox.com/s/iqrpjj54ouca7ns/Adds and drops.xlsx?dl=0


Hassan