Compare text arrays

  • Thread starter Thread starter Hops
  • Start date Start date
H

Hops

hello,

Need a function that compares two arrays and returns true if the contents
are the same, even if not in the same order.

e.g.

A1:A4 = {green, blue, red, brown}
B1:B4 = {blue, brown, red, green}
C1:C4 = {green, blue, red, yellow}

something that returns true comparing array A to B but false comparing A to
C.

TIA.
 
Hi Hops,

Arrays A & C in your example differ, so you can't expect a comparison to
return a true result. However, a suitable formula would be:
=($A1=C$1)*($A2=C2)*($A3=C3)*($A4=C4)
entered as an array formula (Ctrl-Shift-Enter). This will return 1 if true,
0 if false.

Cheers
 
PS:
Enter the formula into, say C5. Copy into B5 also to test column B. Hence
the mixed use of relative & absolute referencing.
 
Hi
if you don't mind using a UDF you can try the following:
--
Public Function compare_ranges(a_rng As Range, b_rng As Range) As
Boolean
Dim ret_value As Boolean
Dim cell As Range
If a_rng.Cells.Count <> b_rng.Cells.Count Then
ret_value = False
Else
ret_value = True
For Each cell In a_rng
If Application.WorksheetFunction.CountIf(b_rng, cell) = 0 Then
ret_value = False
Exit For
End If
Next
End If
compare_ranges = ret_value
End Function
 
Hi,

With Rg1 and Rg2, this ARRAY formula (Ctrl-Shift-Enter) returns TRUE if all
members of Rg1 are in Rg2. This is a slight variation on your problem but if
your arrays are of the same size, it fits your problem definition.

=AND(ISNUMBER(MATCH(Rg1,Rg2,0)))

Regards,

Daniel M.
 
your arrays are of the same size, it fits your problem definition.

same size and contain only unique values (no duplicates), it fits...

Daniel M.
 
Need a function that compares two arrays and returns true if the contents
are the same, even if not in the same order.

e.g.

A1:A4 = {green, blue, red, brown}
B1:B4 = {blue, brown, red, green}
C1:C4 = {green, blue, red, yellow}

something that returns true comparing array A to B but false comparing A to
C.
Click to expand...

If all you want to ensure is that both ranges contain at least one instance of
each entry in the other range, then you could use

=(SUMPRODUCT(COUNTIF(Rng1,Rng2))=SUMPRODUCT(COUNTIF(Rng2,Rng1)))

Note: this ignores blank cells. To treat blank cells as a distinct value that
must also be matched, use

=(SUMPRODUCT(COUNTIF(Rng1,Rng2&"*"))=SUMPRODUCT(COUNTIF(Rng2,Rng1&"*")))
 
Daniel.M said:
Hi,

With Rg1 and Rg2, this ARRAY formula (Ctrl-Shift-Enter) returns TRUE if all
members of Rg1 are in Rg2. This is a slight variation on your problem but if
your arrays are of the same size, it fits your problem definition.

=AND(ISNUMBER(MATCH(Rg1,Rg2,0)))

Regards,

Daniel M.
Click to expand...

thanks, just what I needed.
 
Back
Top