Sorting cells that are within certain range?

[Marko_]

Please take a look at the figure below. I have to sort new data (in this case retention time together with the corresponding area values) according to the retention time of the reference data (yellow). The retention time of the new data is not necessarily equal to the ret. time of reference data, but it is in the range of ref. ret. data+-25.


Figure
On the left you can see the raw data that I get, and on the right how it should look like after sorting the data. The problem is, I have thousands of measurements, and I can't sort the data manually.

[/URL][/IMG]

 

[oldbrewer]

I have looked at your image, but cannot see what you are sorting. Tell us in words how you got to the right hand table, please.

 

[Marko_]

I have looked at your image, but cannot see what you are sorting. Tell us in words how you got to the right hand table, please.

I'll try to explain it. A retention time value represents one single chemical substance, so you can see the retention time column as a list of different substances. The Area column represents the quantity of these substances in a sample.
Columns Ret Time 1 and Area1 represent supstances and their quantities of the first sample (reference sample), Ret Time2 and Area 2 of the second one, and Ret Time3 and Area 3 of the third one. My goal is to sort a single chemical substance (for example for alpha Pinene is the retention time in the range between 9625 to 9675) in one single row for all three samples. All ret. time values of the second sample (column AF) and all subsequent samples should be compared with the ret. values of the reference sample (column AD). If ret. time value of the sample 2 equals ref.value+-25, it represents the same substance and should be placed in the same row as the ret. time value of the reference sample, if not - the cell should be left blank.

On the example of alpha Pinene: The ret. time value of the first sample (reference sample) 9650 (column AK), of the 2. sample 9654 (AM) and of the 3. sample 9651 (AO) are placed in the same row together with the quantity values (Area value) of the alpha Pinene in each corresponding sample. The retention time values of the 2. (9654) and 3. sample (9651) are in the range 9650(ref.value) +-25, so the condition is fulfilled.

All other substances should be also sorted in this manner. Check whether ret. time value = ref. value+-25. If true > place in the same row, in not > leave blank.

 

[oldbrewer]

second row - why is 9.112 not included on the right hand table - and do you mean +/- 0.025

 

[Marko_]

second row - why is 9.112 not included on the right hand table - and do you mean +/- 0.025

Thanks for the reply!

1) I deleted it because I don't have a corresponding retention time value in the reference sample. I didn't want to confuse you. In my future measurements I'll make sure that I have all needed retention time values.
It would be even better if you have an idea how to sort this data without reference sample, but I think that would be much more difficult.

2) Yes, I mean +/- 0.025.

 

[oldbrewer]

it would help me and probably others with a pretend sheet with whole numbers - just a few columns - and say in terms of that sheet what you want to do. Once we nail what is wanted the solution will come quickly.

 

[Marko_]

it would help me and probably others with a pretend sheet with whole numbers - just a few columns - and say in terms of that sheet what you want to do. Once we nail what is wanted the solution will come quickly.

it would help me and probably others with a pretend sheet with whole numbers - just a few columns - and say in terms of that sheet what you want to do. Once we nail what is wanted the solution will come quickly.

I uploaded an example: http://s000.tinyupload.com/?file_id=08018583441363979303

Please first take a look at the sheet 2. These tables are my raw data. Each table represents a certain sample. I only need green fields for my further calculations.
In the sheet 1 you can see how are retention times and areas from the raw data sorted (green fields - sheet 1). You can see that each row represents one single substance (for example a-Pinene - red color). That is my goal. I need one single substance in a same row for all 4 samples. Each substance have retention time in a certain range (say mean value +/- 25).
In this case I don't have a reference sample, because I did the sorting manually. However, I can mark one of the samples as a reference one (as in previous posts) to make automatic sorting easier.

Toluene D8 (row 5) is marked yellow because it is my standard-substance that I use for further calculations. Just ignore it. I take it from the raw data just like all the others.

 

[oldbrewer]

really sorry - I never download

I am not understanding what you want at all - that is why I asked for simple made up data and what you want to do with it, then it can be adapted to your real data.

 

[Marko_]

Ok, I understand. I have tried to simplify it in the table below. Column 2 is the raw data. Column 4 (actually 5) is sorted data.
Condition: If reference-25 < Sample raw < reference+25, then sort that sample value (column sample-sorted) beside ref. value; else - leave blank.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]reference[/TD]
[TD]sample- raw[/TD]
[TD][/TD]
[TD]reference[/TD]
[TD]sample- sorted[/TD]
[/TR]
[TR]
[TD]1030[/TD]
[TD]1020[/TD]
[TD][/TD]
[TD]1030[/TD]
[TD]1020[/TD]
[/TR]
[TR]
[TD]1250[/TD]
[TD]1390[/TD]
[TD][/TD]
[TD]1250[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1400[/TD]
[TD]1989[/TD]
[TD][/TD]
[TD]1400[/TD]
[TD]1390[/TD]
[/TR]
[TR]
[TD]1550[/TD]
[TD]2300[/TD]
[TD][/TD]
[TD]1550[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]3660[/TD]
[TD][/TD]
[TD]2000[/TD]
[TD]1989[/TD]
[/TR]
[TR]
[TD]2320[/TD]
[TD][/TD]
[TD][/TD]
[TD]2320[/TD]
[TD]2300[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD][/TD]
[TD][/TD]
[TD]3000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3500[/TD]
[TD][/TD]
[TD][/TD]
[TD]3500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3650[/TD]
[TD][/TD]
[TD][/TD]
[TD]3650[/TD]
[TD]3660[/TD]
[/TR]
</tbody>[/TABLE]