Detecting first occurence and marking others as repeat

[vibhorkhanna]

hi I am trying to find the first occurrence of a value within a set of values within bigger series and mark others as repeat values.
The table below might help understand better.
ID Unique/Repeat Supervisor Remark Count Expected Output
A 1 MO NC 7 MO NC
A 0 MO NC REPEAT REPEAT
A 0 RO C RO C
A 0 RO C REPEAT REPEAT
A 0 MO C REPEAT REPEAT
A 0 RO C REPEAT REPEAT
A 0 GO NC GO REPEAT
B 1 MO NC 2 MO NC
B 0 MO NC REPEAT REPEAT

I have made a function DetectChange, to achieve the above result:
=IF(B2=1,C2,DetectChange(MATCH(A2,A:A,0):MATCH(A2,A:A,0)+COUNTIF(A:A,A2),A2:MATCH(A2,A:A,0)+COUNTIF(A:A,A2),Column(C2))) ' for supervisor column
=IF(B2=1,D2,DetectChange(MATCH(A2,A:A,0):MATCH(A2,A:A,0)+COUNTIF(A:A,A2),A2:MATCH(A2,A:A,0)+COUNTIF(A:A,A2),Column(D2))) ' for remark column

Public Sub DetectChange(ByVal FullRange As Range, ByVal TestRange As Range, ByVal Pos As Integer)
Dim wrk, fnd, result As Range
Dim i, j As Integer

i = FullRange.Row.Count

For j = 0 To i Step 1
wrk(j) = FullRange(j).Offset(0, Pos - 1)
Next j

fnd = TestRange(0)
With Range(wrk)
Set result = .Find(What:=fnd, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
DetectChange = "REPEAT"
Else
DetectChange = fnd
End If
End With
End If
End Sub

I am still a novice at VBA coding and any help would be greatly appreciated. Many Thanks!

 

[Scott Huish]

This should be accomplishable with a formula, why VBA?

 

[Scott Huish]

Formula in F2:
=IF(COUNTIFS(C$2:C2,C2,$A$2:$A2,$A2)=1,C2,"REPEAT")
Copy across and down.

Excel Workbook
	A	B	C	D	E	F	G
1	ID	Unique/Repeat	Supervisor	Remark	Count	Expected	Output
2	A	1	MO	NC	7	MO	NC
3	A	0	MO	NC		REPEAT	REPEAT
4	A	0	RO	C		RO	C
5	A	0	RO	C		REPEAT	REPEAT
6	A	0	MO	C		REPEAT	REPEAT
7	A	0	RO	C		REPEAT	REPEAT
8	A	0	GO	NC		GO	REPEAT
9	B	1	MO	NC	2	MO	NC
10	B	0	MO	NC		REPEAT	REPEAT
Sheet1

 

[vibhorkhanna]

Thanks a ton Scott! This is much simpler than I was imaging. Thanks Again.

Formula in F2:
=IF(COUNTIFS(C$2:C2,C2,$A$2:$A2,$A2)=1,C2,"REPEAT")
Copy across and down.

Excel Workbook
	A	B	C	D	E	F	G
1	ID	Unique/Repeat	Supervisor	Remark	Count	Expected	Output
2	A	1	MO	NC	7	MO	NC
3	A	0	MO	NC	*	REPEAT	REPEAT
4	A	0	RO	C	*	RO	C
5	A	0	RO	C	*	REPEAT	REPEAT
6	A	0	MO	C	*	REPEAT	REPEAT
7	A	0	RO	C	*	REPEAT	REPEAT
8	A	0	GO	NC	*	GO	REPEAT
9	B	1	MO	NC	2	MO	NC
10	B	0	MO	NC	*	REPEAT	REPEAT
Sheet1

 

[vibhorkhanna]

Scott, It is taking too long to execute for the amount of data I have. I would have to resort to using macros