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]

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?

Are you able to post some data using XL2BB indicting what you are trying to achieve?

I'm sure that what you want is possible.

 

[rachel06]

Of course! See below.

The first table is on one sheet and the longer one is on another sheet. I want to match the top one against the bottom, and bring in a result. Each colored section is a named range (R_1, R_2, etc) so if the name of the range it matches could be the result of the formula, perfect! But I need the result once, just where the formula is, not for every row. So in this case, the top table matches the second table in the colored tables, so I'd want the formula to result in R_2.

Let me know if that doesn't make sense!

Sample Data.xlsx
	A
1	The following are excluded:
2	Apples
3	Grapes
4	Oranges
5	Bananas
6	Cookies
7	Milk
8	Vitamins
9	Paper
10	Pencils
Rate Sheet Language

Sample Data.xlsx
	A
1	The following are excluded:
2	Apples
3	Grapes
4	Oranges
5	Bananas
6	Cookies
7	Milk
8	Shirts
9	Shoes
10	Paper
11	Pencils;
12	Cereal
13	Markers
14	Shampoo
15	Soap
16	Towels
17	Globes
18
19	The following are excluded:
20	Apples
21	Grapes
22	Oranges
23	Bananas
24	Cookies
25	Milk
26	Vitamins
27	Paper
28	Pencils
29
30	The following are excluded:
31	Apples
32	Grapes
33	Oranges
34	Plants
35	Bananas
36	Drills
37	Milk
38	Vitamins
39	Paper
40	Hammers
41
42	The following are excluded:
43	Apples
44	Grapes
45	Oranges
46	Plants
47	Screws
48	Vitamins
49	Nails
50
51	The following are excluded:
52	Apples
53	Grapes
54	Oranges
55	Bananas
56	Cookies
57	Milk
58	Shirts
59	Shoes
60	Paper
61	Pencils
62
63	The following are excluded:
64	Apples
65	Grapes
66	Oranges
67	Plants
68	Wrenches
69	Tacos
70	Vitamins
71	Paper
72	Hammers
73
74	The following are excluded:
75	Apples
76	Grapes
77	Oranges
78	Plants
79	Screws
80	Burgers
81	Vitamins
82	Nails
Lookup

 

[HighAndWilder]

Of course! See below.

The first table is on one sheet and the longer one is on another sheet. I want to match the top one against the bottom, and bring in a result. Each colored section is a named range (R_1, R_2, etc) so if the name of the range it matches could be the result of the formula, perfect! But I need the result once, just where the formula is, not for every row. So in this case, the top table matches the second table in the colored tables, so I'd want the formula to result in R_2.

Let me know if that doesn't make sense!

I must admit to not fully understanding what you want but we will get there.

You want to carry out a check for the values in a number of named ranges, one at a time, and report where there is a match between the values in another list.

Can you run this procedure when you have a new blank sheet active and post the mini-sheet of named ranges?

Private Sub subNamedRanges()
Dim nm As Name
  
  With ActiveSheet.Range("A1")
    .CurrentRegion.ClearContents
    .ListNames
  End With

End Sub

 

[Fuji]

The first table is on one sheet and the longer one is on another sheet. I want to match the top one against the bottom, and bring in a result. Each colored section is a named range (R_1, R_2, etc) so if the name of the range it matches could be the result of the formula, perfect! But I need the result once, just where the formula is, not for every row. So in this case, the top table matches the second table in the colored tables, so I'd want the formula to result in R_2.

Try

Sub test()
    Dim s$, myName As Name
    s = Sheets("sheet1").[a1].CurrentRegion.Address(, , , 1)
    For Each myName In ThisWorkbook.Names
        If Evaluate("iferror(and(" & s & myName.RefersTo & "),false)") Then
            MsgBox myName.Name: Exit For
        End If
    Next
End Sub

 

[rachel06]

Try

Sub test()
    Dim s$, myName As Name
    s = Sheets("sheet1").[a1].CurrentRegion.Address(, , , 1)
    For Each myName In ThisWorkbook.Names
        If Evaluate("iferror(and(" & s & myName.RefersTo & "),false)") Then
            MsgBox myName.Name: Exit For
        End If
    Next
End Sub

Hi! I'm not sure I'm understanding how this one works. I think in this case, a formula is going to be easier than a code like this. Below is the end result I'm trying to get to:

The macro is looping through a folder of excel workbooks (sometimes 100+) and:
- Lists out the file names on a sheet called Output (column A) in the "Master Workbook", where the macro is run from.
- Opens each individual workbook, find the exclusion language, and pastes it onto a sheet called "Rate Sheet Language"

What I'm really hoping for is, in column B on the Output sheet, to have what list was found on that Excel (next to the file name). I'm just assuming having the formula reference the range that has the matching language would be easiest, but I could be wrong!

 

[rachel06]

I must admit to not fully understanding what you want but we will get there.

You want to carry out a check for the values in a number of named ranges, one at a time, and report where there is a match between the values in another list.

Can you run this procedure when you have a new blank sheet active and post the mini-sheet of named ranges?

