• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding unique arrays

F

FBB

New Member
The formula below compares 1 array to 3 other arrays. This can get unwieldy if I want to do this for a lot of arrays. Any suggestions how to simplify this?

=IF(OR(AND(A1:AX5=B1:B5),AND(A1:A5=C1:C5),AND(A1:A5=D1:D5)),0,1)
 
Hi FBB,

Could you please tell us what you are trying to achieve, it is easier than trying to see if your formula is OK.

If you are comparing A1:A5, with each of the other columns then the following will work, provided it is Array Entered

{=IF(AND(A1:A5=B1:B5,A1:A5=C1:C5,A1:A5=D1:D5),0,1)}

cheers
kanti
 
Hi ,

I am not clear on what you wish to compare.

First , your formula is presumably :

=IF(OR(AND(A1:A5=B1:B5),AND(A1:A5=C1:C5),AND(A1:A5=D1:D5)),0,1)

Does this mean that all of the elements in the 4 columns should match ? i.e. :

A1 = B1 or A1 = C1 or A1 = D1

A2 = B2 or A2 = C2 or A2 = D2

and so on ?

To how many such ranges do you want to extend this ?

Can you upload a sample data file ?

Narayan
 
If it is just about the comparison between the cells as Kanti and Narayan stated then you can use the below formula and drag to the desired range:
=IF(OR($A2=$B2,$A2=$C2,$A2=$D2),0,1)
 
Thanks all. First, Nara is correct -- the formaula had an errant AX in it. It should have read:

=OR(AND(A1:A5=B1:B5),AND(A1:A5=C1:C5),AND(A1:A5=D1:D5))

I should have done a better job of describing what the formula accomplishes. Say you have 4 lists in cells A1 to A5:{Bob, Jim, Sam, Kim, Abe} ,B1 to B5: {Bob, "", Sam, Kim, Abe} , C1 to C5{Bob, Jim, Sam, "", Abe} , and D1 to D5 {Bob, Jim, Sam, Kim, Abe} . This formula would return TRUE because the first array was the same as one of the other arrays (the foruth array). If the fourth array was not the same as the first array (no arrays matched) Then the formula would return false.

This can get unwieldy if I want to do this for a lot of arrays, for example, is 1 array included in a list of 50 arrays. Any suggestions how to simplify the formula?

Thanks!
 
Assuming the list you want to search is in A1:A4
and your lists are in B2:B7,C2:C7, ... H2:H7

The following formula returns an array indicating the number of matches in each list.
=MMULT(TRANSPOSE(ROW(B2:H7)^0), COUNTIF(A1:A4, B2:H7))

If you are looking to find out if at least one of the columns had a match, you can verify that with something like:
=MAX(MMULT(...))>=ROWS(A1:A4)

Cheers,
Sajan.
 
One thing I forgot to mention... the above formula would count a match if values are out of sequence...
i.e. {A";"B";"C"} and {"C";"A";"B"} will match.

If you need to respect the position of a value in the array, the above formula will need to be tweaked.
 
Hi Sajan,

Thanks for the help, but I'm actually looking to check for instances of the entire array, not individual records within the array.

I've included a file attached that better shows what I am trying to accomplish.
 

Attachments

  • Array Match Example.xlsx
    10 KB · Views: 12
Hello FBB,

Try this Array Formula:

=SUM(IF(MMULT(IFERROR(MATCH("|"&Data&"|","|"&Array1&"|",0)=COLUMN(Array1)-COLUMN(INDEX(Array1,1,1))+1,0)+0,TRANSPOSE(COLUMN(Array1)^0))=COLUMNS(Array1),1))
 
You must log in or register to reply here.
Back
Top