Hi, I've done a lot of searching and I can't seem to find a macro to do what I need. I'm using Excel 2016 on a Windows7 system.
I want to transfer the data from sheet1 to sheet2 but I need transfer the peaks into the correct peak# slots in Sheet2.
My example Sheet1 includes a small portion of the data. There are actually 126 samples x 4 treatments. Two treatments have 78 peaks and two treatments have 97 peaks. Each treatment is in a separate excel file. Each sample within the treatments is missing some peaks.
My example Sheet2 shows how I need the data to look when its moved and sorted.
Thanks for any help that you can provide,
Charles
<tbody>
</tbody>
<tbody>
</tbody>
I want to transfer the data from sheet1 to sheet2 but I need transfer the peaks into the correct peak# slots in Sheet2.
My example Sheet1 includes a small portion of the data. There are actually 126 samples x 4 treatments. Two treatments have 78 peaks and two treatments have 97 peaks. Each treatment is in a separate excel file. Each sample within the treatments is missing some peaks.
My example Sheet2 shows how I need the data to look when its moved and sorted.
Thanks for any help that you can provide,
Charles
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
1 | 10a_aa_post | 10b_aa_post | 10c_aa_post | |||
2 | Peak Number | Area | Peak Number | Area | Peak Number | Area |
3 | 1 | 1711998 | 1 | 1853650 | 1 | 1847710 |
4 | 2 | 8029340 | 2 | 5611290 | 2 | 6016448 |
5 | 3 | 4915881 | 3 | 8775656 | 3 | 3215693 |
6 | 4 | 38680016 | 4 | 71855765 | 4 | 35540418 |
7 | 5 | 519028 | 5 | 587182 | 5 | 614839 |
8 | 6 | 1192247 | 6 | 1500691 | 6 | 1402716 |
9 | 7 | 1234680 | 7 | 1428559 | 7 | 1560591 |
10 | 8 | 5548756 | 8 | 12625992 | 8 | 12906065 |
11 | 9 | 605775 | 9 | 1253483 | 9 | 817432 |
12 | 11 | 554808 | 10 | 53626 | 11 | 779580 |
13 | 12 | 8228298 | 11 | 522073 | 12 | 10264538 |
14 | 13 | 21971054 | 12 | 13236265 | 13 | 30499693 |
15 | 14 | 14765239 | 13 | 13706866 | 14 | 9260197 |
16 | 15 | 14542104 | 14 | 15704812 | 15 | 14630105 |
17 | 16 | 178358803 | 15 | 20114122 | 16 | 1.89E+08 |
18 | 17 | 185498 | 16 | 1.88E+08 | 17 | 196856 |
19 | 18 | 281996 | 17 | 256745 | 18 | 366896 |
20 | 19 | 456211 | 19 | 209729 | 19 | 2096169 |
21 | 21 | 12693321 | 20 | 1.25E+08 | 21 | 6659092 |
22 | 23 | 39389980 | 21 | 12803776 | 22 | 108981 |
23 | 24 | 19395726 | 22 | 186847 | 23 | 35203858 |
24 | 25 | 12568227 | 23 | 53923881 | 24 | 30211645 |
25 | 26 | 2362588 | 24 | 11055736 | 25 | 13345519 |
26 | 27 | 7429453 | 25 | 16060882 | 26 | 1377612 |
27 | 29 | 511484 | 26 | 1978827 | 27 | 7859924 |
28 | 30 | 198494680 | 27 | 7689519 | 29 | 879285 |
29 | 31 | 488886 | 29 | 172378 | 30 | 2.17E+08 |
30 | 32 | 1636628 | 30 | 2.15E+08 | 31 | 191226 |
31 | 33 | 11351019 | 31 | 155564 | 32 | 1790381 |
32 | 34 | 589676464 | 32 | 1787199 | 33 | 18733803 |
33 | 35 | 61658145 | 33 | 5966999 | 34 | 5.83E+08 |
34 | 36 | 135309918 | 34 | 5.19E+08 | 35 | 1.03E+08 |
35 | 37 | 1149078 | 35 | 82092558 | 36 | 1.32E+08 |
36 | 39 | 483099 | 36 | 1.53E+08 | 37 | 1672384 |
37 | 41 | 213464591 | 37 | 453385 | 39 | 380146 |
38 | 42 | 21380137 | 39 | 95584 | 41 | 1.57E+08 |
39 | 43 | 2595170 | 41 | 2.32E+08 | 42 | 50685538 |
40 | 44 | 22642148 | 42 | 28205400 | 43 | 3036323 |
41 | 45 | 25717903 | 43 | 2356221 | 44 | 42633226 |
42 | 46 | 35029124 | 44 | 20225805 | 45 | 68955608 |
43 | 48 | 8802185 | 45 | 17637885 | 46 | 65401995 |
44 | 49 | 6341506 | 46 | 26788062 | 47 | 5308973 |
45 | 50 | 127059 | 48 | 10375892 | 48 | 11641826 |
46 | 51 | 2430990 | 49 | 3670994 | 49 | 11754336 |
47 | 52 | 7812739 | 50 | 296236 | 50 | 1142407 |
48 | 54 | 97095 | 51 | 2197993 | 51 | 4276465 |
49 | 55 | 14840263 | 52 | 8285574 | 52 | 10141213 |
50 | 56 | 5176166 | 55 | 4696769 | 54 | 372645 |
51 | 57 | 158891347 | 56 | 5889741 | 55 | 26558749 |
52 | 58 | 28025724 | 57 | 2.3E+08 | 56 | 12618311 |
53 | 59 | 144408114 | 58 | 27776530 | 57 | 1.82E+08 |
54 | 60 | 7149809 | 59 | 3.83E+08 | 58 | 1.18E+08 |
55 | 61 | 1031245 | 60 | 9885661 | 59 | 2.12E+08 |
56 | 62 | 9508156 | 61 | 275596 | 60 | 5176742 |
57 | 63 | 25611499 | 62 | 22955110 | 61 | 478733 |
58 | 64 | 9532541 | 63 | 12290893 | 62 | 14754304 |
59 | 65 | 3854284 | 64 | 7381827 | 63 | 45035197 |
60 | 66 | 163786299 | 65 | 3115115 | 64 | 9162606 |
61 | 67 | 24907271 | 66 | 1.73E+08 | 65 | 4606021 |
62 | 68 | 2426568 | 67 | 26776087 | 66 | 1.92E+08 |
63 | 69 | 3569924 | 68 | 2891300 | 67 | 30971809 |
64 | 70 | 3505023 | 69 | 6338285 | 68 | 3145373 |
65 | 71 | 2584635 | 70 | 4383205 | 69 | 5170261 |
66 | 72 | 979883 | 71 | 3652839 | 70 | 4761501 |
67 | 73 | 335284 | 72 | 807410 | 71 | 6291664 |
68 | 74 | 1959171609 | 73 | 164844 | 72 | 1702351 |
69 | 75 | 28344644 | 74 | 2.07E+09 | 73 | 373766 |
70 | 76 | 244084878 | 75 | 20728256 | 74 | 1.76E+09 |
71 | 77 | 21866630 | 76 | 2.52E+08 | 75 | 43787099 |
72 | 78 | 54717563 | 77 | 36394594 | 76 | 2.88E+08 |
73 | 78 | 1.26E+08 | 77 | 38370513 | ||
74 | 78 | 1.15E+08 |
<tbody>
</tbody>
Sheet1
A | B | C | D | |
---|---|---|---|---|
1 | PeakNumber | 10a_aa_post | 10b_aa_post | 10c_aa_post |
2 | 1 | 1711998 | 1853650 | 1847710 |
3 | 2 | 8029340 | 5611290 | 6016448 |
4 | 3 | 4915881 | 8775656 | 3215693 |
5 | 4 | 38680016 | 71855765 | 35540418 |
6 | 5 | 519028 | 587182 | 614839 |
7 | 6 | 1192247 | 1500691 | 1402716 |
8 | 7 | 1234680 | 1428559 | 1560591 |
9 | 8 | 5548756 | 12625992 | 12906065 |
10 | 9 | 605775 | 1253483 | 817432 |
11 | 10 | 53626 | ||
12 | 11 | 554808 | 522073 | 779580 |
13 | 12 | 8228298 | 13236265 | 10264538 |
14 | 13 | 21971054 | 13706866 | 30499693 |
15 | 14 | 14765239 | 15704812 | 9260197 |
16 | 15 | 14542104 | 20114122 | 14630105 |
17 | 16 | 178358803 | 188259221 | 189161699 |
18 | 17 | 185498 | 256745 | 196856 |
19 | 18 | 281996 | 366896 | |
20 | 19 | 456211 | 209729 | 2096169 |
21 | 20 | 124819302 | ||
22 | 21 | 12693321 | 12803776 | 6659092 |
23 | 22 | 186847 | 108981 | |
24 | 23 | 39389980 | 53923881 | 35203858 |
25 | 24 | 19395726 | 11055736 | 30211645 |
26 | 25 | 12568227 | 16060882 | 13345519 |
27 | 26 | 2362588 | 1978827 | 1377612 |
28 | 27 | 7429453 | 7689519 | 7859924 |
29 | 28 | |||
30 | 29 | 511484 | 172378 | 879285 |
31 | 30 | 198494680 | 215474018 | 217062680 |
32 | 31 | 488886 | 155564 | 191226 |
33 | 32 | 1636628 | 1787199 | 1790381 |
34 | 33 | 11351019 | 5966999 | 18733803 |
35 | 34 | 589676464 | 519358121 | 583149402 |
36 | 35 | 61658145 | 82092558 | 103154593 |
37 | 36 | 135309918 | 153243824 | 132203164 |
38 | 37 | 1149078 | 453385 | 1672384 |
39 | 38 | |||
40 | 39 | 483099 | 95584 | 380146 |
41 | 40 | |||
42 | 41 | 213464591 | 232201644 | 156812752 |
43 | 42 | 21380137 | 28205400 | 50685538 |
44 | 43 | 2595170 | 2356221 | 3036323 |
45 | 44 | 22642148 | 20225805 | 42633226 |
46 | 45 | 25717903 | 17637885 | 68955608 |
47 | 46 | 35029124 | 26788062 | 65401995 |
48 | 47 | 5308973 | ||
49 | 48 | 8802185 | 10375892 | 11641826 |
50 | 49 | 6341506 | 3670994 | 11754336 |
51 | 50 | 127059 | 296236 | 1142407 |
52 | 51 | 2430990 | 2197993 | 4276465 |
53 | 52 | 7812739 | 8285574 | 10141213 |
54 | 53 | |||
55 | 54 | 97095 | 372645 | |
56 | 55 | 14840263 | 4696769 | 26558749 |
57 | 56 | 5176166 | 5889741 | 12618311 |
58 | 57 | 158891347 | 230074510 | 182362907 |
59 | 58 | 28025724 | 27776530 | 117636571 |
60 | 59 | 144408114 | 383111620 | 212149788 |
61 | 60 | 7149809 | 9885661 | 5176742 |
62 | 61 | 1031245 | 275596 | 478733 |
63 | 62 | 9508156 | 22955110 | 14754304 |
64 | 63 | 25611499 | 12290893 | 45035197 |
65 | 64 | 9532541 | 7381827 | 9162606 |
66 | 65 | 3854284 | 3115115 | 4606021 |
67 | 66 | 163786299 | 173263366 | 191733905 |
68 | 67 | 24907271 | 26776087 | 30971809 |
69 | 68 | 2426568 | 2891300 | 3145373 |
70 | 69 | 3569924 | 6338285 | 5170261 |
71 | 70 | 3505023 | 4383205 | 4761501 |
72 | 71 | 2584635 | 3652839 | 6291664 |
73 | 72 | 979883 | 807410 | 1702351 |
74 | 73 | 335284 | 164844 | 373766 |
75 | 74 | 1959171609 | 2066600923 | 1758841931 |
76 | 75 | 28344644 | 20728256 | 43787099 |
77 | 76 | 244084878 | 252196455 | 287838008 |
78 | 77 | 21866630 | 36394594 | 38370513 |
79 | 78 | 54717563 | 126288053 | 114539600 |
<tbody>
</tbody>
Sheet2