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
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​

 

[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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

	A	B	C	D
1	PeakNumber
2	1
3	2
4	3
5	4

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</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.