travislr11
New Member
- Joined
- Jan 20, 2022
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hello All, I recently went through a course trying to learn VBA and picked up some understanding of the language but having trouble with a project I am attempting to solve. I want to expedite organizing data for two statistical software that I use. One requires the entire data collected for a trial to be organized in a vertical layout continuously until the end of the trial. While the other requires data from each rating data to be in a vertical format but separated by each rating date and progress horizontally or across the columns. I use both software regularly because each does different things so I am trying to build a macro that will copy portions of a continuous range based on a condition and paste only the values in close proximity by the date the data was collected with headers. I was using a start row and last row variables to establish a dynamic range and then build a for each loop based on a condition by either the rating date or plot # but was not having much success. So essentially saying if this condition is or is not met, do this until it is met, then offset a certain number of columns to begin copying and pasting the next block of data. I attached the final product in the form of two mini sheets so visually you can see what it would look like if a macro were to run properly on this set of data. Any help is appreciated.
ARM Data Organization Macro.xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
4 | ||||||||||||
5 | ||||||||||||
6 | All Data Needs To Go Below This Header, With Plot #'s Being the First Column and Headers Starting in Cell A8 | |||||||||||
7 | ||||||||||||
8 | Plot # | Date | BP % | TQ | TC | |||||||
9 | 101 | Jun 15 2021 | 0 | 7.5 | 7 | |||||||
10 | 102 | Jun 15 2021 | 0 | 7.5 | 7 | |||||||
11 | 103 | Jun 15 2021 | 0 | 7.5 | 7 | |||||||
12 | 104 | Jun 15 2021 | 0 | 7.5 | 7 | |||||||
13 | 105 | Jun 15 2021 | 0 | 7.5 | 7 | |||||||
14 | 201 | Jun 15 2021 | 0 | 7 | 7 | |||||||
15 | 202 | Jun 15 2021 | 0 | 7.5 | 7 | |||||||
16 | 203 | Jun 15 2021 | 0 | 7 | 7 | |||||||
17 | 204 | Jun 15 2021 | 0 | 7 | 7 | |||||||
18 | 205 | Jun 15 2021 | 0 | 7 | 7 | |||||||
19 | 301 | Jun 15 2021 | 0 | 7.5 | 7 | |||||||
20 | 302 | Jun 15 2021 | 5 | 7 | 7 | |||||||
21 | 303 | Jun 15 2021 | 0 | 7 | 7 | |||||||
22 | 304 | Jun 15 2021 | 0 | 7 | 7 | |||||||
23 | 305 | Jun 15 2021 | 0 | 7.5 | 7 | |||||||
24 | 401 | Jun 15 2021 | 0 | 7.5 | 7 | |||||||
25 | 402 | Jun 15 2021 | 0 | 7 | 7 | |||||||
26 | 403 | Jun 15 2021 | 0 | 7 | 7 | |||||||
27 | 404 | Jun 15 2021 | 0 | 7 | 7 | |||||||
28 | 405 | Jun 15 2021 | 0 | 7.5 | 7 | |||||||
29 | 101 | Jun 29 2021 | 5 | 6.5 | 7 | |||||||
30 | 102 | Jun 29 2021 | 5 | 6.5 | 7 | |||||||
31 | 103 | Jun 29 2021 | 0 | 7 | 7 | |||||||
32 | 104 | Jun 29 2021 | 0 | 7 | 7 | |||||||
33 | 105 | Jun 29 2021 | 0 | 7.5 | 7 | |||||||
34 | 201 | Jun 29 2021 | 2 | 7 | 7 | |||||||
35 | 202 | Jun 29 2021 | 0 | 7 | 7 | |||||||
36 | 203 | Jun 29 2021 | 0 | 7 | 7 | |||||||
37 | 204 | Jun 29 2021 | 2 | 7 | 7 | |||||||
38 | 205 | Jun 29 2021 | 10 | 6.5 | 7 | |||||||
39 | 301 | Jun 29 2021 | 2 | 7 | 7 | |||||||
40 | 302 | Jun 29 2021 | 5 | 7 | 7 | |||||||
41 | 303 | Jun 29 2021 | 0 | 7 | 7 | |||||||
42 | 304 | Jun 29 2021 | 2 | 7.5 | 7 | |||||||
43 | 305 | Jun 29 2021 | 0 | 7 | 7 | |||||||
44 | 401 | Jun 29 2021 | 2 | 7 | 7 | |||||||
45 | 402 | Jun 29 2021 | 1 | 7 | 7 | |||||||
46 | 403 | Jun 29 2021 | 0 | 7 | 7 | |||||||
47 | 404 | Jun 29 2021 | 5 | 6.5 | 7 | |||||||
48 | 405 | Jun 29 2021 | 0 | 7.5 | 7 | |||||||
49 | 101 | Jun 29 2021 | 5 | 6.5 | 7 | |||||||
50 | 102 | Jun 29 2021 | 5 | 6.5 | 7 | |||||||
51 | 103 | Jun 29 2021 | 0 | 7 | 7 | |||||||
52 | 104 | Jun 29 2021 | 0 | 7 | 7 | |||||||
53 | 105 | Jun 29 2021 | 0 | 7.5 | 7 | |||||||
54 | 201 | Jun 29 2021 | 2 | 7 | 7 | |||||||
55 | 202 | Jun 29 2021 | 0 | 7 | 7 | |||||||
56 | 203 | Jun 29 2021 | 0 | 7 | 7 | |||||||
57 | 204 | Jun 29 2021 | 2 | 7 | 7 | |||||||
58 | 205 | Jun 29 2021 | 10 | 6.5 | 7 | |||||||
59 | 301 | Jun 29 2021 | 2 | 7 | 7 | |||||||
60 | 302 | Jun 29 2021 | 5 | 7 | 7 | |||||||
61 | 303 | Jun 29 2021 | 0 | 7 | 7 | |||||||
62 | 304 | Jun 29 2021 | 2 | 7.5 | 7 | |||||||
63 | 305 | Jun 29 2021 | 0 | 7 | 7 | |||||||
64 | 401 | Jun 29 2021 | 2 | 7 | 7 | |||||||
65 | 402 | Jun 29 2021 | 1 | 7 | 7 | |||||||
66 | 403 | Jun 29 2021 | 0 | 7 | 7 | |||||||
67 | 404 | Jun 29 2021 | 5 | 6.5 | 7 | |||||||
68 | 405 | Jun 29 2021 | 0 | 7.5 | 7 | |||||||
69 | 101 | July 28 2021 | 15 | 5.5 | 5 | |||||||
70 | 102 | July 28 2021 | 2 | 6.5 | 5 | |||||||
71 | 103 | July 28 2021 | 2 | 6.5 | 6 | |||||||
72 | 104 | July 28 2021 | 0 | 6.5 | 6 | |||||||
73 | 105 | July 28 2021 | 0 | 7 | 7 | |||||||
74 | 201 | July 28 2021 | 0 | 6.5 | 5 | |||||||
75 | 202 | July 28 2021 | 0 | 6.5 | 5 | |||||||
76 | 203 | July 28 2021 | 5 | 6.5 | 5 | |||||||
77 | 204 | July 28 2021 | 5 | 7 | 5 | |||||||
78 | 205 | July 28 2021 | 20 | 6 | 5 | |||||||
79 | 301 | July 28 2021 | 5 | 5 | 5 | |||||||
80 | 302 | July 28 2021 | 30 | 5 | 5 | |||||||
81 | 303 | July 28 2021 | 2 | 6.5 | 5 | |||||||
82 | 304 | July 28 2021 | 5 | 6 | 5 | |||||||
83 | 305 | July 28 2021 | 2 | 6.5 | 5 | |||||||
84 | 401 | July 28 2021 | 2 | 6 | 5 | |||||||
85 | 402 | July 28 2021 | 5 | 6.5 | 5 | |||||||
86 | 403 | July 28 2021 | 5 | 6.5 | 5 | |||||||
87 | 404 | July 28 2021 | 20 | 6 | 5 | |||||||
88 | 405 | July 28 2021 | 5 | 6 | 5 | |||||||
89 | 101 | August 12 2021 | 5 | 6.5 | 7 | |||||||
90 | 102 | August 12 2021 | 0 | 7 | 7 | |||||||
91 | 103 | August 12 2021 | 0 | 7 | 7 | |||||||
92 | 104 | August 12 2021 | 2 | 6.5 | 7 | |||||||
93 | 105 | August 12 2021 | 0 | 7.5 | 8 | |||||||
94 | 201 | August 12 2021 | 0 | 7.5 | 8 | |||||||
95 | 202 | August 12 2021 | 0 | 7 | 8 | |||||||
96 | 203 | August 12 2021 | 3 | 7 | 8 | |||||||
97 | 204 | August 12 2021 | 0 | 7.5 | 8 | |||||||
98 | 205 | August 12 2021 | 5 | 6.5 | 8 | |||||||
99 | 301 | August 12 2021 | 4 | 6 | 7 | |||||||
100 | 302 | August 12 2021 | 6 | 6 | 8 | |||||||
101 | 303 | August 12 2021 | 3 | 6.5 | 7 | |||||||
102 | 304 | August 12 2021 | 4 | 6 | 7 | |||||||
103 | 305 | August 12 2021 | 0 | 7 | 7 | |||||||
104 | 401 | August 12 2021 | 3 | 6 | 7 | |||||||
105 | 402 | August 12 2021 | 2 | 7 | 8 | |||||||
106 | 403 | August 12 2021 | 0 | 7.5 | 8 | |||||||
107 | 404 | August 12 2021 | 8 | 6 | 7 | |||||||
108 | 405 | August 12 2021 | 2 | 7 | 7 | |||||||
109 | 101 | August 24 2021 | 25 | 6 | 7 | |||||||
110 | 102 | August 24 2021 | 10 | 6 | 7 | |||||||
111 | 103 | August 24 2021 | 5 | 6.5 | 7 | |||||||
112 | 104 | August 24 2021 | 5 | 6.5 | 7 | |||||||
113 | 105 | August 24 2021 | 5 | 7.5 | 8 | |||||||
114 | 201 | August 24 2021 | 10 | 6.5 | 7 | |||||||
115 | 202 | August 24 2021 | 0 | 7 | 7 | |||||||
116 | 203 | August 24 2021 | 5 | 6.5 | 7 | |||||||
117 | 204 | August 24 2021 | 5 | 7 | 7 | |||||||
118 | 205 | August 24 2021 | 10 | 7.5 | 7 | |||||||
119 | 301 | August 24 2021 | 20 | 6 | 7 | |||||||
120 | 302 | August 24 2021 | 40 | 5 | 7 | |||||||
121 | 303 | August 24 2021 | 15 | 6 | 7 | |||||||
122 | 304 | August 24 2021 | 15 | 5.5 | 7 | |||||||
123 | 305 | August 24 2021 | 5 | 7 | 7 | |||||||
124 | 401 | August 24 2021 | 25 | 5.5 | 7 | |||||||
125 | 402 | August 24 2021 | 10 | 6.5 | 7 | |||||||
126 | 403 | August 24 2021 | 10 | 6.5 | 7 | |||||||
127 | 404 | August 24 2021 | 25 | 6 | 7 | |||||||
128 | 405 | August 24 2021 | 20 | 6 | 7 | |||||||
ARMDataOrganizer |
ARM Data Organization Macro.xlsm | |||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | |||
7 | |||||||||||||||||||||||||||||||||||||||
8 | Plot # | Date | BP % | TQ | TC | Plot # | Date | BP % | TQ | TC | Plot # | Date | BP % | TQ | TC | Plot # | Date | BP % | TQ | TC | Plot # | Date | BP % | TQ | TC | Plot # | Date | BP % | TQ | TC | Plot # | Date | BP % | TQ | TC | ||||
9 | 101 | Jun 15 2021 | 0 | 7.5 | 7 | 101 | Jun 29 2021 | 5 | 6.5 | 6.5 | 101 | Jun 29 2021 | 5 | 6.5 | 6.5 | 101 | Jun 29 2021 | 5 | 6.5 | 6.5 | 101 | July 28 2021 | 15 | 5.5 | 5 | 101 | August 12 2021 | 5 | 6.5 | 7 | 101 | August 24 2021 | 25 | 6 | 6.5 | ||||
10 | 102 | Jun 15 2021 | 0 | 7.5 | 7 | 102 | Jun 29 2021 | 5 | 6.5 | 6.5 | 102 | Jun 29 2021 | 5 | 6.5 | 6.5 | 102 | Jun 29 2021 | 5 | 6.5 | 6.5 | 102 | July 28 2021 | 2 | 6.5 | 5 | 102 | August 12 2021 | 0 | 7 | 7 | 102 | August 24 2021 | 10 | 6 | 6.5 | ||||
11 | 103 | Jun 15 2021 | 0 | 7.5 | 7 | 103 | Jun 29 2021 | 0 | 7 | 6.5 | 103 | Jun 29 2021 | 0 | 7 | 6.5 | 103 | Jun 29 2021 | 0 | 7 | 6.5 | 103 | July 28 2021 | 2 | 6.5 | 5.5 | 103 | August 12 2021 | 0 | 7 | 7 | 103 | August 24 2021 | 5 | 6.5 | 7 | ||||
12 | 104 | Jun 15 2021 | 0 | 7.5 | 7 | 104 | Jun 29 2021 | 0 | 7 | 6.5 | 104 | Jun 29 2021 | 0 | 7 | 6.5 | 104 | Jun 29 2021 | 0 | 7 | 6.5 | 104 | July 28 2021 | 0 | 6.5 | 6 | 104 | August 12 2021 | 2 | 6.5 | 7 | 104 | August 24 2021 | 5 | 6.5 | 7 | ||||
13 | 105 | Jun 15 2021 | 0 | 7.5 | 7 | 105 | Jun 29 2021 | 0 | 7.5 | 6.5 | 105 | Jun 29 2021 | 0 | 7.5 | 6.5 | 105 | Jun 29 2021 | 0 | 7.5 | 6.5 | 105 | July 28 2021 | 0 | 7 | 7 | 105 | August 12 2021 | 0 | 7.5 | 7.5 | 105 | August 24 2021 | 5 | 7.5 | 7.5 | ||||
14 | 201 | Jun 15 2021 | 0 | 7 | 7 | 201 | Jun 29 2021 | 2 | 7 | 6.5 | 201 | Jun 29 2021 | 2 | 7 | 6.5 | 201 | Jun 29 2021 | 2 | 7 | 6.5 | 201 | July 28 2021 | 0 | 6.5 | 5 | 201 | August 12 2021 | 0 | 7.5 | 7.5 | 201 | August 24 2021 | 10 | 6.5 | 6.5 | ||||
15 | 202 | Jun 15 2021 | 0 | 7.5 | 7 | 202 | Jun 29 2021 | 0 | 7 | 6.5 | 202 | Jun 29 2021 | 0 | 7 | 6.5 | 202 | Jun 29 2021 | 0 | 7 | 6.5 | 202 | July 28 2021 | 0 | 6.5 | 5 | 202 | August 12 2021 | 0 | 7 | 7.5 | 202 | August 24 2021 | 0 | 7 | 7 | ||||
16 | 203 | Jun 15 2021 | 0 | 7 | 7 | 203 | Jun 29 2021 | 0 | 7 | 6.5 | 203 | Jun 29 2021 | 0 | 7 | 6.5 | 203 | Jun 29 2021 | 0 | 7 | 6.5 | 203 | July 28 2021 | 5 | 6.5 | 5 | 203 | August 12 2021 | 3 | 7 | 7.5 | 203 | August 24 2021 | 5 | 6.5 | 6.5 | ||||
17 | 204 | Jun 15 2021 | 0 | 7 | 7 | 204 | Jun 29 2021 | 2 | 7 | 6.5 | 204 | Jun 29 2021 | 2 | 7 | 6.5 | 204 | Jun 29 2021 | 2 | 7 | 6.5 | 204 | July 28 2021 | 5 | 7 | 5 | 204 | August 12 2021 | 0 | 7.5 | 7.5 | 204 | August 24 2021 | 5 | 7 | 7 | ||||
18 | 205 | Jun 15 2021 | 0 | 7 | 7 | 205 | Jun 29 2021 | 10 | 6.5 | 6.5 | 205 | Jun 29 2021 | 10 | 6.5 | 6.5 | 205 | Jun 29 2021 | 10 | 6.5 | 6.5 | 205 | July 28 2021 | 20 | 6 | 5 | 205 | August 12 2021 | 5 | 6.5 | 7.5 | 205 | August 24 2021 | 10 | 7.5 | 7 | ||||
19 | 301 | Jun 15 2021 | 0 | 7.5 | 7 | 301 | Jun 29 2021 | 2 | 7 | 6.5 | 301 | Jun 29 2021 | 2 | 7 | 6.5 | 301 | Jun 29 2021 | 2 | 7 | 6.5 | 301 | July 28 2021 | 5 | 5 | 5 | 301 | August 12 2021 | 4 | 6 | 7 | 301 | August 24 2021 | 20 | 6 | 7 | ||||
20 | 302 | Jun 15 2021 | 5 | 7 | 7 | 302 | Jun 29 2021 | 5 | 7 | 6.5 | 302 | Jun 29 2021 | 5 | 7 | 6.5 | 302 | Jun 29 2021 | 5 | 7 | 6.5 | 302 | July 28 2021 | 30 | 5 | 5 | 302 | August 12 2021 | 6 | 6 | 7.5 | 302 | August 24 2021 | 40 | 5 | 7 | ||||
21 | 303 | Jun 15 2021 | 0 | 7 | 7 | 303 | Jun 29 2021 | 0 | 7 | 6.5 | 303 | Jun 29 2021 | 0 | 7 | 6.5 | 303 | Jun 29 2021 | 0 | 7 | 6.5 | 303 | July 28 2021 | 2 | 6.5 | 5 | 303 | August 12 2021 | 3 | 6.5 | 7 | 303 | August 24 2021 | 15 | 6 | 7 | ||||
22 | 304 | Jun 15 2021 | 0 | 7 | 7 | 304 | Jun 29 2021 | 2 | 7.5 | 6.5 | 304 | Jun 29 2021 | 2 | 7.5 | 6.5 | 304 | Jun 29 2021 | 2 | 7.5 | 6.5 | 304 | July 28 2021 | 5 | 6 | 5 | 304 | August 12 2021 | 4 | 6 | 6.5 | 304 | August 24 2021 | 15 | 5.5 | 7 | ||||
23 | 305 | Jun 15 2021 | 0 | 7.5 | 7 | 305 | Jun 29 2021 | 0 | 7 | 6.5 | 305 | Jun 29 2021 | 0 | 7 | 6.5 | 305 | Jun 29 2021 | 0 | 7 | 6.5 | 305 | July 28 2021 | 2 | 6.5 | 5 | 305 | August 12 2021 | 0 | 7 | 7 | 305 | August 24 2021 | 5 | 7 | 7 | ||||
24 | 401 | Jun 15 2021 | 0 | 7.5 | 7 | 401 | Jun 29 2021 | 2 | 7 | 6.5 | 401 | Jun 29 2021 | 2 | 7 | 6.5 | 401 | Jun 29 2021 | 2 | 7 | 6.5 | 401 | July 28 2021 | 2 | 6 | 5 | 401 | August 12 2021 | 3 | 6 | 7 | 401 | August 24 2021 | 25 | 5.5 | 6.5 | ||||
25 | 402 | Jun 15 2021 | 0 | 7 | 7 | 402 | Jun 29 2021 | 1 | 7 | 6.5 | 402 | Jun 29 2021 | 1 | 7 | 6.5 | 402 | Jun 29 2021 | 1 | 7 | 6.5 | 402 | July 28 2021 | 5 | 6.5 | 5 | 402 | August 12 2021 | 2 | 7 | 7.5 | 402 | August 24 2021 | 10 | 6.5 | 7 | ||||
26 | 403 | Jun 15 2021 | 0 | 7 | 7 | 403 | Jun 29 2021 | 0 | 7 | 6.5 | 403 | Jun 29 2021 | 0 | 7 | 6.5 | 403 | Jun 29 2021 | 0 | 7 | 6.5 | 403 | July 28 2021 | 5 | 6.5 | 5 | 403 | August 12 2021 | 0 | 7.5 | 7.5 | 403 | August 24 2021 | 10 | 6.5 | 7 | ||||
27 | 404 | Jun 15 2021 | 0 | 7 | 7 | 404 | Jun 29 2021 | 5 | 6.5 | 6.5 | 404 | Jun 29 2021 | 5 | 6.5 | 6.5 | 404 | Jun 29 2021 | 5 | 6.5 | 6.5 | 404 | July 28 2021 | 20 | 6 | 5 | 404 | August 12 2021 | 8 | 6 | 7 | 404 | August 24 2021 | 25 | 6 | 7 | ||||
28 | 405 | Jun 15 2021 | 0 | 7.5 | 7 | 405 | Jun 29 2021 | 0 | 7.5 | 6.5 | 405 | Jun 29 2021 | 0 | 7.5 | 6.5 | 405 | Jun 29 2021 | 0 | 7.5 | 6.5 | 405 | July 28 2021 | 5 | 6 | 5 | 405 | August 12 2021 | 2 | 7 | 6.5 | 405 | August 24 2021 | 20 | 6 | 7 | ||||
29 | |||||||||||||||||||||||||||||||||||||||
ARMDataOrganizer |