Compare two ranges in Excel to see if they match exactly

R

rachel06

Board Regular
Joined
Feb 3, 2016
Messages
148
Office Version
  1. 365
Platform
  1. Windows
Hello,

Hope this is my last post for a while. I'm trying to come up with a formula to tell me if two ranges in Excel are match exactly. Everything I'm trying is bringing a result for each individual row in the range, but I'm looking for something that would compare something like A1:A10 to B1:B10 to see if the lists match exactly, and give me a yes or a no, in only the cell the formula is in.

Note that this is part of a macro where I'm bringing the list from a pricing sheet to my master spreadsheet where I need to determine what list out of 7 or so it's on, and I have hundreds of these each month, so it isn't as easy as two columns side by side like in my example :) Essentially, I'm aiming for a formula that'll have a bunch of IFs in it until it finds the list that matches.

Is there a function in Excel for this?
 
Sort by date Sort by votes
Hmm. It's not working for me, but I get what it's trying to do. I'm going to play around with it for a bit. Thanks so much!!
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Actually, this really got me going in a good direction. The TEXTJOIN was definitely the way to go. Thanks so much!!
 
Upvote 0
Hi again!

I set up some formulas within the workbook so that when the new language is copied in, it just recalculates- thanks again for that part! I wouldn't have gotten there without your help!

Now the part I'm stuck on is this - how to I add this formula to my loop? It'd need to be in Column B next to the file name which is brought in the first part of the below code.

This is the formula:
=XLOOKUP(TRUE,'Rate Sheet Language'!P:P,'Rate Sheet Language'!O:O)

I know I'll need a Paste Values in there as well, that part is easy though! I'm just struggling to add it so it works like the file name being brought in, automatically going to the next row in the column.

Code: 
Sub ListAllFiles()

Dim MyPath As String
Dim MyFile As String
Dim wb As Workbook
Dim FldrPicker As FileDialog
Dim sh As Worksheet
Dim i As Integer

'''Return File Name'''

Set sh = ThisWorkbook.Sheets("Output")
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
        .Title = "Please Select Folder"
        .AllowMultiSelect = False
        .ButtonName = "Select"
        If .Show = -1 Then
            MyPath = .SelectedItems(1) & "\"
        Else
            End
       End If
    End With
       
MyFile = Dir(MyPath)
i = 2

Do While MyFile <> ""

  
    sh.Cells(i, 1) = MyFile
    MyFile = Dir
    i = i + 1
    
'''Find Exclusion Language and Copy to Main Workbook'''

    'Open File
    Set wb = Workbooks.Open(fileName:=MyPath & MyFile)
    
    'Ensure Workbook has opened before moving on to next line of code
     DoEvents
     
 
