I'm drawing a blank on this one.

I have an Excel database made up of 24 columns (representing numeric
fields) and approximately 2500 rows. Each record will have a value in
at least one of the 24 fields and can have values in up to all 24
fields. I am to create a 25th field (called "ID") in which an "x" is
placed if a record meets the following criteria:

1.) It must have values in at least 2 of the 24 fields, and
2.) At least 1 of the fields with a value in it is not contiguous with
another field with a value in it.

In other words, to qualify for an "x", at least 2 fields have numbers
and there is a gap separating at least one of those fields.

Here's a simplified version:


Field1 Field2 Field3 Field4 Field5 ID
------------------------------------------
12
------------------------------------------
3 14 x
------------------------------------------
1 5
------------------------------------------
7 8 12 x
------------------------------------------
2 3
------------------------------------------
6 5 4 x
------------------------------------------
8 7 1 12 14
------------------------------------------
7 8 10 11 x
------------------------------------------


Either a macro solution or a formula solution to getting the "x" is
fine, and if "helper" columns are needed, that's fine, too.

Many thanks,
Paul

Dick,

Thank you very much for your considerable time and effort. This was a
big help, and I appreciate the work you put in on it for me very much.

Many thanks,
Paul



"Dick Kusleika" wrote in message ...
Paul

For this worksheet formula, you will need two dummy columns on either end of
the 24 columns of data. The dummy columns must contain values. My formula
is in A1, the dummy columns are B and AA and the data is in C1:Z1. Array
enter this formula with control+shift+enter

=IF(COUNT(C1:Z1)1,IF(SUM((C1:Z1<"")*(B1:Y1="")*( D1:AA1=""))0,"selected","
"),"")

I didn't test all the combinations of columns, but I think it's right. Let
me know if you find a mistake.

For this UDF, you don't need dummy columns, just the code below

=IF(isselected(C1:Z1),"selected","")

Function IsSelected(Rng As Range) As Boolean

Dim cell As Range
Dim FirstCol As Long
Dim LastCol As Long

FirstCol = Rng.Cells(1).Column
LastCol = Rng.Cells(Rng.Count).Column

If Application.Count(Rng) 1 Then
For Each cell In Rng.Cells
If Not IsEmpty(cell) Then
Select Case cell.Column
Case FirstCol
If IsEmpty(cell.Offset(0, 1)) Then
IsSelected = True
Exit Function
End If
Case LastCol
If IsEmpty(cell.Offset(0, -1)) Then
IsSelected = True
Exit Function
End If
Case Else
If IsEmpty(cell.Offset(0, 1)) And _
IsEmpty(cell.Offset(0, -1)) Then

IsSelected = True
Exit Function
End If
End Select
End If
Next cell
End If

IsSelected = False

End Function

Let me know if they don't suit your needs.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Paul Simon" wrote in message
om...
I'm drawing a blank on this one.

I have an Excel database made up of 24 columns (representing numeric
fields) and approximately 2500 rows. Each record will have a value in
at least one of the 24 fields and can have values in up to all 24
fields. I am to create a 25th field (called "ID") in which an "x" is
placed if a record meets the following criteria:

1.) It must have values in at least 2 of the 24 fields, and
2.) At least 1 of the fields with a value in it is not contiguous with
another field with a value in it.

In other words, to qualify for an "x", at least 2 fields have numbers
and there is a gap separating at least one of those fields.

Here's a simplified version:


Field1 Field2 Field3 Field4 Field5 ID
------------------------------------------
12
------------------------------------------
3 14 x
------------------------------------------
1 5
------------------------------------------
7 8 12 x
------------------------------------------
2 3
------------------------------------------
6 5 4 x
------------------------------------------
8 7 1 12 14
------------------------------------------
7 8 10 11 x
------------------------------------------


Either a macro solution or a formula solution to getting the "x" is
fine, and if "helper" columns are needed, that's fine, too.

Many thanks,
Paul