Macro to transfer data from sheet1 to sheet2

[Karp40]

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

	A	B	C	D	E	F
10a_aa_post	10b_aa_post	10c_aa_post
Peak Number	Area	Peak Number	Area	Peak Number	Area

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1711998[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1853650[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1847710[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8029340[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5611290[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6016448[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4915881[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8775656[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3215693[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]38680016[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]71855765[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]35540418[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]519028[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]587182[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]614839[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1192247[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1500691[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1402716[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1234680[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1428559[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1560591[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5548756[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]12625992[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]12906065[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]605775[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1253483[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]817432[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]554808[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]53626[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]779580[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8228298[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]522073[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10264538[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]21971054[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13236265[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]30499693[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14765239[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]13706866[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]9260197[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]14542104[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15704812[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]14630105[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]178358803[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20114122[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1.89E+08[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]185498[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1.88E+08[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]196856[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]281996[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]256745[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]366896[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]456211[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]209729[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]2096169[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]12693321[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1.25E+08[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]6659092[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]39389980[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]12803776[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]108981[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]19395726[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]186847[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]35203858[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]12568227[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]53923881[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]30211645[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]2362588[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]11055736[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]13345519[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]7429453[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]16060882[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]1377612[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]511484[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]1978827[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]7859924[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]198494680[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]7689519[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]879285[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]488886[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]172378[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2.17E+08[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]1636628[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2.15E+08[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]191226[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]11351019[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]155564[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]1790381[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]589676464[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]1787199[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]18733803[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]61658145[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]5966999[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]5.83E+08[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]135309918[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]5.19E+08[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]1.03E+08[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]1149078[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]82092558[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]1.32E+08[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]483099[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]1.53E+08[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]1672384[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]213464591[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]453385[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]380146[/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]21380137[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]95584[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]1.57E+08[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]2595170[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]2.32E+08[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]50685538[/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]22642148[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]28205400[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]3036323[/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]25717903[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]2356221[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]42633226[/TD]

[TD="align: center"]42[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]35029124[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]20225805[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]68955608[/TD]

[TD="align: center"]43[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]8802185[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]17637885[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]65401995[/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]6341506[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]26788062[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]5308973[/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]127059[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]10375892[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]11641826[/TD]

[TD="align: center"]46[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]2430990[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]3670994[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]11754336[/TD]

[TD="align: center"]47[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]7812739[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]296236[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1142407[/TD]

[TD="align: center"]48[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]97095[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]2197993[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4276465[/TD]

[TD="align: center"]49[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]14840263[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]8285574[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]10141213[/TD]

[TD="align: center"]50[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]5176166[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]4696769[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]372645[/TD]

[TD="align: center"]51[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]158891347[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]5889741[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]26558749[/TD]

[TD="align: center"]52[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]28025724[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]2.3E+08[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]12618311[/TD]

[TD="align: center"]53[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]144408114[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]27776530[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]1.82E+08[/TD]

[TD="align: center"]54[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]7149809[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]3.83E+08[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]1.18E+08[/TD]

[TD="align: center"]55[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]1031245[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]9885661[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]2.12E+08[/TD]

[TD="align: center"]56[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]9508156[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]275596[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]5176742[/TD]

[TD="align: center"]57[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]25611499[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]22955110[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]478733[/TD]

[TD="align: center"]58[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]9532541[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]12290893[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]14754304[/TD]

[TD="align: center"]59[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]3854284[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]7381827[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]45035197[/TD]

[TD="align: center"]60[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]163786299[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]3115115[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]9162606[/TD]

[TD="align: center"]61[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]24907271[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]1.73E+08[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]4606021[/TD]

[TD="align: center"]62[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]2426568[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]26776087[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]1.92E+08[/TD]

[TD="align: center"]63[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]3569924[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]2891300[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]30971809[/TD]

[TD="align: center"]64[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]3505023[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]6338285[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]3145373[/TD]

[TD="align: center"]65[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]2584635[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]4383205[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]5170261[/TD]

[TD="align: center"]66[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]979883[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]3652839[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]4761501[/TD]

[TD="align: center"]67[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]335284[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]807410[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]6291664[/TD]

[TD="align: center"]68[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]1959171609[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]164844[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]1702351[/TD]

[TD="align: center"]69[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]28344644[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]2.07E+09[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]373766[/TD]

[TD="align: center"]70[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]244084878[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]20728256[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]1.76E+09[/TD]

[TD="align: center"]71[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]21866630[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]2.52E+08[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]43787099[/TD]

[TD="align: center"]72[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]54717563[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]36394594[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]2.88E+08[/TD]

[TD="align: center"]73[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]1.26E+08[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]38370513[/TD]

[TD="align: center"]74[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]1.15E+08[/TD]

</tbody>

Sheet1​

	A	B	C	D
PeakNumber	10a_aa_post	10b_aa_post	10c_aa_post

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1711998[/TD]
[TD="align: right"]1853650[/TD]
[TD="align: right"]1847710[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8029340[/TD]
[TD="align: right"]5611290[/TD]
[TD="align: right"]6016448[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4915881[/TD]
[TD="align: right"]8775656[/TD]
[TD="align: right"]3215693[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]38680016[/TD]
[TD="align: right"]71855765[/TD]
[TD="align: right"]35540418[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]519028[/TD]
[TD="align: right"]587182[/TD]
[TD="align: right"]614839[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1192247[/TD]
[TD="align: right"]1500691[/TD]
[TD="align: right"]1402716[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1234680[/TD]
[TD="align: right"]1428559[/TD]
[TD="align: right"]1560591[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5548756[/TD]
[TD="align: right"]12625992[/TD]
[TD="align: right"]12906065[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]605775[/TD]
[TD="align: right"]1253483[/TD]
[TD="align: right"]817432[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]53626[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]554808[/TD]
[TD="align: right"]522073[/TD]
[TD="align: right"]779580[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8228298[/TD]
[TD="align: right"]13236265[/TD]
[TD="align: right"]10264538[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]21971054[/TD]
[TD="align: right"]13706866[/TD]
[TD="align: right"]30499693[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14765239[/TD]
[TD="align: right"]15704812[/TD]
[TD="align: right"]9260197[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]14542104[/TD]
[TD="align: right"]20114122[/TD]
[TD="align: right"]14630105[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]178358803[/TD]
[TD="align: right"]188259221[/TD]
[TD="align: right"]189161699[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]185498[/TD]
[TD="align: right"]256745[/TD]
[TD="align: right"]196856[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]281996[/TD]
[TD="align: right"][/TD]
[TD="align: right"]366896[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]456211[/TD]
[TD="align: right"]209729[/TD]
[TD="align: right"]2096169[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]124819302[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]12693321[/TD]
[TD="align: right"]12803776[/TD]
[TD="align: right"]6659092[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"]186847[/TD]
[TD="align: right"]108981[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]39389980[/TD]
[TD="align: right"]53923881[/TD]
[TD="align: right"]35203858[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]19395726[/TD]
[TD="align: right"]11055736[/TD]
[TD="align: right"]30211645[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]12568227[/TD]
[TD="align: right"]16060882[/TD]
[TD="align: right"]13345519[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]2362588[/TD]
[TD="align: right"]1978827[/TD]
[TD="align: right"]1377612[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]7429453[/TD]
[TD="align: right"]7689519[/TD]
[TD="align: right"]7859924[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]511484[/TD]
[TD="align: right"]172378[/TD]
[TD="align: right"]879285[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]198494680[/TD]
[TD="align: right"]215474018[/TD]
[TD="align: right"]217062680[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]488886[/TD]
[TD="align: right"]155564[/TD]
[TD="align: right"]191226[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]1636628[/TD]
[TD="align: right"]1787199[/TD]
[TD="align: right"]1790381[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]11351019[/TD]
[TD="align: right"]5966999[/TD]
[TD="align: right"]18733803[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]589676464[/TD]
[TD="align: right"]519358121[/TD]
[TD="align: right"]583149402[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]61658145[/TD]
[TD="align: right"]82092558[/TD]
[TD="align: right"]103154593[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]135309918[/TD]
[TD="align: right"]153243824[/TD]
[TD="align: right"]132203164[/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]1149078[/TD]
[TD="align: right"]453385[/TD]
[TD="align: right"]1672384[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]483099[/TD]
[TD="align: right"]95584[/TD]
[TD="align: right"]380146[/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]213464591[/TD]
[TD="align: right"]232201644[/TD]
[TD="align: right"]156812752[/TD]

[TD="align: center"]43[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]21380137[/TD]
[TD="align: right"]28205400[/TD]
[TD="align: right"]50685538[/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]2595170[/TD]
[TD="align: right"]2356221[/TD]
[TD="align: right"]3036323[/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]22642148[/TD]
[TD="align: right"]20225805[/TD]
[TD="align: right"]42633226[/TD]

[TD="align: center"]46[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]25717903[/TD]
[TD="align: right"]17637885[/TD]
[TD="align: right"]68955608[/TD]

[TD="align: center"]47[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]35029124[/TD]
[TD="align: right"]26788062[/TD]
[TD="align: right"]65401995[/TD]

[TD="align: center"]48[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5308973[/TD]

[TD="align: center"]49[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]8802185[/TD]
[TD="align: right"]10375892[/TD]
[TD="align: right"]11641826[/TD]

[TD="align: center"]50[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]6341506[/TD]
[TD="align: right"]3670994[/TD]
[TD="align: right"]11754336[/TD]

[TD="align: center"]51[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]127059[/TD]
[TD="align: right"]296236[/TD]
[TD="align: right"]1142407[/TD]

[TD="align: center"]52[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]2430990[/TD]
[TD="align: right"]2197993[/TD]
[TD="align: right"]4276465[/TD]

[TD="align: center"]53[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]7812739[/TD]
[TD="align: right"]8285574[/TD]
[TD="align: right"]10141213[/TD]

[TD="align: center"]54[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]55[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]97095[/TD]
[TD="align: right"][/TD]
[TD="align: right"]372645[/TD]

[TD="align: center"]56[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]14840263[/TD]
[TD="align: right"]4696769[/TD]
[TD="align: right"]26558749[/TD]

[TD="align: center"]57[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]5176166[/TD]
[TD="align: right"]5889741[/TD]
[TD="align: right"]12618311[/TD]

[TD="align: center"]58[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]158891347[/TD]
[TD="align: right"]230074510[/TD]
[TD="align: right"]182362907[/TD]

[TD="align: center"]59[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]28025724[/TD]
[TD="align: right"]27776530[/TD]
[TD="align: right"]117636571[/TD]

[TD="align: center"]60[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]144408114[/TD]
[TD="align: right"]383111620[/TD]
[TD="align: right"]212149788[/TD]

[TD="align: center"]61[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]7149809[/TD]
[TD="align: right"]9885661[/TD]
[TD="align: right"]5176742[/TD]

[TD="align: center"]62[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]1031245[/TD]
[TD="align: right"]275596[/TD]
[TD="align: right"]478733[/TD]

[TD="align: center"]63[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]9508156[/TD]
[TD="align: right"]22955110[/TD]
[TD="align: right"]14754304[/TD]

[TD="align: center"]64[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]25611499[/TD]
[TD="align: right"]12290893[/TD]
[TD="align: right"]45035197[/TD]

[TD="align: center"]65[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]9532541[/TD]
[TD="align: right"]7381827[/TD]
[TD="align: right"]9162606[/TD]

[TD="align: center"]66[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]3854284[/TD]
[TD="align: right"]3115115[/TD]
[TD="align: right"]4606021[/TD]

[TD="align: center"]67[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]163786299[/TD]
[TD="align: right"]173263366[/TD]
[TD="align: right"]191733905[/TD]

[TD="align: center"]68[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]24907271[/TD]
[TD="align: right"]26776087[/TD]
[TD="align: right"]30971809[/TD]

[TD="align: center"]69[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]2426568[/TD]
[TD="align: right"]2891300[/TD]
[TD="align: right"]3145373[/TD]

[TD="align: center"]70[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]3569924[/TD]
[TD="align: right"]6338285[/TD]
[TD="align: right"]5170261[/TD]

[TD="align: center"]71[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]3505023[/TD]
[TD="align: right"]4383205[/TD]
[TD="align: right"]4761501[/TD]

[TD="align: center"]72[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]2584635[/TD]
[TD="align: right"]3652839[/TD]
[TD="align: right"]6291664[/TD]

[TD="align: center"]73[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]979883[/TD]
[TD="align: right"]807410[/TD]
[TD="align: right"]1702351[/TD]

[TD="align: center"]74[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]335284[/TD]
[TD="align: right"]164844[/TD]
[TD="align: right"]373766[/TD]

[TD="align: center"]75[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]1959171609[/TD]
[TD="align: right"]2066600923[/TD]
[TD="align: right"]1758841931[/TD]

[TD="align: center"]76[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]28344644[/TD]
[TD="align: right"]20728256[/TD]
[TD="align: right"]43787099[/TD]

[TD="align: center"]77[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]244084878[/TD]
[TD="align: right"]252196455[/TD]
[TD="align: right"]287838008[/TD]

[TD="align: center"]78[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]21866630[/TD]
[TD="align: right"]36394594[/TD]
[TD="align: right"]38370513[/TD]

[TD="align: center"]79[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]54717563[/TD]
[TD="align: right"]126288053[/TD]
[TD="align: right"]114539600[/TD]

</tbody>

Sheet2​

 

[Peter_SSs]

Welcome to the MrExcel board!

Best to try and keep your sample data and expected results a bit smaller than that if you can.

Do the PeakNumbers already appear on Sheet2 in column A or does the macro need to determine them too?

 

[Karp40]

Hi,
Sorry for the length of the sample data. I wasn't sure how much I needed to post for everyone to see the patterns in the data.

The peak numbers would already be in column A of Sheet2 (it only needs to be on the sheet once). The macro needs to look at each sample in Sheet1, take the sample name (ie. 10a_aa-post) and paste into B1 on sheet2. Take peak number 1 from that sample and paste it into B2, peak number2 into B3, etc. Then move on the next sample and put in into C1, etc., until it runs out of samples. The biggest thing is to put each peak's area data into its corresponding row on Sheet2 and to leave a blank cell for the peaknumbers with no data.

Thanks for any help that you can give,

Charles

 

[Peter_SSs]

Assuming the layout of the two sheets is as below (ie column heading positions, starting rows of actual data, nothing in Sheet2 except in col A etc) ..

	A	B	C	D	E	F
10a_aa_post	10b_aa_post	10c_aa_post
Peak Number	Area	Peak Number	Area	Peak Number	Area

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1711998[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1853650[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1847710[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8029340[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5611290[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6016448[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4915881[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8775656[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3215693[/TD]

</tbody>

Sheet1​

	A	B	C	D
PeakNumber

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

.. then give this a try in a copy of your workbook.

Sub PeakNumbers()  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, uba2 As Long
  
  a = Sheets("Sheet1").Range("A1").CurrentRegion.Value
  uba2 = UBound(a, 2)
  Set d = CreateObject("Scripting.Dictionary")
  With Sheets("Sheet2")
    With .Range("A1", .Range("A" & Rows.Count).End(xlUp))
      For i = 1 To .Rows.Count
        d(.Cells(i, 1).Value) = i
      Next i
      ReDim b(1 To .Rows.Count, 1 To uba2 / 2)
      For j = 1 To uba2 Step 2
        b(1, (j + 1) / 2) = a(1, j)
      Next j
      For i = 3 To UBound(a)
        For j = 1 To uba2 Step 2
          If d.exists(a(i, j)) Then b(d(a(i, j)), (j + 1) / 2) = a(i, j + 1)
        Next j
      Next i
      With .Offset(, 1).Resize(, UBound(b, 2))
        .NumberFormat = "General"
        .Value = b
      End With
      .CurrentRegion.Columns.AutoFit
    End With
  End With
End Sub

 

[Karp40]

Hi,

Thanks for the sorting macro. It works very well.

Thanks again,

Charles

 

[Peter_SSs]

Hi,

Thanks for the sorting macro. It works very well.

Thanks again,

Charles

Glad it worked out for you. Thanks for letting us know.