How to sort in xcelsius

[Former Member]

Hi All,

Please give me an idea how to do the sorting in Xcelsius.

Thanks

Sathiya

[Former Member]

That works okay for sorting a single array of numbers, but the issue is rather more involved if you're sorting a labelled list - ie, a column of labels with an associated value corresponding to each one. The problem is that the standard functions don't deal with ties well. In the example below, we have a list of regions with a performance value where two of the values are identical, and we want to have the list of regions reordered to show them highest to lowest.

Region	Value	Sorted	Rank	InOrder
North	10	30	2	South
South	30	20	3	East
East	20	20	4	West
West	20	10	1	North

You can use the RANK function to give the order into the list of each value, but it ranks ties the same, so it can't be used to then index back to the list of labels to obtain the reordered list (in the example above, East would appear twice and West not at all). With some fancier formulas, we can create a ranking list that ensures all of the labels will appear. I've got the dollar signs in all the right places to allow the formulas to be copied down from the first row. Apologies if it's a little hard to read. One other note is that in the Value column, using the LARGE formula, I'm using the COUNTA function to eliminate the need to have a separate column listing the ordinals.

Region	Value	Sorted	Ranked	InOrder
North	10	=LARGE(B$2:B$5,COUNTA(A$2:A2))	=IF(C2<>C1,MATCH(C2,B$2:B$5,0),MATCH(C2,OFFSET(B$2:B$5,D1,0,COUNT(B$2:B$5)-D1,1),0)+D1)	=INDEX($A$2:$B$5,$D2,1)
South	30	=LARGE(B$2:B$5,COUNTA(A$2:A3))	=IF(C3<>C2,MATCH(C3,B$2:B$5,0),MATCH(C3,OFFSET(B$2:B$5,D2,0,COUNT(B$2:B$5)-D2,1),0)+D2)	=INDEX($A$2:$B$5,$D3,1)
East	20	=LARGE(B$2:B$5,COUNTA(A$2:A4))	=IF(C4<>C3,MATCH(C4,B$2:B$5,0),MATCH(C4,OFFSET(B$2:B$5,D3,0,COUNT(B$2:B$5)-D3,1),0)+D3)	=INDEX($A$2:$B$5,$D4,1)
West	20	=LARGE(B$2:B$5,COUNTA(A$2:A5))	=IF(C5<>C4,MATCH(C5,B$2:B$5,0),MATCH(C5,OFFSET(B$2:B$5,D4,0,COUNT(B$2:B$5)-D4,1),0)+D4)	=INDEX($A$2:$B$5,$D5,1)

[Former Member]

Thanks Peter,

I think the solution which you gave me that may work for me. Can you elaborate more on your answer. I am a beginner in Xcelcius it is little difficult for me to compare with my case,

My requirement is

Product Line Plan Actuals B/W Percent A/F A/P

abc 176343.7334 114991.0294 -61352.70397 65.21 65.20845805 65.21

VI 106838.0502 62189.12586 -44648.92434 58.21 58.20878025 58.21

LFP 56714.29595 39714.28202 -17000.01393 70.03 70.02516976 70.03

SD 43584.48418 23054.64322 -20529.84096 52.90 52.89644619 52.90

SIDM 14355.56844 8135.42865 -6220.13979 56.67 56.67089174 56.67

Scanner 9506.45942 6465.83516 -3040.62426 68.02 68.01517657 68.02

FAR 2399.23508 2256.97058 -142.2645 94.07 94.07042264 94.07

Other 666.98926 546.49427 -120.49499 81.93 81.93449322 81.93

everything has to be sorted according to plan value......

Please guide me with the excel formula ....

Thanks for your help...

Regards

Sathiya

[Former Member]

Attached the excel sheet with more clear

Product Line Plan Actuals B/W Percent A/F A/P

Hope this will be useful.

Thanks

Sathiya

[Former Member]

