12 Replies Latest reply: Jun 28, 2012 5:38 AM by jagan mohan rao appala

# How to make data in Ascending  order?

Hello Everyone,

 Col1 Col2 Col3 Col4 col5 col7 col7 100? 120? 160? 100 - 120 160 180? 220? 230? 230 180 190 220 189? 200? 240? 189 200 240 250

I have one strght table like above. I have data only Col4,Col5,col6 and col7. Here My question is I have to fill this data based on selection of these four fileds. I need to fill Col1,col2, col3 in Ascending   order?

How it is possible?

Best,

Siva.

• ###### Re: How to make data in Ascending  order?

It is not clear at all what you are trying to achieve. Please explain.

• ###### Re: How to make data in Ascending  order?

Hi Loannis,

 Col1 Col2 Col3 Col4 col5 col7 col7 100 - 120 160 230 180 190 220 189 200 240 250

I have one straight table like above. I don't have data for Col1,Col2 and Col3. I should get data for these fileds from Col4,Col5, Col6 and Col7.

In first row Col 4,Col5,Col6 and Col7 we have data 100,-,120 and 160 respectively. In this 100,120 and 160 are three minimum values. So, I have to put these three minimum values in Col1,Col2  and Col3.

How Can I put expression for this?

I want to o/p like Below

 Col1 Col2 Col3 Col4 col5 col7 col7 100? 120? 160? 100 - 120 160 180? 220? 230? 230 180 190 220 189? 200? 240? 189 200 240 250

Best,

Siva

• ###### Re: How to make data in Ascending  order?

Col1 is easily achieved by rangemin but Col2 and 3 I haven't found anything yet

Still trying

• ###### Re: How to make data in Ascending  order?

Hi Siva,

It is bit difficult to find second and Third min values in expression for given values.  We need to find a logic using IF statements.

Hope attached file helps you.  I calculated column A only.

Regards,

Jagan.

• ###### Re: How to make data in Ascending  order?

Hi Jagan,

Thank you very much. Its working for Col1 and I need same thing for col2 and col3?

Can anyone help on this? Its very urgent.

Best,

Siva

• ###### Re: How to make data in Ascending  order?

Hi Siva,

I some how managed to get the result, I think it is not optimized way but there is no option.  Let us wait if any of our friends come out with any other logic.

Regards,

Jagan.

• ###### Re: How to make data in Ascending  order?

Hi Jagan,

 Col1 col2 col3 Col4 Col5 Col6 Col7 Col8 Col9 156 164 - 156 185 190 189 189 164 156 205 217 - 205 205 205 271.5 271.5 217 224.5 408.5 458.5 - 408.5 478 - 478 478 408.5 458.5

As per your report I am getting Col1 and Col2 values what I expected. But for col3 I am getting as same of Col1 values

Best,

Siva

• ###### Re: How to make data in Ascending  order?

Hi Jagan,

I am putting expression like below

RangeMin(

If(Match(\$(vMarketFare1), MKT1, MKT2), Null(), \$(vMarketFare1)),

If(Match(\$(vMarketFare2), MKT1, MKT2), Null(), \$(vMarketFare2)),

If(Match(\$(vMarketFare3), MKT1, MKT2), Null(), \$(vMarketFare3)),

If(Match(\$(vMarketFare4), MKT1, MKT2), Null(), \$(vMarketFare4)),

If(Match(\$(vMarketFare5), MKT1, MKT2), Null(), \$(vMarketFare5)),

If(Match(\$(vMarketFare6), MKT1, MKT2), Null(), \$(vMarketFare6))

)

Will it work for variables?

Best,

Siva

• ###### Re: How to make data in Ascending  order?

Hello There,

How to found third Rangemin value?

Best,

Siva

• ###### Re: How to make data in Ascending  order?

Hi Siva,

Can you attach what you declared for variables?

vMarketFareX = ?

Regards,

Jagan.

• ###### Re: How to make data in Ascending  order?

Hi Jagan,

1)RangeMin(\$(vMarketFare1),\$(vMarketFare2),\$(vMarketFare3),\$(vMarketFare4),\$(vMarketFare5),\$(vMarketFare6))--1st Expression

2)RangeMin(If(MKT1 = \$(vMarketFare2), Null(), \$(vMarketFare2)), If(MKT1 = \$(vMarketFare3), Null(), \$(vMarketFare3)), If(MKT1 = \$(vMarketFare4), Null(), \$(vMarketFare4)), If(MKT1 = \$(vMarketFare5), Null(), \$(vMarketFare5)),If(MKT1 = \$(vMarketFare6), Null(), \$(vMarketFare6)))--2nd expression

3)

RangeMin(

If(Match(\$(vMarket1), MKT1, MKT2), Null(), \$(vMarket1)),

If(Match(\$(vMarket2), MKT1, MKT2), Null(), \$(vMarket2)),

If(Match(\$(vMarket3), MKT1, MKT2), Null(), \$(vMarket3)),

If(Match(\$(vMarket4), MKT1, MKT2), Null(), \$(vMarket4)),

If(Match(\$(vMarket5), MKT1, MKT2), Null(), \$(vMarket5)),

If(Match(\$(vMarket6), MKT1, MKT2), Null(), \$(vMarket6))

)-- 3rd expression

Best,

Siva

• ###### Re: How to make data in Ascending  order?

Hi Siva,

Try like this, hope it works

RangeMin(

If((\$(vMarket1) = MKT1 OR \$(vMarket1) = MKT2), Null(), \$(vMarket1)),

If((\$(vMarket2) = MKT1 OR \$(vMarket2) = MKT2), Null(), \$(vMarket2)),

If((\$(vMarket3) = MKT1 OR \$(vMarket3) = MKT2), Null(), \$(vMarket3)),

If((\$(vMarket4) = MKT1 OR \$(vMarket4) = MKT2), Null(), \$(vMarket4)),

If((\$(vMarket5) = MKT1 OR \$(vMarket5) = MKT2), Null(), \$(vMarket5)),

If((\$(vMarket6) = MKT1 OR \$(vMarket6) = MKT2), Null(), \$(vMarket6))

)

Regards,

Jagan.