exclude cells from an AND

[stuck]

The array formula:

Code: Select all

=IF(AND(D2=O2,Z2,AK2,AV2,BG2),"all same","different")

is fine as far as it goes, as in if the contents of the six cells (D2,O2, etc.) are all the same it tells me they are the same.

However, I need something a bit more dynamic, I need a formula that can ignore / not include any cells in the array in the AND ststement if those cells say 'not used'. For example, if D2, O2, Z2, AK2 and AV2 all held 'bananas' but BG2 held 'not used' I want to know that AND(D2, O2, Z2, AK2, AV2) are '"all same".

Does such a formula exist?

Thanks,

Ken

[HansV]

Your formula only checks that D2=O2, the rest is ignored (try it!)
I'll get back to you later.

[stuck]

Your formula only checks that D2=O2, the rest is ignored (try it!)
I'll get back to you later.

Yes, sorry, I realised that as soon as I posted, I should have edited the formula, to add in all the missing 'D2=' bits.

Thank you for looking into this but there's no rush, I've logged out of my work system now.

Ken

[HansV]

A formula is possible, but it gets complicated with all the possible combinations, so I wrote a short UDF:

Code: Select all

Function Same(ParamArray cells()) As String
    Dim col As New Collection
    Dim cell
    On Error Resume Next
    For Each cell In cells
        If LCase(cell.Value) <> "not used" Then
            col.Add Item:=cell.Value, Key:=CStr(cell.Value)
        End If
    Next cell
    If col.Count = 1 Then
        Same = "All Same"
    Else
        Same = "Different"
    End If
End Function

Use like this:

=Same(D2,O2,Z2,AK2,AV2,BG2)

[HansV]

Even more dynamic, the following version lets you specify the exception string:

Code: Select all

Function Same(Exception As String, ParamArray cells()) As String
    Dim col As New Collection
    Dim cell
    On Error Resume Next
    For Each cell In cells
        If LCase(cell.Value) <> Exception Then
            col.Add Item:=cell.Value, Key:=CStr(cell.Value)
        End If
    Next cell
    If col.Count = 1 Then
        Same = "All Same"
    Else
        Same = "Different"
    End If
End Function

Usage:

=Same("not used",D2,O2,Z2,AK2,AV2,BG2)

[stuck]

Genius! I'll try that later

Ken