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

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

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

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
>
>

"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
>>
>>
>

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
> >>
> >>
> >
>
>
>