Sorting

[krsarkar]

I have an excel sheet or a table which looks like following(3 columns or field),

Part-----Assembly----Avg
5------- 12.5------- 15
4------- 20--------- 5
2------- 35--------- 30
1------- 14--------- 16
3------- 18--------- 21

(It is an example, May be I will have thousands of records)

This data is collected from simulation run (do not bother).

Now what I want is the following,

First,  Find the smallest number from 2 columns (Assembly and Avg),
For example, here the smallest number is 5, which is in Avg column.

Second  If the smallest number found in Column (Avg) than place the entire
record at the beginning of the table, or if the smallest number found in column(Assembly) than place the entire record at the end of the table.

Note: You can use another table or excel sheet to insert the sorted record or the same sheet or table.

Therefore, from the above table we get the following,

Part-----Assembly----Avg
4------- 20----------- 5
2------- 35------------ 30
3------- 18------------ 21
1------- 14------------ 16
5------- 12.5-----------15


Therefore, the prime thing is to find the smallest number first and place the record on table according to the columns (Assembly, Avg) and to keep in mind that after each search the found number must be eliminated to find the next smallest. The whole process should run at once.

I have no idea about it how to place record on a table according to above requirement.

Please provide me some solution for that.
You can write code for Excel macro (VBA) or visual basic coding or SQL query. Any of the form would be accepted. I just need the logic to do this.

[Uncle Dickie]

Not sure if you will have found a solution to this but the following concept should work:

• Find the minimum of the columns.
• If it is from the Assembly column, multiply it by -1.
• Create a new column which has either a 1 or 2 in it, 1 if it came from Average and 2 if it came from Assembly
• Sort the data by the new column and then by the modified minimum

An example of this which works in SQL

SELECT   partID
         ,myAssy
         ,myAvg
         ,CASE
             WHEN  myAssy < myAvg
             THEN  myAssy * -1
             ELSE  myAvg
         END AS myMin
         ,CASE
             WHEN  myAssy < myAvg
             THEN  2
             ELSE  1
         END AS myMinType
FROM     dbo.R_Table
ORDER BY myMinType
         ,myMin

You can probably remove the CASE statements from the SELECT and just recreate them in the ORDER BY but I have not tried this.