Monster of a formula!!

[CSmith]

Here goes...not sure if this can be done but you guys are the experts so
thought I'd ask!! We have six columns, DA - DF and we want the formula in
DG. The first three columns repeat themselves in the last three. The first
three are titled "Season 0", "Season 1", and "Season 2" and are in DA-DC and
repeat themselves in DD-DF, giving us our six columns. Within those six
columns we have ID numbers for people, in no particular order in any of the
six columns. We want the formula in DG to look in column BT, pull that ID #
out and search for it in one of the six columns mentioned earlier, DA-DF.
Here is where it gets tricky. I mentioned that the six columns just repeat
themselves, Season 0, Season 1, Season 2, Season 0, Season 1, Season2. While
the formula is pulling the ID # from BT and searching in columns DA-DF, we
need to know exactly where it found them. For instance, if the ID # in BT
row 2 is 12345, and it finds it in any of the six columns, we need some
specifics. Any ID # found in the same seasons are acceptable, meaning if
12345 is found in Season 0 (DA) and Season 0 (DD), we just want the formula
to return something like "Good". If 12345 is found in only ONE of the
columns out of the six, that is also "Good". We are looking for 12345 found
in various seasons. If 12345 is found in Season 1 and Season 2 (doesn't
matter which 1 & 2 column), we want a formula return of "Bad". We need Excel
to look through all rows and columns in DA-DF, not just the row associated
with the formula. So if the first row is 2, we don't want the formula to
only look for 12345 in the six columns in row 2. We need it to look all the
way down to row 50,000 in ALL six columns. Basically, pull the ID # from
BT, look through thousands of cells with in six columns and return a "Good"
or "Bad". We are just trying to identify numbers that appear once in any of
the six columns (Good), numbers that appear more than once but within the
same seasons ("Good" and in both season 1 columns, or season 2 columns,...),
and numbers that appear more than once but in different seasons with a return
of "Bad" (12345 found in Season 1 column DB row 15,000 and 12345 found in
Season 0 column DD row 5, 423). Any help is appreciated as soon as possible.
Thanks!!

 

[Bernie Deitrick]

You will need 7 columns of formulas overall.

In DG2, enter

=IF(NOT(ISERROR(MATCH($BT2,DAA,FALSE))),"Found","")

and copy to DH2L2. In DM2, enter the formula

=IF(COUNTIF(DG2L2,"Found")=1,"Good",IF(COUNTIF(DG2L2,"Found")>=3,"Bad",IF(AND(DG2=DJ2,DH2=DK2,DI2=DL2),"Good","Bad")))

Then copy DG2M2 down to match your column of values in DT, and look at the values in DM.

HTH,
Bernie
MS Excel MVP

 

[Tom Hutchins]

Here is one way, using 3 helper columns...

I put my formulas in columns DG through DJ. If you use different columns,
adjust the references in the column DJ formula accordingly.

In DG2, enter
=IF(LEN(BT2)>0,SUMPRODUCT(--($DA$1:$DA$50000=BT2))+SUMPRODUCT(--($DD$1:$DD$50000=BT2)),0)

In DH2, enter
=IF(LEN(BT2)>0,SUMPRODUCT(--($DB$1:$DB$50000=BT2))+SUMPRODUCT(--($DE$1:$DE$50000=BT2)),0)

In DI2, enter
=IF(LEN(BT2)>0,SUMPRODUCT(--($DC$1:$DC$50000=BT2))+SUMPRODUCT(--($DF$1:$DF$50000=BT2)),0)

In DJ2, enter
=CHOOSE(COUNTIF(DG2I2,">0")+1,"","GOOD","BAD")

Copy all of these down as needed. You could then hide columns DG-DI if
desired.

Hope this helps,

Hutch

 

[Ron Rosenfeld]

