I have a large amount of data in 4 columns in the format of:

1 2 3 4 header
AA 01 A 1
AA 01 A 2
AA 01 A 3
AA 01 B 1
AA 01 B 2
AA 01 B 3
AA 01 C 1
AA 01 C 2
AA 01 C 3
AA 01 D 1
AA 01 D 2
AA 01 D 3

The data in column 4 alternates 1,2,3 for the whole selection
The data in column 3 alternates as such between A,B,C and D
The data in column 2 goes up 1 every time until it gets to 10 and then
column 1 changes to AB and so on until it gets to the letters AF so in
total there will be 18720 records in total. If someone can think of
some way of automating this it would come in very handy.

Cheers,
Mike

Mike

Can you clarify a few things. In your post you said:

The data in column 2 goes up 1 every time until it gets to 10 and then
column 1 changes to AB and so on until it gets to the letters AF

However you have shown column 1 staying static as 01. Also the example below
shows AA repeated for 12 rows which makes sense considering you want to
repeat A,B,C,D 3 times each. But in the discription above you have said AA
should change to AB after 10 rows not 12.

Obviously I have not grasped what you are after as 12 rows by AA,AB,AC,AD,AE
and AF only gives me a total of 72 rows.

Regards
Rowan

"Mike G" wrote:

I have a large amount of data in 4 columns in the format of:

1 2 3 4 header
AA 01 A 1
AA 01 A 2
AA 01 A 3
AA 01 B 1
AA 01 B 2
AA 01 B 3
AA 01 C 1
AA 01 C 2
AA 01 C 3
AA 01 D 1
AA 01 D 2
AA 01 D 3

The data in column 4 alternates 1,2,3 for the whole selection
The data in column 3 alternates as such between A,B,C and D
The data in column 2 goes up 1 every time until it gets to 10 and then
column 1 changes to AB and so on until it gets to the letters AF so in
total there will be 18720 records in total. If someone can think of
some way of automating this it would come in very handy.

Cheers,
Mike

And I've confused the issue further by quoting the wrong column number. I
meant to say "However you have shown column 2 staying static as 01"

"Rowan" wrote:

Mike

Can you clarify a few things. In your post you said:

The data in column 2 goes up 1 every time until it gets to 10 and then
column 1 changes to AB and so on until it gets to the letters AF

However you have shown column 1 staying static as 01. Also the example below
shows AA repeated for 12 rows which makes sense considering you want to
repeat A,B,C,D 3 times each. But in the discription above you have said AA
should change to AB after 10 rows not 12.

Obviously I have not grasped what you are after as 12 rows by AA,AB,AC,AD,AE
and AF only gives me a total of 72 rows.

Regards
Rowan

"Mike G" wrote:

I have a large amount of data in 4 columns in the format of:

1 2 3 4 header
AA 01 A 1
AA 01 A 2
AA 01 A 3
AA 01 B 1
AA 01 B 2
AA 01 B 3
AA 01 C 1
AA 01 C 2
AA 01 C 3
AA 01 D 1
AA 01 D 2
AA 01 D 3

The data in column 4 alternates 1,2,3 for the whole selection
The data in column 3 alternates as such between A,B,C and D
The data in column 2 goes up 1 every time until it gets to 10 and then
column 1 changes to AB and so on until it gets to the letters AF so in
total there will be 18720 records in total. If someone can think of
some way of automating this it would come in very handy.

Cheers,
Mike

Thanks for replying Rowan

I didn't word it particularly well at all.

