Compare two ranges in Excel to see if they match exactly

[rachel06]

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?

 

[HighAndWilder]

I

This is the line that is highlighted when I click debug:

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

And again, appreciate your help.

It could be that it has got to the end of the list of files and the value of MyFile is a zero length string.

Move the following line
MyFile = Dir
to the bottom of the loop as shown below.

wb.Close SaveChanges:=False

MyFile = Dir

Loop

 

[rachel06]

Thank you!! I will give that a go next week when I'm back in the office and have recharged my brain

 

[HighAndWilder]

Thank you!! I will give that a go next week when I'm back in the office and have recharged my brain

I trust that your brain is recharged now.

I am sure that there is an easier way of achieving the task that this code does.

Are you able to post the contents of one of the source sheets?

'''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

 

[rachel06]

Hi!

I unfortunately cannot share one of the source sheets as it's proprietary information. Also, those worksheets come as read only which makes it difficult

I do appreciate your willingness to help with the rest of my code though!

 

[rachel06]

I actually just ran it, and it ran smoothly, correctly, and quickly. Thank you so much for all of your help. I can't even express how grateful I am and how much time this is going to save me in the future.

Cheers!

 

[HighAndWilder]

I actually just ran it, and it ran smoothly, correctly, and quickly. Thank you so much for all of your help. I can't even express how grateful I am and how much time this is going to save me in the future.

Cheers!

I'm glad that it works well for you.