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]

Furthermore, both columns need to shrink to 50 rows.

Sorry but I didn't understand what you mean by reducing to 50.
You could put a sample of what you have and the result you want.

 

[alive15]

Sorry, not 50, the correct # of rows per column is 5,000 rows.

I want to shrink the first column (originally 10,000 rows) into two columns of 5,000 rows each.

 

[DanteAmor]

Try this, The results in columns B and C

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

 

[sandy666]

with Power Query

Data		Column1	Column2
C1		C1	2
2		C3	4
4		C5	6
C3
C5
6

// Table2
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    IF = Table.AddColumn(Table.TransformColumnTypes(Source,{{"Data", type text}}), "Custom", each if Text.Contains([Data], "C") then true else false),
    List = Table.AddColumn(Table.Group(IF, {"Custom"}, {{"Count", each _, type table}}), "Custom.1", each Table.Column([Count],"Data")),
    Extract = Table.TransformColumns(List, {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Table.SelectColumns(Extract,{"Custom.1"}), "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3"}),
    Top5000 = Table.FirstN(Table.Transpose(Split),5000)
in
    Top5000

 

[alive15]

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

 

[alive15]

Sandy, I don't know how to use power query, I'm unsure where to paste your code.

Thanks,

 

[sandy666]

I suggest to read this before you start with Power Query aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference

 

[alive15]

I appreciate the links Sandy, but I am not a big excel user at my office, so for me, it is not worth spending an hour or so studying up on it. I'm grateful for the help you guys are giving me, but I am just looking for something simple to get my work done (obviously writing code is not simple, but copying and pasting code is very simple).

Are you familiar with Dante's code by any chance? If you are able to help me with that code, that would be excellent for me. I am just looking to get my work organized and then move on to the next task. Thanks again, I don't mean to offend you or devalue your efforts.

 

[sandy666]

so try this example
btw, what is your Excel version?

about vba read my 2nd line at bottom of my post