Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Challenging | Excel Programming | |||
challenging | Excel Programming | |||
Challenging? | Excel Programming | |||
Very challenging | Excel Programming | |||
Something Challenging | Excel Programming |