The third and fourth columns are okay and remain the same.
The second column remains 01 for 12 records and then becomes 02 again
for another 12 records and then 03 for 12 records and so on until 10.
The first column remains as AA for 120 records and then becomes AB and
the records repeat up to CF.
So there are really 58 batches of 120 records - a total of 6960
records (where the hell I came up with 18720 I'm not sure).

So i do apologise for throwing you with a poor explanation.

The records are to be used for labelling a grid in a GIS application -
this might give you a better idea of what I'm trying to do - letters
for rows, numbers for columns.

Cheers,
Mike




"?B?Um93YW4=?=" wrote in message ...
And I've confused the issue further by quoting the wrong column number. I
meant to say "However you have shown column 2 staying static as 01"

"Rowan" wrote:

Mike

Can you clarify a few things. In your post you said:

The data in column 2 goes up 1 every time until it gets to 10 and then
column 1 changes to AB and so on until it gets to the letters AF

However you have shown column 1 staying static as 01. Also the example below
shows AA repeated for 12 rows which makes sense considering you want to
repeat A,B,C,D 3 times each. But in the discription above you have said AA
should change to AB after 10 rows not 12.

Obviously I have not grasped what you are after as 12 rows by AA,AB,AC,AD,AE
and AF only gives me a total of 72 rows.

Regards
Rowan

"Mike G" wrote:

I have a large amount of data in 4 columns in the format of:

1 2 3 4 header
AA 01 A 1
AA 01 A 2
AA 01 A 3
AA 01 B 1
AA 01 B 2
AA 01 B 3
AA 01 C 1
AA 01 C 2
AA 01 C 3
AA 01 D 1
AA 01 D 2
AA 01 D 3

The data in column 4 alternates 1,2,3 for the whole selection
The data in column 3 alternates as such between A,B,C and D
The data in column 2 goes up 1 every time until it gets to 10 and then
column 1 changes to AB and so on until it gets to the letters AF so in
total there will be 18720 records in total. If someone can think of
some way of automating this it would come in very handy.

Cheers,
Mike

Hello Mike

I understand what you are after now - shouldn't be too diffcult to do. The
only question is how many "batches" you actually want. As you stated 58 sets
of 120 records is 6960 rows. However 58 sets only gets you to BF and not CF.
CF would be 84 sets and 10080 rows. I am going to do this up to CF and if you
don't want the extras you can just delete the rows.

Regards
Rowan

"Mike G" wrote:

Thanks for replying Rowan

I didn't word it particularly well at all.

The third and fourth columns are okay and remain the same.
The second column remains 01 for 12 records and then becomes 02 again
for another 12 records and then 03 for 12 records and so on until 10.
The first column remains as AA for 120 records and then becomes AB and
the records repeat up to CF.
So there are really 58 batches of 120 records - a total of 6960
records (where the hell I came up with 18720 I'm not sure).

So i do apologise for throwing you with a poor explanation.

The records are to be used for labelling a grid in a GIS application -
this might give you a better idea of what I'm trying to do - letters
for rows, numbers for columns.

Cheers,
Mike




"?B?Um93YW4=?=" wrote in message ...
And I've confused the issue further by quoting the wrong column number. I
meant to say "However you have shown column 2 staying static as 01"

"Rowan" wrote:

Mike

Can you clarify a few things. In your post you said:

The data in column 2 goes up 1 every time until it gets to 10 and then
column 1 changes to AB and so on until it gets to the letters AF

However you have shown column 1 staying static as 01. Also the example below
shows AA repeated for 12 rows which makes sense considering you want to
repeat A,B,C,D 3 times each. But in the discription above you have said AA
should change to AB after 10 rows not 12.

Obviously I have not grasped what you are after as 12 rows by AA,AB,AC,AD,AE
and AF only gives me a total of 72 rows.

Regards
Rowan

"Mike G" wrote:

I have a large amount of data in 4 columns in the format of:

1 2 3 4 header
AA 01 A 1
AA 01 A 2
AA 01 A 3
AA 01 B 1
AA 01 B 2
AA 01 B 3
AA 01 C 1
AA 01 C 2
AA 01 C 3
AA 01 D 1
AA 01 D 2
AA 01 D 3

The data in column 4 alternates 1,2,3 for the whole selection
The data in column 3 alternates as such between A,B,C and D
The data in column 2 goes up 1 every time until it gets to 10 and then
column 1 changes to AB and so on until it gets to the letters AF so in
total there will be 18720 records in total. If someone can think of
some way of automating this it would come in very handy.

Cheers,
Mike

Sorry for doubting you Mike. I was counting from A and not AA. CF is 58 sets
and 6960 rows ;). Solution to follow shortly.

"Rowan" wrote:

Hello Mike

I understand what you are after now - shouldn't be too diffcult to do. The
only question is how many "batches" you actually want. As you stated 58 sets
of 120 records is 6960 rows. However 58 sets only gets you to BF and not CF.
CF would be 84 sets and 10080 rows. I am going to do this up to CF and if you
don't want the extras you can just delete the rows.

Regards
Rowan

"Mike G" wrote:

Thanks for replying Rowan

I didn't word it particularly well at all.

The third and fourth columns are okay and remain the same.
The second column remains 01 for 12 records and then becomes 02 again
for another 12 records and then 03 for 12 records and so on until 10.
The first column remains as AA for 120 records and then becomes AB and
the records repeat up to CF.
So there are really 58 batches of 120 records - a total of 6960
records (where the hell I came up with 18720 I'm not sure).

So i do apologise for throwing you with a poor explanation.

The records are to be used for labelling a grid in a GIS application -
this might give you a better idea of what I'm trying to do - letters
for rows, numbers for columns.

Cheers,
Mike




"?B?Um93YW4=?=" wrote in message ...
And I've confused the issue further by quoting the wrong column number. I
meant to say "However you have shown column 2 staying static as 01"

"Rowan" wrote:

Mike

Can you clarify a few things. In your post you said:

The data in column 2 goes up 1 every time until it gets to 10 and then
column 1 changes to AB and so on until it gets to the letters AF

However you have shown column 1 staying static as 01. Also the example below
shows AA repeated for 12 rows which makes sense considering you want to
repeat A,B,C,D 3 times each. But in the discription above you have said AA
should change to AB after 10 rows not 12.

Obviously I have not grasped what you are after as 12 rows by AA,AB,AC,AD,AE
and AF only gives me a total of 72 rows.

Regards
Rowan

"Mike G" wrote:

I have a large amount of data in 4 columns in the format of:

1 2 3 4 header
AA 01 A 1
AA 01 A 2
AA 01 A 3
AA 01 B 1
AA 01 B 2
AA 01 B 3
AA 01 C 1
AA 01 C 2
AA 01 C 3
AA 01 D 1
AA 01 D 2
AA 01 D 3

The data in column 4 alternates 1,2,3 for the whole selection
The data in column 3 alternates as such between A,B,C and D
The data in column 2 goes up 1 every time until it gets to 10 and then
column 1 changes to AB and so on until it gets to the letters AF so in
total there will be 18720 records in total. If someone can think of
some way of automating this it would come in very handy.

Cheers,
Mike

Mike

Try this macro on a blank sheet.

Sub GISGrid()

Dim h As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim r As Long

Application.ScreenUpdating = False

r = 1
Columns("B:B").NumberFormat = "@"
For h = 1 To 58
For i = 1 To 10
For j = 1 To 12
If i < 10 Then
Cells(r, 2).Value = "0" & i
Else
Cells(r, 2).Value = i
End If
If h <= 26 Then
Cells(r, 1).Value = "A" & Chr(64 + h)
ElseIf h <= 52 Then
Cells(r, 1).Value = "B" & Chr(38 + h)
Else
Cells(r, 1).Value = "C" & Chr(12 + h)

End If
r = r + 1
Next j
Next i
Next h

For k = 1 To 6960 Step 12
Range(Cells(k, 3), Cells(k + 2, 3)).Value = "A"
Range(Cells(k + 3, 3), Cells(k + 5, 3)).Value = "B"
Range(Cells(k + 6, 3), Cells(k + 8, 3)).Value = "C"
Range(Cells(k + 9, 3), Cells(k + 11, 3)).Value = "D"
Next k

For m = 1 To 6960 Step 3
Cells(m, 4).Value = 1
Cells(m + 1, 4).Value = 2
Cells(m + 2, 4).Value = 3
Next m

Application.ScreenUpdating = True

End Sub

Regards
Rowan

"Mike G" wrote:

Thanks for replying Rowan

I didn't word it particularly well at all.

The third and fourth columns are okay and remain the same.
The second column remains 01 for 12 records and then becomes 02 again
for another 12 records and then 03 for 12 records and so on until 10.
The first column remains as AA for 120 records and then becomes AB and
the records repeat up to CF.
So there are really 58 batches of 120 records - a total of 6960
records (where the hell I came up with 18720 I'm not sure).

So i do apologise for throwing you with a poor explanation.

The records are to be used for labelling a grid in a GIS application -
this might give you a better idea of what I'm trying to do - letters
for rows, numbers for columns.

Cheers,
Mike




"?B?Um93YW4=?=" wrote in message ...
And I've confused the issue further by quoting the wrong column number. I
meant to say "However you have shown column 2 staying static as 01"

"Rowan" wrote:

Mike

Can you clarify a few things. In your post you said:

The data in column 2 goes up 1 every time until it gets to 10 and then
column 1 changes to AB and so on until it gets to the letters AF

However you have shown column 1 staying static as 01. Also the example below
shows AA repeated for 12 rows which makes sense considering you want to
repeat A,B,C,D 3 times each. But in the discription above you have said AA
should change to AB after 10 rows not 12.

Obviously I have not grasped what you are after as 12 rows by AA,AB,AC,AD,AE
and AF only gives me a total of 72 rows.

Regards
Rowan

"Mike G" wrote:

I have a large amount of data in 4 columns in the format of:

1 2 3 4 header
AA 01 A 1
AA 01 A 2
AA 01 A 3
AA 01 B 1
AA 01 B 2
AA 01 B 3
AA 01 C 1
AA 01 C 2
AA 01 C 3
AA 01 D 1
AA 01 D 2
AA 01 D 3

The data in column 4 alternates 1,2,3 for the whole selection
The data in column 3 alternates as such between A,B,C and D
The data in column 2 goes up 1 every time until it gets to 10 and then
column 1 changes to AB and so on until it gets to the letters AF so in
total there will be 18720 records in total. If someone can think of
some way of automating this it would come in very handy.

Cheers,
Mike