Excel Question - Alternate lines

[dibbers006]

The initial message was deleted from this topic on 12 December 2023 at 22:32

[droopsnoot]

Are you trying to sort existing data (the column of numbers, and some other columns of data that has to stick with each number) into that sequence, or is it sufficient to simply populate a new sheet with a single column containing numbers in that sequence?

For the latter, I'd imagine a quick bit of VBScript would handle it. Except, what happens after 14?

The sequence is basically

x=1
loop while x < 1800:
insert x then move down
insert x+7 then move down
x++
endloop

but that will cause an overlap very quickly.

Edited by droopsnoot on Thursday 4th April 11:52

Edited by droopsnoot on Thursday 4th April 12:06

[droopsnoot]

This VB Macro looks like it will do the job, or it seemed to on my test:

Sub sbCopyRange()

Dim Count, Subcount, incr
Dim Source, Dest As String

Count = 0 ' starting row to copy
incr = 1

While (Count < 1800)
  For Subcount = 1 To 7
  Source = "Sheet1!A" + CStr(Count + Subcount) + ":K" + CStr(Count + Subcount)
  Dest = "Sheet2!A" + CStr(incr)
  Range(Source).Copy Destination:=Range(Dest)
  incr = incr + 1
  Source = "Sheet1!A" + CStr(Count + Subcount + 7) + ":K" + CStr(Count + Subcount + 7)
  Dest = "Sheet2!A" + CStr(incr) 
  Range(Source).Copy Destination:=Range(Dest)
  incr = incr + 1
  Next Subcount
  Count = Count + 14
Wend

Application.CutCopyMode = False

End Sub

In that code, I've put a loop to only copy the first 1800 rows, you could change that if you needed to. I've also presumed that you're copying columns A to K - you can see in the two lines that start "Source = " how I specify from column A to column K, so you can change that if you need to.

I copy the stuff from Sheet1 to Sheet2, so if your workbook has different names you might need to change that. I thought it was easier to copy to an entirely blank sheet rather than try to move stuff around inside the same one. I guess it depends on how often you need to do this.

I am sure there are much better ways to achieve what you want, I've taken this from some sample code on the web showing how to copy and paste in a VBA macro. There's probably a better way to do the loop, but I tend to stop at the point where it seems to be working.

Edited by droopsnoot on Thursday 4th April 13:18

[droopsnoot]

No problem, glad it helps.

[davepoth]

Type 1 in row 1, 8 in row 2.

In row 3 type =A1+1

Copy down as needed to get a column in the right order.

In the next column you can do a VLOOKUP on your source data using the first column as the reference, and then it will all be in the right order.

[skyline501]

A1 = 1
A2 = A1+7
A3 = A2-6

Drag cells A2 and A3 down as far as you need.

[droopsnoot]

But, when you hit row 15, that becomes 8 (which was already in row 2), when the spec requires it to be 15 and the pattern to start again.

[droopsnoot]

That seems to just start again at 1 on row 15. As I understand it, it needs to go:

1, 8, 2, 9, 3, 10, 4, 11, 5, 12, 6, 13, 7, 14

then

15, 22, 16, 23, 17, 24, 18, 25, 19, 26, 20, 27, 21, 28

Each time the sequence starts again, the starting number increases to the next group of 14.

I'm sure it's possible without a macro, but remember it also needs to keep the other columns of data, it's not just about inserting the numbers in that sequence.

Edited by droopsnoot on Saturday 6th April 19:35