A challenging One
 

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

A challenging One
 

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

On 18 Mar., 04:29, " wrote:
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

A challenging One
 

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!



On Mon, 17 Mar 2008 21:19:35 -0700 (PDT), Per Jessen
wrote:

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

On 18 Mar., 04:29, " wrote:
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

A challenging One
 

No code required - you can do it by formula...

=IF(A2=A1,IF(A2=A3,"",2),1)




"Per Jessen" wrote:

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

On 18 Mar., 04:29, " wrote:
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

A challenging One
 

"James Snell" wrote in message
...
No code required - you can do it by formula...

=IF(A2=A1,IF(A2=A3,"",2),1)

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


Replace @mailinator.com with @tiscali.co.uk


"James Snell" wrote in message
...
No code required - you can do it by formula...

=IF(A2=A1,IF(A2=A3,"",2),1)




"Per Jessen" wrote:

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

On 18 Mar., 04:29, " wrote:
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

A challenging One
 

My bad - missed the bit about the 2 being every 3rd repeated entry. But
still no code required. :)

"Sandy Mann" wrote:

"James Snell" wrote in message
...
No code required - you can do it by formula...

=IF(A2=A1,IF(A2=A3,"",2),1)

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


Replace @mailinator.com with @tiscali.co.uk


"James Snell" wrote in message
...
No code required - you can do it by formula...

=IF(A2=A1,IF(A2=A3,"",2),1)




"Per Jessen" wrote:

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

On 18 Mar., 04:29, " wrote:
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