What is the macro to move data from column to different column and row

[matahazel28]

Dear all,

I am a beginner here. I would like to ask a help for someone who can help me to solve the problem. I have around 800 data in excel and I want to move data from one column to different column and row.
It is difficult to say here, I will give the example in the table below.

Please ignore the colors, they are just marker for easy visualization.
So, I have around 20 data here. I have to "move" data number one "yellow cell" to column (A1), then data number two "blue cell" to column (A1'). After that data number three to column (A1), data number four to column (A1') so on until every data move to column A1 or A1'. Therefore, Every data from odd number moves to column A1 and every data from even number move to column A1'
I have around 800 data and it is impossible to do all manually. It takes many times and energy. Does anyone want to help me how to work efficiently and rapidly? Do I need run some script in Macro?
Please help me.
Thank you for the attention.

EVERY DATA FROM ODD NUMBER MOVES TO COLUMN A1 AND ANY EVEN DATA MOVE TO COLUMN A1'

 

[steve the fish]

You can use a formula:

=INDEX($D$2:$D$20,ROW(D2)*2+COLUMN(D2)-ROW($D$2)-COLUMN($D$2)-1)

Drag across and down until it starts to error.

 

[Snakehips]

EVERY DATA FROM ODD NUMBER MOVES TO COLUMN A1 AND ANY EVEN DATA MOVE TO COLUMN A1'. is a tad confusing.
I stand to be corrected but my interpretation is that odd values move to A and even values move to A' ?

Here is vba that should accomplish that, if indeed it is correct.

TEST_MREXCEL.xlsm
	A	B	C	D
1	Inividu	A	A'	Data
2	1	23	34	23
3	2	43	56	34
4	3	13	78	43
5	4	89	32	56
6	5	45	56	78
7	6		44	32
8	7		76	13
9	8			56
10	9			89
11				44
12				76
13				45
14
Sheet3

Sub SortCols()
Dim DatRng() As Variant
Dim Odds As Variant
Dim Evens As Variant
Dim c As Integer
Dim o As Integer
Dim e As Integer

Range("B:C").ClearContents
Lastr = Cells(Rows.Count, 4).End(xlUp).Row
ReDim Odds(Lastr)
ReDim Evens(Lastr)
ReDim DatRng(Lastr)

Odds(0) = "A"
Evens(0) = "A'"

DatRng = Application.Transpose(Range("D2:D" & Lastr))
For c = 1 To UBound(DatRng)
    If DatRng(c) Mod 2 = 0 Then
        e = e + 1
        Evens(e) = DatRng(c)
        Else
        o = o + 1
            Odds(o) = DatRng(c)    
End If
Next c

Range("B1:B" & Lastr) = Application.Transpose(Odds)
Range("C1:C" & Lastr) = Application.Transpose(Evens)

End Sub

Hope that helps.

 

[matahazel28]

I am sorry, "EVERY DATA FROM ODD NUMBER MOVES TO COLUMN A1 AND ANY EVEN DATA MOVE TO COLUMN A1'." was a mistake.
But your interpretation is true @Snakehips
I have tried your VBA macro and It worked!
here the example of running

Thank you so much for helping me.
Please stay healthy and happy!

 

[matahazel28]

I am sorry, I was wrong. But, the sentences about "Therefore, Every data from odd number moves to column A1 and every data from even number move to column A1'" did not mean like your interpretation.

I hope like this

data number 1 move to A1 column, data number 2 move to A1' column
data number 3 move to A1 column, data number 4 move to A1' column
data number 5 move to A1 column, data number 6 move to A1' column
so on.....

so, the odd number and even number in this sentence "Therefore, Every data from odd number moves to column A1 and every data from even number move to column A1'" are the number of individu. It was different with your interpretation @Snakehips. Therefore, A1 or A1' column can contain either odd/even values.
Could you please help me to modify your scripts?
Thank you for the kindness.

 

[Snakehips]

Ok. @steve the fish was spot on and his formula does the trick.

If you want to tweak my code to do the same thing then just change the line

If DatRng(c) Mod 2 = 0 Then

to

If c Mod 2 = 0 Then

Hope that helps.

 

[matahazel28]

Ok. @steve the fish was spot on and his formula does the trick.

If you want to tweak my code to do the same thing then just change the line

If DatRng(c) Mod 2 = 0 Then

to

If c Mod 2 = 0 Then

Hope that helps.

Thank you sir @Snakehips