Data arrangement macro

[makinmomb]

I want to re arrange my data as follows , if i do manually it will take long
, cell reference A meaning on sheet 2

D3	TO	A1
E3	TO	A2
J3	TO	A3
O3	TO	A4
N3	TO	A5
F3	TO	A6
G3	TO	A7
I3	TO	A8
H3	TO	A9
B3	TO	A10
K3	TO	A11
M3	TO	A12
C3	TO	A13
W3	TO	A14
U3	TO	A15
V3	TO	A16
S3	TO	A17
T3	TO	A18
X3	TO	A19
AA3	TO	A20
Y3	TO	A21

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[footoo]

Here's a way without VBA.

Enter the Sheet1 cell refs in column B of Sheet2
Enter in A1 of Sheet2 and fill down : =INDIRECT("Sheet1!" & B1)
Convert Sheet2 column A to value
Delete Sheet2 column B

 

[makinmomb]

I need a macro , i dont know how indirect works

 

[makinmomb]

I have to data sheets of like 100 rows thus need this macro badly before this work eats away my day doing it the painful manner

 

[footoo]

Why do you need a macro?
By the time you copy, paste and run a macro you can already do it per post #2 (which will take about 1 or 2 minutes).
You've already wasted a few hours by re-posting.

i dont know how indirect works

Have a look here : https://support.office.com/en-us/article/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261

 

[Rick Rothstein]

Assuming your data starts on Row 3 (as your posted example suggests), and assuming you want the output to go to Sheet2, give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub ReorderTransposeColumns()
  Dim LastRow As Long, Rng As Range
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
  Sheets("Sheet2").Range("A1").Resize(21, LastRow - 2) = Application.Transpose(Application.Index(Cells, Evaluate("ROW(3:" & LastRow & ")"), Split("4 5 10 15 14 6 7 9 8 2 11 13 3 23 21 22 19 20 24 27 25")))
End Sub[/td]
[/tr]
[/table]

 

[makinmomb]

Rick thank you , i dont need even the extra sheets , i hope the macro will work too for say 10 trucks , thus were data for 3 trucks

 

[makinmomb]

Rick can you please edit the macro to correct this output , at where it picks 3195000 there needs to be
1500 prior that being the quantity of the value , like it did 500 followed by 1185000

911000
0
0
0
0
3195000
500
1185000

<colgroup><col></colgroup><tbody>
</tbody>

 

[makinmomb]

I must say its rick who introduced me to macros , i recall he even gave
me the the keys to get to the module and run a macro , since then i have
saved tons of hours and courtesy thx Rick to have changed my style of
how i use Excel , i am sure to have saved tons of hours and rocking to
better jobs due to the speed macros give

 

[makinmomb]

Oh yeah it extracts records endlessly , i tried to append data at below the file and does the needful , thank you rick again for taking care of that too