# How to create calculated field column for sorting the numbers

I have three columns

``````value1 value2 value3
(100   100     80)
(100   100     100)
(70    40     50)
``````

I want to sort this by concatenating all these and padding left 0 and then sort it. Can any one help me out, how can I write the formula.

• Rishabh, can you add the Output you want from this Input of 3 rows in you question so we understand what is it you want to sort first or concatenate. – Danny '365CSI' Engelman Jun 13 '15 at 15:56
• Also, please mark the answer as accepted if any of the below answered solved your problem – Tanmay Jun 14 '15 at 8:14
• Hey, thanks for helping me out, I got it. your solutions helped me a lot .. thanks again Denny and Tanmay. – Rishabh Shukla Jun 15 '15 at 13:45

• You can use the MAX function to get the highest from 3 values
• Append leading 000 to the result (padding left you mean, I presume)
• Then use the RIGHT function to get the last 3 digits

Available Functions and Syntax List is at ViewMaster365.com/functions

Off the top of my head (so likely wrong, as it is 08:00 here in Amsterdam and I haven't finished my first coffee yet)

``````= RIGHT( "000" & MAX( value1,value2,value3 ) , 3 )
``````
• Thanks Danny for the help. I am getting the max value from the row.I am looking for something like row 1 =100 70 80 where 100 is from value 1 column 70 is from value 2 and 80 from value 3 and same with other two rows 100 100 80 and 100 100 100..I am sorry if I am not clear..I will look at the link which you have shared..Thanks again :) and I hope you would have had your coffee.. – Rishabh Shukla Jun 12 '15 at 7:03

Try this out

Step 1 : Sort the numbers (Formula below). To keep the formula concise I had to user "123","321"etc. They represent the order of values. So, "123" means order in ascending i.e. value1 value2 and then value3

``````    IF( Value1<Value2
, IF( Value1<Value3
, IF( Value2<Value3
,"123"
,"132"
)
,"312"
)
, IF( Value2<Value3
, IF( Value1<Value3
,"213"
,"231"
)
,"321"
)
)
``````

Step 2 : Since you now have the base ready, you can replace "123" etc with whatever you want (padding with zeros) in your case

Here are some tests that i ran. Check out the screen shot below.

Hope it helps