After looking at my previous posting, I see that the text editor removed a rather crucial part of the formulas, interpreting it as markup characters. The 'ranked' column formulas begin with an If statement comparing a cell to the cell above it. The 'not equal' symbols were removed - so for example the first formula should read IF(C2 <<> C1, MATCH.... - except that there should only be one << symbol; the only way I can get them to display is to put in two. I've inserted your example below where I've put the sorting formulas to the right. Since your example already had it sorted by Plan, I've reordered the original to sort alphabetically by product line so that we can better illustrate sorting by the Plan column.

To make the example work in Excel, you'll need to copy and paste it into a worksheet beginning in cell A1, do a Text-To-Columns split (space delimited), and then edit the 'ranked' formulas to change that double << to a single. The important column is the 'Ranked' one - all the columns to the right of it are simple indexes into the original list using the ranked value to reorder the list. Hopefully this all makes sense.

ProductLine	Plan	Actuals	B/W	Percent	A/F	A/P	Sorted	Ranked	ProductLineInOrder	PlanInOrder	ActualsInOrder	B/WInorder	PercentInOrder	A/FInOrder	A/Pinorder
abc	176343.7334	114991.0294	-61352.70397	65.21	65.20845805	65.21	=LARGE(B$2:B$9,COUNTA(A$2:A2))	=IF(H2<<>H1,MATCH(H2,B$2:B$9,0),MATCH(H2,OFFSET(B$2:B$9,I1,0,COUNT(G$2:G$5)-I1,1),0)+I1)	=INDEX($A$2:$G$9,$I2,1)	=INDEX($A$2:$G$9,$I2,2)	=INDEX($A$2:$G$9,$I2,3)	=INDEX($A$2:$G$9,$I2,4)	=INDEX($A$2:$G$9,$I2,5)	=INDEX($A$2:$G$9,$I2,6)	=INDEX($A$2:$G$9,$I2,7)
FAR	2399.23508	2256.97058	-142.2645	94.07	94.07042264	94.07	=LARGE(B$2:B$9,COUNTA(A$2:A3))	=IF(H3<<>H2,MATCH(H3,B$2:B$9,0),MATCH(H3,OFFSET(B$2:B$9,I2,0,COUNT(G$2:G$5)-I2,1),0)+I2)	=INDEX($A$2:$G$9,$I3,1)	=INDEX($A$2:$G$9,$I3,2)	=INDEX($A$2:$G$9,$I3,3)	=INDEX($A$2:$G$9,$I3,4)	=INDEX($A$2:$G$9,$I3,5)	=INDEX($A$2:$G$9,$I3,6)	=INDEX($A$2:$G$9,$I3,7)
LFP	56714.29595	39714.28202	-17000.01393	70.03	70.02516976	70.03	=LARGE(B$2:B$9,COUNTA(A$2:A4))	=IF(H4<<>H3,MATCH(H4,B$2:B$9,0),MATCH(H4,OFFSET(B$2:B$9,I3,0,COUNT(G$2:G$5)-I3,1),0)+I3)	=INDEX($A$2:$G$9,$I4,1)	=INDEX($A$2:$G$9,$I4,2)	=INDEX($A$2:$G$9,$I4,3)	=INDEX($A$2:$G$9,$I4,4)	=INDEX($A$2:$G$9,$I4,5)	=INDEX($A$2:$G$9,$I4,6)	=INDEX($A$2:$G$9,$I4,7)
Other	666.98926	546.49427	-120.49499	81.93	81.93449322	81.93	=LARGE(B$2:B$9,COUNTA(A$2:A5))	=IF(H5<<>H4,MATCH(H5,B$2:B$9,0),MATCH(H5,OFFSET(B$2:B$9,I4,0,COUNT(G$2:G$5)-I4,1),0)+I4)	=INDEX($A$2:$G$9,$I5,1)	=INDEX($A$2:$G$9,$I5,2)	=INDEX($A$2:$G$9,$I5,3)	=INDEX($A$2:$G$9,$I5,4)	=INDEX($A$2:$G$9,$I5,5)	=INDEX($A$2:$G$9,$I5,6)	=INDEX($A$2:$G$9,$I5,7)
Scanner	9506.45942	6465.83516	-3040.62426	68.02	68.01517657	68.02	=LARGE(B$2:B$9,COUNTA(A$2:A6))	=IF(H6<<>H5,MATCH(H6,B$2:B$9,0),MATCH(H6,OFFSET(B$2:B$9,I5,0,COUNT(G$2:G$5)-I5,1),0)+I5)	=INDEX($A$2:$G$9,$I6,1)	=INDEX($A$2:$G$9,$I6,2)	=INDEX($A$2:$G$9,$I6,3)	=INDEX($A$2:$G$9,$I6,4)	=INDEX($A$2:$G$9,$I6,5)	=INDEX($A$2:$G$9,$I6,6)	=INDEX($A$2:$G$9,$I6,7)
SD	43584.48418	23054.64322	-20529.84096	52.9	52.89644619	52.9	=LARGE(B$2:B$9,COUNTA(A$2:A7))	=IF(H7<<>H6,MATCH(H7,B$2:B$9,0),MATCH(H7,OFFSET(B$2:B$9,I6,0,COUNT(G$2:G$5)-I6,1),0)+I6)	=INDEX($A$2:$G$9,$I7,1)	=INDEX($A$2:$G$9,$I7,2)	=INDEX($A$2:$G$9,$I7,3)	=INDEX($A$2:$G$9,$I7,4)	=INDEX($A$2:$G$9,$I7,5)	=INDEX($A$2:$G$9,$I7,6)	=INDEX($A$2:$G$9,$I7,7)
SIDM	14355.56844	8135.42865	-6220.13979	56.67	56.67089174	56.67	=LARGE(B$2:B$9,COUNTA(A$2:A8))	=IF(H8<<>H7,MATCH(H8,B$2:B$9,0),MATCH(H8,OFFSET(B$2:B$9,I7,0,COUNT(G$2:G$5)-I7,1),0)+I7)	=INDEX($A$2:$G$9,$I8,1)	=INDEX($A$2:$G$9,$I8,2)	=INDEX($A$2:$G$9,$I8,3)	=INDEX($A$2:$G$9,$I8,4)	=INDEX($A$2:$G$9,$I8,5)	=INDEX($A$2:$G$9,$I8,6)	=INDEX($A$2:$G$9,$I8,7)
VI	106838.0502	62189.12586	-44648.92434	58.21	58.20878025	58.21	=LARGE(B$2:B$9,COUNTA(A$2:A9))	=IF(H9<<>H8,MATCH(H9,B$2:B$9,0),MATCH(H9,OFFSET(B$2:B$9,I8,0,COUNT(G$2:G$5)-I8,1),0)+I8)	=INDEX($A$2:$G$9,$I9,1)	=INDEX($A$2:$G$9,$I9,2)	=INDEX($A$2:$G$9,$I9,3)	=INDEX($A$2:$G$9,$I9,4)	=INDEX($A$2:$G$9,$I9,5)	=INDEX($A$2:$G$9,$I9,6)	=INDEX($A$2:$G$9,$I9,7)

[Former Member]

Hi Sathiya,

Can you be more specific with your question

You require sorting on chart components or on selectors or you want logic for excel.

If you want sorting for component level, we have sorting option at component properties.(Behaviour Tab)

Let me know if you need mroe information on excel logic.

Correct me if i am wrong on your explanation..

Regards,

AnjaniKumar C.A.

[Former Member]

Hi,

Thanks for your reply. I need to do the sorting in Spread sheet level, according to that it will be sorted in components as well as in chart.

Give me an idea to sort in spread sheet level.

Thanks

Sathiya

[Former Member]

Hi Sathiya,

On which particular thing you want sorting. Can you give some sample data, such that i'll get an idea.

Regards,

Anjanikumar C.A.

[Former Member]

Hi,

300,293

78,018

141,699

74,562

35,312

14,609

1,701

3,149

the above numbers will be available in the spread sheet. I need to sort in descending order.

Thanks

Sathiya

[Former Member]

Hi,

You can apply LARGE() function for your data.

LARGE(array, k)

Where array is your dataset and K is your S.No.

Note you have to add a column which contains the S.No, as shown below

1	300	293
2	78	018
3	141	699
4	74	562
5	35	312
6	14	609
7	1	701
8	3	149

Solution 
	A	B	A-ans	B-ans
1	300	293	300	701
2	78	018	141	699
3	141	699	78	609
4	74	562	74	562
5	35	312	35	312
6	14	609	14	293
7	1	701	3	149
8	3	149	1	18

Solution with formula:

	A	B	A-ans	                     B-ans
1	300	293	=LARGE($J$9:$J$16,I9)	=LARGE($K$9:$K$16,I9)
2	78	18	=LARGE($J$9:$J$16,I10)	=LARGE($K$9:$K$16,I10)
3	141	699	=LARGE($J$9:$J$16,I11)	=LARGE($K$9:$K$16,I11)
4	74	562	=LARGE($J$9:$J$16,I12)	=LARGE($K$9:$K$16,I12)
5	35	312	=LARGE($J$9:$J$16,I13)	=LARGE($K$9:$K$16,I13)
6	14	609	=LARGE($J$9:$J$16,I14)	=LARGE($K$9:$K$16,I14)
7	1	701	=LARGE($J$9:$J$16,I15)	=LARGE($K$9:$K$16,I15)
8	3	149	=LARGE($J$9:$J$16,I16)	=LARGE($K$9:$K$16,I16)

If you want ascending then you can use

SMALL() function

Let me know if you have any issues.

Regards,

AnjaniKumar C.A.

Edited by: AnjaniKumarCasula on Apr 26, 2011 5:42 PM