0

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
0
  • 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
1

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.

enter image description here Hope it helps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.