Formula checking seprate condition(s) in column & row

  • Thread starter Thread starter Ahmed
  • Start date Start date
A

Ahmed

Hi

I need help in the following.

One column has numbers ( if numbers are same formula should notify ) o
the other hand on the right side row's { that all rows that hav
matching numbers on the left side } have different numbers in fields o
rows ( appearing as fR , in below chart) , if the number on the lef
hand side columns is same & on its right side in any column row any th
number matches or is same ( in diffrent fields of fR) the formul
should notify , not as same or 1 , by the figure that is <same o
repeating in the said field of row> should appear in place of same o
"1".
e.g.
Columns fR fR fR fR Formula field
4333170 22 44 1 1 22
4333170 33 36
4333170 11 22 22

the number was same in column ( i.e. 4333170 & in fR 22 appeared to b
the same digit in 1st & 3rd row,therefore the formula reflected "22".


regards

Ahme
 
Ahmed,

You need to use a user-defined-function. The code is below, copy it into a
module in your workbook. Then use it like this:

=Repeated($A$1:$A$10,$B$1:$E$10)

Where your data columns start in A, row 1, and you have 10 rows of data, and
your formula is in cell F1. Copy it from F1 to F1:F10.

HTH,
Bernie
MS Excel MVP

Option Explicit
Function Repeated(CodeRange As Range, Fields As Range) As String
Dim myCell As Range
Dim myKey As Range
Dim myFields As Range
Dim myCount As Integer
Dim i As Integer

Repeated = ""

Set myKey = Intersect(CodeRange, Application.Caller.EntireRow)
Set myFields = Intersect(Fields, Application.Caller.EntireRow)
For Each myCell In CodeRange
If myCell.Row <> myKey.Row Then
If myCell.Value = myKey.Value Then
Set myFields = Union(myFields, Intersect(Fields, myCell.EntireRow))
End If
End If
Next myCell

For Each myCell In Intersect(Fields, Application.Caller.EntireRow)
myCount = 0
For i = 1 To myFields.Areas.Count
myCount = myCount + Application.WorksheetFunction.CountIf( _
myFields.Areas(i), myCell.Value)
Next i

If myCount > 1 Then
If Not InStr(1, Repeated, " " & myCell.Value & ", ") > 0 Then
If Repeated = "" Then
Repeated = " " & myCell.Value & ", "
Else
Repeated = Repeated & myCell.Value & ", "
End If
End If
End If

Next myCell

If Repeated = "" Then
Repeated = "No repeats"
Else
Repeated = Trim(Left(Repeated, Len(Repeated) - 2))
End If
End Function
 
Back
Top