Matching data on multiple sheets

[LostinVA]

Hi All -

Is there a way to compare data in a column of one workbook tab with data on another tab (multiple columns) to see if there are ANY matches in the string of data??

For example:

Sheet 1, Cell A1: "Red, Yellow, Car, Truck"
Sheet 2, Column A is "Color" and Column B is "Vehicle Type"
- Column A contains the value "Red" in cell A15
- Column B contains the value "Car" in cell B5 and the value "Truck" in cell B10.

So on the first tab, is there a way to identify that "Red", "Car", and "Truck" were a match to data from Sheet 2?

My apologies if this doesn't quite make sense, I'm having a hard time describing my ask.

Appreciate any help you all can provide. Thank you!

 

[JLGWhiz]

This is based on your narrative of your objective.

Sub t()
Dim ary As Variant, i As Long, fn
ary = Split(Sheets("Sheet1").Range("A1").Value, ",")
    For i = LBound(ary) To UBound(ary)
        Set fn = Intersect(Sheets("Sheet2").UsedRange, Sheets("Sheet2").Range("A:B")).Find(Trim(ary(i)), , xlValues, xlWhole)
            If Not fn Is Nothing Then fn.Interior.Color = vbYellow
    Next
End Sub

It will find matches for the strings listed in cell A1 of sheet 1 by looking in columns A & B of sheet 2. If found the cell will be colored yellow. You would then need to manually remover the color when you have finished that session so it will not be misleading in future sessions.

 

[LostinVA]

Hi! Thanks for your reply.

I added the code and modified slightly to update tab names and extend the range on Sheet 2 and there weren't any yellow highlights so not sure it worked as intended? To modify this slightly, is there a way I could have any 'matches' between Sheet 1, cell A1 and data in Sheet 2 be listed on Sheet 1, cell B1? So instead of highlighting matches yellow, the actual matches would be listed?

Appreciate your help!

 

[JLGWhiz]

Hi! Thanks for your reply.

I added the code and modified slightly to update tab names and extend the range on Sheet 2 and there weren't any yellow highlights so not sure it worked as intended? To modify this slightly, is there a way I could have any 'matches' between Sheet 1, cell A1 and data in Sheet 2 be listed on Sheet 1, cell B1? So instead of highlighting matches yellow, the actual matches would be listed?

Appreciate your help!

Post your current code please.

 

[LostinVA]

It's what you provided but changed the Sheet names and extended the range on the second sheet to A:L
I have zip for experience so I'm sure I completely missed something.

Sub t()
Dim ary As Variant, i As Long, fn
ary = Split(Sheets("ENNI_list").Range("A1").Value, ",")
For i = LBound(ary) To UBound(ary)
Set fn = Intersect(Sheets("Interconnects").UsedRange, Sheets("Interconnects").Range("A:L")).Find(Trim(ary(i)), , xlValues, xlWhole)
If Not fn Is Nothing Then fn.Interior.Color = vbYellow
Next
End Sub

 

[Sharid]

See this post of mine, i use it to match from one sheet to another, code was done by Fluff. Link to my post on Mr Excel

 

[LostinVA]

Thanks Sharid! I'm an extreme amateur.. I barely know how to enter the code in VBA. Would you be able to modify your code to fit my data?

First tab - 'ENNI_list' with data in column A
Second tab - 'Interconnects' with data in columns A - L

I would like to take the cell data from the first tab in A1 and identity any "matches" with data from the second tab, all cells in columns A - L. Any "matches" would be listed on the first tab, cell B1

I believe the code that JLGWhiz provided was going to highlight any "matches" yellow on the second tab. I'm hoping to have them listed in the first tab (B1). Not sure if that's possible or not.

Thank you!

 

[JLGWhiz]

this worked for two sheets in the same workbook. If your sheets are in different workbooks, then the workbook reference has to precede the sheet reference to distinguish which is which.

Sub t2()
 Dim ary As Variant, i As Long, fn As Range, rng As Range
 ary = Split(Sheets("ENNI_list").Range("A1").Value, ",")
 Set rng = Sheets("ENNI_list").Range("B1")
    For i = LBound(ary) To UBound(ary)
        Set fn = Intersect(Sheets("Interconnects").UsedRange, Sheets("Interconnects").Range("A:L")).Find(Trim(ary(i)), , xlValues, xlWhole)
        If Not fn Is Nothing Then rng = rng.Value & ", " & fn.Value
    Next
rng = Mid(rng.Value, 3)
 End Sub

 

[Sharid]

I'm no expert, I have made some changes to the code, you can download From this link. I have changed some of Fluffs code, hope it helps.

I have also added comments to the code have a read, if you want to make changes

 

[Sharid]

I don't want to hijack this post, but is it possible to highlight via colour on sheet two what is NOT matched, as that was a requirement i believe. I could not do this as it was out of my depth and on a personal reason, I would want this as it matches records but its hard to work out what was not matched especially if you have a large data set.