'''Copy Exclusion Language from Rate Sheet'''
    Cells.Find(What:="calculation of the", After:=ActiveCell, LookIn:= _
        xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        Range(Selection, Selection.End(xlDown)).Copy
    
'''Paste Exclusion Language into Main  Workbook'''
    Windows("UMR EXCLUSION LANGUAGE.xlsm").Activate
    Sheets("Rate Sheet Language").Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Selection.UnMerge
    
'''Text to Columns / Delete Blank Rows and Bottom Row'''
    
    Sheets("Rate Sheet Language").Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        
    Sheets("Rate Sheet Language").Cells(Rows.Count, "A").End(xlUp).EntireRow.Delete
    Sheets("Rate Sheet Language").Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
        
        
'''''FORMULA FOR LOOKUP HERE'''''
        
         
'''Clear Rate Sheet Language'''
    'Sheets("Rate Sheet Language").Range("A1:A27").Clear
      


wb.Close SaveChanges:=False
    
Loop

End Sub
 
Upvote 0
rachel06 said:
Hi again!

I set up some formulas within the workbook so that when the new language is copied in, it just recalculates- thanks again for that part! I wouldn't have gotten there without your help!

Now the part I'm stuck on is this - how to I add this formula to my loop? It'd need to be in Column B next to the file name which is brought in the first part of the below code.

This is the formula:
=XLOOKUP(TRUE,'Rate Sheet Language'!P:P,'Rate Sheet Language'!O:O)

I know I'll need a Paste Values in there as well, that part is easy though! I'm just struggling to add it so it works like the file name being brought in, automatically going to the next row in the column.

Code: 
Sub ListAllFiles()

Dim MyPath As String
Dim MyFile As String
Dim wb As Workbook
Dim FldrPicker As FileDialog
Dim sh As Worksheet
Dim i As Integer

'''Return File Name'''

Set sh = ThisWorkbook.Sheets("Output")
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
        .Title = "Please Select Folder"
        .AllowMultiSelect = False
        .ButtonName = "Select"
        If .Show = -1 Then
            MyPath = .SelectedItems(1) & "\"
        Else
            End
       End If
    End With
      
MyFile = Dir(MyPath)
i = 2

Do While MyFile <> ""

 
    sh.Cells(i, 1) = MyFile
    MyFile = Dir
    i = i + 1
   
'''Find Exclusion Language and Copy to Main Workbook'''

    'Open File
    Set wb = Workbooks.Open(fileName:=MyPath & MyFile)
   
    'Ensure Workbook has opened before moving on to next line of code
     DoEvents
    
 
'''Copy Exclusion Language from Rate Sheet'''
    Cells.Find(What:="calculation of the", After:=ActiveCell, LookIn:= _
        xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        Range(Selection, Selection.End(xlDown)).Copy
   
'''Paste Exclusion Language into Main  Workbook'''
    Windows("UMR EXCLUSION LANGUAGE.xlsm").Activate
    Sheets("Rate Sheet Language").Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Selection.UnMerge
   
'''Text to Columns / Delete Blank Rows and Bottom Row'''
   
    Sheets("Rate Sheet Language").Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
       
    Sheets("Rate Sheet Language").Cells(Rows.Count, "A").End(xlUp).EntireRow.Delete
    Sheets("Rate Sheet Language").Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
   
       
       
'''''FORMULA FOR LOOKUP HERE'''''
       
        
'''Clear Rate Sheet Language'''
    'Sheets("Rate Sheet Language").Range("A1:A27").Clear
     


wb.Close SaveChanges:=False
   
Loop

End Sub
Click to expand...
Are you able to format the Lookup sheet like this?

Compare two ranges in Excel to see if they match exactly.xlsm
AB
2R_1Apples
3R_1Grapes
4R_1Oranges
5R_1Bananas
6R_1Cookies
7R_1Milk
8R_1Shirts
9R_1Shoes
10R_1Paper
11R_1Pencils;
12R_1Cereal
13R_1Markers
14R_1Shampoo
15R_1Soap
16R_1Towels
17R_1Globes
18R_2Apples
19R_2Grapes
20R_2Oranges
21R_2Bananas
22R_2Cookies
23R_2Milk
24R_2Vitamins
25R_2Paper
26R_2Pencils
Sheet6
 
Upvote 0
rachel06 said:
Do you mean have the Range in A and the File Name in B?
Click to expand...
Now that I have the code I can do it VBA. I can't test it fully though because I don't have your files.

'''''FORMULA FOR LOOKUP HERE'''''
sh.Cells(i-1, 2) = fncFindRange

Just add the second line above into you code where shown and the following function under your procedure.

This code puts the "Rate Sheet Language" data into an array and then the data from the named ranges into an array and then loops through each named range data array element
and compares it to the corresponding "Rate Sheet Language" array element. It only does this though if the sizes of both arrays are the same.
It writes the named range name next to the file name if the two arrays match.

IMPORTANT Take note of this comment in the code .
' << Change this 3 to the number of named ranges that you have.
Fingers crossed.

VBA Code: 
Private Function fncFindRange() As String
Dim arr1() As Variant
Dim arr2() As Variant
Dim i As Integer
Dim n As Integer
Dim blnOK As Boolean

  With Worksheets("Rate Sheet Language")
    arr1 = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
  End With
          
  For n = 1 To 3  ' << Change this 3 to the number of named ranges that you have.
      
    blnOK = True
    
    arr2 = Range("R_" & n).Value
    
    If UBound(arr1) = UBound(arr2) Then
       
      For i = 1 To UBound(arr1)
      
        If arr1(i, 1) <> arr2(i, 1) Then
          blnOK = False
        End If
 
      Next i
      
      If blnOK Then
        fncFindRange = "R_" & n
        Exit Function
      End If
   
   End If
      
  Next n
  
  fncFindRange = ""

End Function
 
Upvote 0
HighAndWilder said:
Now that I have the code I can do it VBA. I can't test it fully though because I don't have your files.

'''''FORMULA FOR LOOKUP HERE'''''
sh.Cells(i-1, 2) = fncFindRange

Just add the second line above into you code where shown and the following function under your procedure.

This code puts the "Rate Sheet Language" data into an array and then the data from the named ranges into an array and then loops through each named range data array element
and compares it to the corresponding "Rate Sheet Language" array element. It only does this though if the sizes of both arrays are the same.
It writes the named range name next to the file name if the two arrays match.

IMPORTANT Take note of this comment in the code .
' << Change this 3 to the number of named ranges that you have.
Fingers crossed.

VBA Code: 
Private Function fncFindRange() As String
Dim arr1() As Variant
Dim arr2() As Variant
Dim i As Integer
Dim n As Integer
Dim blnOK As Boolean

  With Worksheets("Rate Sheet Language")
    arr1 = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
  End With
         
  For n = 1 To 3  ' << Change this 3 to the number of named ranges that you have.
     
    blnOK = True
   
    arr2 = Range("R_" & n).Value
   
    If UBound(arr1) = UBound(arr2) Then
      
      For i = 1 To UBound(arr1)
     
        If arr1(i, 1) <> arr2(i, 1) Then
          blnOK = False
        End If
 
      Next i
     
      If blnOK Then
        fncFindRange = "R_" & n
        Exit Function
      End If
  
   End If
     
  Next n
 
  fncFindRange = ""

End Function
Click to expand...
I've gotten it all to work but at the end when it gets to the last file, I get an error.

"Run-Time Error 1004

Sorry we couldn't find "source folder path," Is it possible it was moved, renamed or deleted?"

Not sure why it'll go through all the files until the last one,
 
Upvote 0
rachel06 said:
I've gotten it all to work but at the end when it gets to the last file, I get an error.

"Run-Time Error 1004

Sorry we couldn't find "source folder path," Is it possible it was moved, renamed or deleted?"

Not sure why it'll go through all the files until the last one,
Click to expand...
Had to eat.

Does it write all of the files in the folder to the sheet Output?

I assume that all of the files have just one sheet and the data extends downwards from cell A1.

What line of code is highlighted when the error occurs?
 
Upvote 0
This is the line that is highlighted when I click debug:

Code: 
'Open File
    Set wb = Workbooks.Open(fileName:=MyPath & MyFile)

And again, appreciate your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
can't get INDEX-MATCH to copy entire column
Replies
2
Views
310
shift-del
shift-del
E
  • Question Question
Formatting date issue
Replies
3
Views
139
zero269
Z
A
  • Solved
Count unique values based on critera with match in named range
Replies
3
Views
144
Fluff
Fluff
L
  • Question Question
Create Upload File Repeating Values in Columns
Replies
0
Views
114
lostyankee
L
K
  • Solved
Can't get vlookup or index/match to work in compare
Replies
6
Views
422
KDS14589
K

Forum statistics

Threads
1,225,606
Messages
6,185,956
Members
453,333
Latest member
BioCoder84

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top