Here goes...not sure if this can be done but you guys are the experts so
thought I'd ask!! We have six columns, DA - DF and we want the formula in
DG. The first three columns repeat themselves in the last three. The first
three are titled "Season 0", "Season 1", and "Season 2" and are in DA-DC and
repeat themselves in DD-DF, giving us our six columns. Within those six
columns we have ID numbers for people, in no particular order in any of the
six columns. We want the formula in DG to look in column BT, pull that ID #
out and search for it in one of the six columns mentioned earlier, DA-DF.
Here is where it gets tricky. I mentioned that the six columns just repeat
themselves, Season 0, Season 1, Season 2, Season 0, Season 1, Season2. While
the formula is pulling the ID # from BT and searching in columns DA-DF, we
need to know exactly where it found them. For instance, if the ID # in BT
row 2 is 12345, and it finds it in any of the six columns, we need some
specifics. Any ID # found in the same seasons are acceptable, meaning if
12345 is found in Season 0 (DA) and Season 0 (DD), we just want the formula
to return something like "Good". If 12345 is found in only ONE of the
columns out of the six, that is also "Good". We are looking for 12345 found
in various seasons. If 12345 is found in Season 1 and Season 2 (doesn't
matter which 1 & 2 column), we want a formula return of "Bad". We need Excel
to look through all rows and columns in DA-DF, not just the row associated
with the formula. So if the first row is 2, we don't want the formula to
only look for 12345 in the six columns in row 2. We need it to look all the
way down to row 50,000 in ALL six columns. Basically, pull the ID # from
BT, look through thousands of cells with in six columns and return a "Good"
or "Bad". We are just trying to identify numbers that appear once in any of
the six columns (Good), numbers that appear more than once but within the
same seasons ("Good" and in both season 1 columns, or season 2 columns,...),
and numbers that appear more than once but in different seasons with a return
of "Bad" (12345 found in Season 1 column DB row 15,000 and 12345 found in
Season 0 column DD row 5, 423). Any help is appreciated as soon as possible.
Thanks!!

You could use a UDF.

To enter this, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

Then enter:

DG2: =ckid(BT2,$DA$1:$DF$50000)

Adjust the Tbl range to whatever is appropriate.

and fill down as far as required.

Oh, you did not state what result you wanted if there are NO matches (e.g. if
an ID does not appear in any column). I took the liberty of making that a
"bad".

I have no idea how fast/slow this will be on a DB of 50,000 rows.

=========================================
Option Explicit
Function CkID(ID, Tbl As Range) As String
Dim c As Range
Dim FirstAdr As String
Dim rNext As Range
Dim aSeasons(0 To 2) As Boolean
Dim i As Long, temp As Long

If ID = "" Then
CkID = "Bad"
Exit Function
End If

With Tbl
Set c = .Find( _
what:=ID, _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows)
If Not c Is Nothing Then
FirstAdr = c.Address
Do
aSeasons((c.Column - .Column) Mod 3) = True
Set c = .Find( _
what:=ID, _
after:=c)
Loop While Not c Is Nothing And c.Address <> FirstAdr
End If
End With

temp = 0
For i = 0 To 2
temp = aSeasons(i) + temp
Next i

If temp = -1 Then
CkID = "Good"
Else
CkID = "Bad"
End If

End Function
==============================
--ron

 

[Rick Rothstein \(MVP - VB\)]

I **think** this formula does what you have asked...

=IF(AND(COUNTIF(DA2A50000,BT2)+COUNTIF(DD2D50000,BT2)>0,COUNTIF(DA2F50000,BT2)-COUNTIF(DA2A50000,BT2)-COUNTIF(DD2D50000,BT2)=0),"Good",IF(AND(COUNTIF(DB2B50000,BT2)+COUNTIF(DE2E50000,BT2)>0,COUNTIF(DA2F50000,BT2)-COUNTIF(DB2B50000,BT2)-COUNTIF(DE2E50000,BT2)=0),"Good",IF(AND(COUNTIF(DC2C50000,BT2)+COUNTIF(DF2F50000,BT2)>0,COUNTIF(DA2F50000,BT2)-COUNTIF(DC2C50000,BT2)-COUNTIF(DF2F50000,BT2)=0),"Good",IF(COUNTIF(DA2F50000,BT2)>0,"Bad","ID
not in use"))))

Rick