How to move rows into columns

[alive15]

Good afternoon all, hope all is well today. I have one column with ten-thousand rows of data. I need to extract all the even numbered rows and move it into column B. Furthermore, both columns need to shrink to 50 rows. How can I accomplish this faster without manually cutting and pasting?

Notes: All odd numbered rows begin with "C", all even numbered rows begin with any number between "0-9".

Thanks! Have a great day.

 

[DanteAmor]

Dante, I tried your code. It did move the correct data to the B column. But column A still looks the same, it did not delete the even numbered rows and did not shrink column A to 5000

I kept column A, so the negatives are in column B and the positives are in column C.
If you don't want column A simply delete column A.
Or add this line to the end of the macro

Columns("A").Delete

Try please:

Sub move_rows()
  Dim a(), b(), i As Long
  a = Range("A1:A10000").Value
  ReDim b(1 To 5000, 1 To 2)
  For i = 1 To 10000 Step 2
    b((i + 1) / 2, 1) = a(i, 1)
    b((i + 1) / 2, 2) = a(i + 1, 1)
  Next
  Range("B1").Resize(5000, 2).Value = b
  Columns("A").Delete
End Sub

 

[navic]

@alive15
Try this idea
Put this formula in a C2 cell and copy down

=IF(LEFT(A2,1)="C",A2,"X")

Put this formula in a D2 cell and copy down

=IFERROR(IF(ISEVEN(A2)=TRUE,A2,"Y"),"Y")

Move 'C' column 'to Sheet2, filter by' X 'and delete all filtered rows. Remove the filter. Now you only have the information starting with the 'C' letter.

Do the same for 'Y' filtering.

If you need see How to quick copy formula down?

 

[navic]

OMG, just now I realized my mistake. I did not understand the question well.

If you are not familiar with VBA?
Try

C2 and copy down =IFERROR(IF(ISEVEN(ROW())<>TRUE,A1," ")," ")
D2 and copy down =IFERROR(IF(ISEVEN(ROW())=TRUE,A1," ")," ")

After, select column range -> 'Go To Special' and delete blank cells

 

[alive15]

Thanks Dante, your original code was actually correct; my data is very difficult to read, so initially it did not look correct, but after further review, it was correct. Thanks again! Now I am able to move forward on my project and continue doing other things.

I appreciate everyone's response; I will try your idea also Navic, and I will let you know if it works or not. Could you explain your recent code also? I don't understand the part after A1 on both lines, what is this doing? If i can get your code to work also, that may be faster than Dante's method, but both seem really good.

 

[DanteAmor]

Thanks Dante, your original code was actually correct; my data is very difficult to read, so initially it did not look correct, but after further review, it was correct. Thanks again! Now I am able to move forward on my project and continue doing other things.

I'm glad to help you. Thanks for the feedback.

 

[navic]

Could you explain your recent code also? I don't understand the part after A1 on both lines, what is this doing?

I will try
The basic formula 'ROW' is as follows (This formula return the row number it is in.)
If you put the formula '=ROW()' in the seventh row then the result will be 7, if you put the formula in the eleventh row then the result will be 11, etc.

=ROW()

If we nest this ROW function in the ISEVEN function then we have the formula below. (The ISEVEN function returns 'TRUE/FALSE' as a result. Returns TRUE if number is even, or FALSE if number is odd.)
I hope you understand the nested Excel formulas.

=ISEVEN(ROW())

The following is an 'IF function' that has the following syntax "=IF(logical_test,value_if_true,value_if_false)".
Let's replace the first argument 'logical_test' with the ISEVEN function and we have the formula below now.

=IF(ISEVEN(ROW())<>TRUE,A1," ")

So if the logical test is 'TRUE' the formula returns the data from the 'A1' cell ( (in this case in 1st formula '<>TRUE') ), and if the result is 'FALSE' the formula returns an empty cell.
The next Excel function is IFERROR. Its syntax is "=IFERROR(value,value_if_error)".
We replace the first 'value' argument with the 'IF' formula. And finally the formula looks like below. (This formula prevents visual display errors or showing zero.)

=IFERROR(IF(ISEVEN(ROW())<>TRUE,A1," ")," ")

I hope I was able to explain the formula to you. If I didn't explain it well, someone else would correct me.

btw: You try using the 'F9' key to calculate a particular function argument within a formula.

 

[Aureliusz Nowakowski]

VBA Code:
Sub move_rows()
Dim a(), b(), i As Long, j As Long
a = Range("A1:A10000").Value
ReDim b(1 To 5000, 1 To 2)
j = 1
For i = 1 To 10000 Step 2
b(j, 1) = a(i, 1)
b(j, 2) = a(i + 1, 1)
j = j + 1
Next
Range("B1").Resize(5000, 2).Value = b
End Sub