Hello,
I have the following segments of data, all of the same shape and dimensions, that I want to organize into a few bigger tables. There are 20 segments of data per time, which are organized in the form of 4 (vertical) X 5 (horizontal) segments. Each data point can be identified by three properties:
1) Sample name: up to 40 (only 4 shown in example)
2) Time: 1,2,3,...,15 (only 2 shown in example)
3) Replicate: 1,2,3,4 (only 3 shown in example)
4) Concentration: 1,2,3,...,12 (only 4 shown in example)
My goal is to bring all the segments of each time together, and make the sample list alphabetical as shown in the two empty lower tables of the attached example XL2BB below:
I was trying to think of an INDEX/MATCH formula, but wasn't sure how to make it search for all 4 properties at once in discontinuous ranges. I tried to break the ranges, but INDEX didn't like it.
I would appreciate any input. Thanks!
I have the following segments of data, all of the same shape and dimensions, that I want to organize into a few bigger tables. There are 20 segments of data per time, which are organized in the form of 4 (vertical) X 5 (horizontal) segments. Each data point can be identified by three properties:
1) Sample name: up to 40 (only 4 shown in example)
2) Time: 1,2,3,...,15 (only 2 shown in example)
3) Replicate: 1,2,3,4 (only 3 shown in example)
4) Concentration: 1,2,3,...,12 (only 4 shown in example)
My goal is to bring all the segments of each time together, and make the sample list alphabetical as shown in the two empty lower tables of the attached example XL2BB below:
Organize.xlsx | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
1 | |||||||||||||||||||||
2 | Time | Concentration | Time | Concentration | |||||||||||||||||
3 | 1 | Replicate | 1 | 2 | 3 | 4 | 1 | Replicate | 1 | 2 | 3 | 4 | |||||||||
4 | Sample1 | 1 | 34 | 28 | 27 | 67 | Sample2 | 3 | 82 | 35 | 63 | 36 | |||||||||
5 | Sample2 | 2 | 63 | 82 | 41 | 66 | Sample4 | 2 | 60 | 67 | 70 | 10 | |||||||||
6 | Sample1 | 2 | 65 | 83 | 56 | 44 | Sample3 | 2 | 69 | 63 | 24 | 78 | |||||||||
7 | Sample3 | 1 | 23 | 74 | 25 | 86 | Sample3 | 3 | 34 | 89 | 14 | 60 | |||||||||
8 | Sample2 | 1 | 71 | 47 | 38 | 49 | Sample1 | 3 | 39 | 82 | 75 | 85 | |||||||||
9 | Sample4 | 3 | 50 | 19 | 59 | 68 | Sample4 | 1 | 11 | 44 | 22 | 42 | |||||||||
10 | |||||||||||||||||||||
11 | Time | Concentration | Time | Concentration | |||||||||||||||||
12 | 2 | Replicate | 1 | 2 | 3 | 4 | 2 | Replicate | 1 | 2 | 3 | 4 | |||||||||
13 | Sample3 | 2 | 46 | 24 | 74 | 48 | Sample1 | 2 | 74 | 35 | 69 | 25 | |||||||||
14 | Sample4 | 1 | 47 | 21 | 38 | 66 | Sample3 | 3 | 71 | 47 | 33 | 36 | |||||||||
15 | Sample1 | 1 | 90 | 77 | 46 | 60 | Sample2 | 2 | 90 | 82 | 73 | 84 | |||||||||
16 | Sample2 | 1 | 77 | 66 | 17 | 14 | Sample4 | 2 | 14 | 20 | 53 | 55 | |||||||||
17 | Sample1 | 3 | 22 | 39 | 84 | 45 | Sample4 | 3 | 18 | 49 | 38 | 31 | |||||||||
18 | Sample3 | 1 | 11 | 22 | 58 | 43 | Sample2 | 1 | 20 | 81 | 28 | 51 | |||||||||
19 | |||||||||||||||||||||
20 | Time | ||||||||||||||||||||
21 | 1 | Concentration | 1 | Concentration | 2 | Concentration | 3 | Concentration | 4 | ||||||||||||
22 | Replicate: | 1 | 2 | 3 | 4 | 1 | 2 | 3 | 4 | 1 | 2 | 3 | 4 | 1 | 2 | 3 | 4 | ||||
23 | Sample1 | ||||||||||||||||||||
24 | Sample2 | ||||||||||||||||||||
25 | Sample3 | ||||||||||||||||||||
26 | Sample4 | ||||||||||||||||||||
27 | |||||||||||||||||||||
28 | Time | ||||||||||||||||||||
29 | 2 | Concentration | 1 | Concentration | 2 | Concentration | 3 | Concentration | 4 | ||||||||||||
30 | Replicate: | 1 | 2 | 3 | 4 | 1 | 2 | 3 | 4 | 1 | 2 | 3 | 4 | 1 | 2 | 3 | 4 | ||||
31 | Sample1 | ||||||||||||||||||||
32 | Sample2 | ||||||||||||||||||||
33 | Sample3 | ||||||||||||||||||||
34 | Sample4 | ||||||||||||||||||||
35 | |||||||||||||||||||||
Sheet1 |
I was trying to think of an INDEX/MATCH formula, but wasn't sure how to make it search for all 4 properties at once in discontinuous ranges. I tried to break the ranges, but INDEX didn't like it.
I would appreciate any input. Thanks!