Sorting of Parameters according to their values

[debapratim1]

Hi All,

I am facing a problem while sorting a group of Objects according to their values. When multiple values entered are same, then Only the First Object is being returned thrice. While I want all the objects to be returned with same values. Please help.

Demo:

[TABLE="width: 213"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Objects[/TD]
[TD]Avg Value[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]OBJ 1[/TD]
[TD]121[/TD]
[/TR]
[TR]
[TD]OBJ 1[/TD]
[TD]121[/TD]
[/TR]
[TR]
[TD]OBJ 8[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]OBJ 11[/TD]
[TD]126[/TD]
[/TR]
[TR]
[TD]OBJ 2[/TD]
[TD]133[/TD]
[/TR]
[TR]
[TD]OBJ 9[/TD]
[TD]135[/TD]
[/TR]
[TR]
[TD]OBJ 10[/TD]
[TD]136[/TD]
[/TR]
[TR]
[TD]OBJ 3[/TD]
[TD]154[/TD]
[/TR]
[TR]
[TD]OBJ 6[/TD]
[TD]172[/TD]
[/TR]
[TR]
[TD]OBJ 4[/TD]
[TD]177[/TD]
[/TR]
[TR]
[TD]OBJ 5[/TD]
[TD]181
[/TD]
[/TR]
</tbody>[/TABLE]

Here Object 1 and Object 7 are having the same value but Object 1 is returned twice.

I have used the formula: =INDEX($C$5:$C$99,MATCH($H5,$D$5:$D$15,0))

 

[NARAYANK991]

Hi ,

Try this :

=INDEX($C$5:$C$99 , SMALL(IF($D$5:$D$99 = $H5 , ROW($D$:$D$99) - MIN(ROW($D$:$D$99)) + 1) , COUNTIF($H$5:$H5 , $H5)))

This is an array formula , to be entered using CTRL SHIFT ENTER.

In case it does not work , please mention the worksheet references for your do $C$5:$C$99 , $D$5:$D$99 , $H5 contain ? Where will this formula be entered , and how will it be copied to other cells ?

 

[debapratim1]

Hi ,

Try this :

=INDEX($C$5:$C$99 , SMALL(IF($D$5:$D$99 = $H5 , ROW($D$:$D$99) - MIN(ROW($D$:$D$99)) + 1) , COUNTIF($H$5:$H5 , $H5)))

This is an array formula , to be entered using CTRL SHIFT ENTER.

In case it does not work , please mention the worksheet references for your do $C$5:$C$99 , $D$5:$D$99 , $H5 contain ? Where will this formula be entered , and how will it be copied to other cells ?

Hi,
Thanks for your help. Can you please help me with the logic so that I can implement it accordingly? Actually, the data is a little complex and many parameters are there. By applying many formulae I got this table.

Again thanks for immense help.

 

[NARAYANK991]

Hi ,

If you can describe how your data is laid out , I can explain.

First , you need to confirm whether the posted formula works.

 

[debapratim1]

Its nt wrking . Its returning the same object in all the rows of the column.

 

[debapratim1]

I want the objects to be arranged according to its respective values. Please let me know if u have any questions.

 

[debapratim1]

[TABLE="width: 492"]
<colgroup><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD]OBJ 1[/TD]
[TD="align: right"]121[/TD]
[TD][/TD]
[TD][/TD]
[TD]OBJ 1[/TD]
[TD]121[/TD]
[/TR]
[TR]
[TD]OBJ 2[/TD]
[TD="align: right"]133[/TD]
[TD][/TD]
[TD][/TD]
[TD]OBJ 1[/TD]
[TD]121[/TD]
[/TR]
[TR]
[TD]OBJ 3[/TD]
[TD="align: right"]154[/TD]
[TD][/TD]
[TD][/TD]
[TD]OBJ 8[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]OBJ 4[/TD]
[TD="align: right"]177[/TD]
[TD][/TD]
[TD][/TD]
[TD]OBJ 11[/TD]
[TD]126[/TD]
[/TR]
[TR]
[TD]OBJ 5[/TD]
[TD="align: right"]181[/TD]
[TD][/TD]
[TD][/TD]
[TD]OBJ 2[/TD]
[TD]133[/TD]
[/TR]
[TR]
[TD]OBJ 6[/TD]
[TD="align: right"]172[/TD]
[TD][/TD]
[TD][/TD]
[TD]OBJ 9[/TD]
[TD]135[/TD]
[/TR]
[TR]
[TD]OBJ 7[/TD]
[TD="align: right"]121[/TD]
[TD][/TD]
[TD][/TD]
[TD]OBJ 10[/TD]
[TD]136[/TD]
[/TR]
[TR]
[TD]OBJ 8[/TD]
[TD="align: right"]125[/TD]
[TD][/TD]
[TD][/TD]
[TD]OBJ 3[/TD]
[TD]154[/TD]
[/TR]
[TR]
[TD]OBJ 9[/TD]
[TD="align: right"]135[/TD]
[TD][/TD]
[TD][/TD]
[TD]OBJ 6[/TD]
[TD]172[/TD]
[/TR]
[TR]
[TD]OBJ 10[/TD]
[TD="align: right"]136[/TD]
[TD][/TD]
[TD][/TD]
[TD]OBJ 4[/TD]
[TD]177[/TD]
[/TR]
[TR]
[TD]OBJ 11[/TD]
[TD="align: right"]126[/TD]
[TD][/TD]
[TD][/TD]
[TD]OBJ 5[/TD]
[TD]181[/TD]
[/TR]
</tbody>[/TABLE]

 

[NARAYANK991]

Hi ,

Please either describe how your data is laid out , with specific worksheet cell / range / row / column references , or use the tools available on this forum to display your worksheet layout , or upload your workbook to some public file sharing service and share the access link here.