A challenging One

  • Thread starter Thread starter test1
  • Start date Start date
T

test1

Simplified this but 1st column contains the data and the second column
is emtpy. Below is what I would like the program to produce when run.
Basically, For every new series of data a 1 would go in the 2nd
column. Pretty easy right, well I also need the program to put a 2
in the second column for every 3rd matching data set after the initial
1. Take a look at the E data, First time it put in a 1, but every 3
matching E's it put in a 2 in the second column. The data and sample
of results might explain it better. Challenging I hope for even you
experts.

Data looks like this
Col1 Col2
A
A
B
B
B
B
C
D
D
E
E
E
E
E
E
E
E
E
F
F
F
F
G
H
H
H
H
H

Results of Program would look like this
Col1 Col2
A 1
A
B 1
B
B
B 2
C 1
D 1
D
E 1
E
E
E 2
E
E
E 2
E
E
F 1
F
F
F 2
G 1
H 1
H
H
H 2
H
 
Hi

This should do what your are asking for ;-)
Just change "StartCell" to the first cell containing your data.

Sub TestMe()
Dim StartCell As String
Dim Counter As Long
Dim TargetRange As Range
StartCell = "A1" ' <= Change to suit
Set TargetRange = Range(StartCell, Range(StartCell).End(xlDown))
For Each c In TargetRange
If c.Value <> TestValue Then
TestValue = c.Value
Counter = 0
c.Offset(0, 1) = 1
Else
Counter = Counter + 1
If Counter Mod 3 = 0 Then c.Offset(0, 1) = 2
End If
Next
End Sub

Regards,

Per
 
Incredible, what I thought was going to be a tough challenge you not
only replied back within minutes but your code was extremely clean and
worked perfectly. Thank You Very Much!!

Just amazing!!! Wish you could have seen the look on my face when I
seen your reply so fast.
Thanks Again!
 
James Snell said:
No code required - you can do it by formula...

=IF(A2=A1,IF(A2=A3,"",2),1)
Click to expand...

Doesn't quite return the same as to OP's example

B1: =IF(A1="","",1)
B2: =IF(A2=A1,"",1) and drag down to B3
B4: =IF(AND(A4=A1,COUNTIF(B2:B3,"")=2),2,IF(A4=A3,"",1))

and dragged down as far as required does seem to match the OP's example'

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
My bad - missed the bit about the 2 being every 3rd repeated entry. But
still no code required. :)
 
Back
Top