Private Sub subNamedRanges()
Dim nm As Name
 
  With ActiveSheet.Range("A1")
    .CurrentRegion.ClearContents
    .ListNames
  End With

End Sub

Here you go!

UMR EXCLUSION LANGUAGE.xlsm
	A	B	C	D
1
2		R_1	=Lookup!$A$1:$A$17
3		R_2	=Lookup!$A$19:$A$28
4		R_3	=Lookup!$A$30:$A$40
5		R_4	=Lookup!$A$42:$A$49
6		R_5	=Lookup!$A$51:$A$61
7		R_6	=Lookup!$A$63:$A$72
8		R_7	=Lookup!$A$74:$C$82
9
Sheet1

I'm going to try to explain this a little better. Here is what I want the end result to be (this is the only piece that's currently not completed, bolded below)

The Macro is run from a Master Spreadsheet where:

1. The user chooses a source folder where the Excels to be reviewed are saved
2. The macro loops through and:
-Places the file name in column A, starting with cell A2 on a sheet called Output
-Opens the file from the source folder, finds the exclusion language, and pastes it into the master spreadsheet, onto a tab called Rate Sheet Language
-Matches that language with one of the ranges on the Lookup sheet (which is what's in the master sheet), and paste the result next to the file name in column B
-Clears that language and moves onto the next file

That's why I think a formula here would be the best option, if possible, because I can just add that into the code and paste values.

Let me know if you need further explanation, and I appreciate your patience and willingness to help!

 

[HighAndWilder]

I'm still not there.

-Opens the file from the source folder, finds the exclusion language,
What is the 'exclusion language? Can you please explain with an example?

Can you please post an example of what is pasted into the Rate Sheet Language sheet?

which is what's in the master sheet
Can you please post what is in the master sheet?

 

[rachel06]

I'm still not there.

-Opens the file from the source folder, finds the exclusion language,
What is the 'exclusion language? Can you please explain with an example?

Can you please post an example of what is pasted into the Rate Sheet Language sheet?

which is what's in the master sheet
Can you please post what is in the master sheet?

Hi!

The exclusion language is what I posted earlier using XL2BB


To make this way easier, I'm not even sure you need to know all of that background, so I'm going to summarize so you or anyone else who looks at this might be able to recreate:

In my earlier thread with the XL2BB data:

1. The 10 white rows are on a sheet called "Rate Sheet Language" (A1-A10)
2. The colored rows are on a sheet called "Lookup" (A1-A82, named with the ranges posted earlier)

What I would want is something that would look at the data on "Rate Sheet Language" and match it to a range on "Lookup". In the above example, it would match to R_2, which is $A$19-$A$28. So essentially, I'm looking for a formula that would return R_2, or whatever range it matches.

Right now every formula I try to do is bringing a TRUE for each of the 10 lines on "Rate Sheet Language" where I just want ONE result.

 

[HighAndWilder]

Hi!

The exclusion language is what I posted earlier using XL2BB


To make this way easier, I'm not even sure you need to know all of that background, so I'm going to summarize so you or anyone else who looks at this might be able to recreate:

In my earlier thread with the XL2BB data:

1. The 10 white rows are on a sheet called "Rate Sheet Language" (A1-A10)
2. The colored rows are on a sheet called "Lookup" (A1-A82, named with the ranges posted earlier)

What I would want is something that would look at the data on "Rate Sheet Language" and match it to a range on "Lookup". In the above example, it would match to R_2, which is $A$19-$A$28. So essentially, I'm looking for a formula that would return R_2, or whatever range it matches.

Right now every formula I try to do is bringing a TRUE for each of the 10 lines on "Rate Sheet Language" where I just want ONE result.

Try this.

On a spare sheet or space on an existng sheet :

Compare two ranges in Excel to see if they match exactly.xlsm
	C	D	E
1		Helper
2		Apples,Grapes,Oranges,Bananas,Cookies,Milk,Shirts,Shoes,Paper,Pencils;,Cereal,Markers,Shampoo,Soap,Towels,Globes
3		Apples,Grapes,Oranges,Bananas,Cookies,Milk,Vitamins,Paper,Pencils
4		Apples,Grapes,Oranges,Plants,Bananas,Drills,Milk,Vitamins,Paper,Hammers
5
Lookup

Cell Formulas
Range	Formula
D2	D2	=TEXTJOIN(",",TRUE,R_1)
D3	D3	=TEXTJOIN(",",TRUE,R_2)
D4	D4	=TEXTJOIN(",",TRUE,R_3)

Named Ranges
Name	Refers To	Cells
R_1	=Lookup!$A$2:$A$17	D2
R_2	=Lookup!$A$18:$A$26	D3
R_3	=Lookup!$A$29:$A$38	D4

And then on the 'Rate Sheet Language' sheet or wherever you like :

Compare two ranges in Excel to see if they match exactly.xlsm
	B	C
1
2	R_2
3
4
Rate Sheet Language

Cell Formulas
Range	Formula
B2	B2	=IFERROR("R_" & MATCH(TEXTJOIN(",",TRUE,'Rate Sheet Language'!A2:A10),Lookup!D2:D4,0),"")