Data Sorting for each row and column ---- URGENT

[MASOODAHMAD]

Hi,

I want to sort the data from Highest (Old price) to Lowest (Old Price) for each product in the row. Please remember that it should not be sorted columnar wise. The New price in each Row should intact with it's corresponding Old price.

The data is given below:
Version:1.0 StartHTML:0000000170 EndHTML:0000016601 StartFragment:0000001567 EndFragment:0000016555 SourceURL:file://localhost/Users/agi-sony/Documents/Sorting.xls <style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl24 { border: 0.5pt solid windowtext; }.xl25 { background: none repeat scroll 0% 0% rgb(204, 255, 255); }.xl26 { font-weight: 700; background: none repeat scroll 0% 0% rgb(255, 204, 153); }.xl27 { font-weight: 700; background: none repeat scroll 0% 0% rgb(255, 204, 153); }</style>

<col style="" width="85"> <col span="16" width="75"> <tbody>
[TD="class: xl25, width: 85"]Products[/TD]
[TD="class: xl27, width: 75"]Old Price[/TD]
[TD="class: xl27, width: 75"]New Price[/TD]
[TD="class: xl27, width: 75"]Old Price[/TD]
[TD="class: xl27, width: 75"]New Price[/TD]
[TD="class: xl27, width: 75"]Old Price[/TD]
[TD="class: xl27, width: 75"]New Price[/TD]
[TD="class: xl27, width: 75"]Old Price[/TD]
[TD="class: xl27, width: 75"]New Price[/TD]
[TD="class: xl27, width: 75"]Old Price[/TD]
[TD="class: xl27, width: 75"]New Price[/TD]
[TD="class: xl27, width: 75"]Old Price[/TD]
[TD="class: xl27, width: 75"]New Price[/TD]
[TD="class: xl27, width: 75"]Old Price[/TD]
[TD="class: xl27, width: 75"]New Price[/TD]
[TD="class: xl27, width: 75"]Old Price[/TD]
[TD="class: xl27, width: 75"]New Price[/TD]

[TD="class: xl26"]Apple[/TD]
[TD="class: xl24, align: right"]£27.50[/TD]
[TD="class: xl24, align: right"]£23.38[/TD]
[TD="class: xl24, align: right"]£20.00[/TD]
[TD="class: xl24, align: right"]£17.00[/TD]
[TD="class: xl24, align: right"]£8.00[/TD]
[TD="class: xl24, align: right"]£6.80[/TD]
[TD="class: xl24, align: right"]£28.00[/TD]
[TD="class: xl24, align: right"]£23.80[/TD]
[TD="class: xl24, align: right"]£35.00[/TD]
[TD="class: xl24, align: right"]£29.75[/TD]
[TD="class: xl24, align: right"]£24.50[/TD]
[TD="class: xl24, align: right"]£20.83[/TD]
[TD="class: xl24, align: right"]£14.00[/TD]
[TD="class: xl24, align: right"]£11.90[/TD]
[TD="class: xl24, align: right"]£17.50[/TD]
[TD="class: xl24, align: right"]£14.88[/TD]

[TD="class: xl26"]Guava[/TD]
[TD="class: xl24, align: right"]£17.50[/TD]
[TD="class: xl24, align: right"]£14.00[/TD]
[TD="class: xl24, align: right"]£15.00[/TD]
[TD="class: xl24, align: right"]£12.00[/TD]
[TD="class: xl24, align: right"]£25.00[/TD]
[TD="class: xl24, align: right"]£20.00[/TD]
[TD="class: xl24, align: right"]£12.50[/TD]
[TD="class: xl24, align: right"]£10.00[/TD]
[TD="class: xl24, align: right"]£20.00[/TD]
[TD="class: xl24, align: right"]£16.00[/TD]
[TD="class: xl24, align: right"]£22.50[/TD]
[TD="class: xl24, align: right"]£18.00[/TD]
[TD="class: xl24, align: right"]£21.00[/TD]
[TD="class: xl24, align: right"]£16.80[/TD]
[TD="class: xl24, align: right"]£28.00[/TD]
[TD="class: xl24, align: right"]£22.40[/TD]

[TD="class: xl26"]Mangoes[/TD]
[TD="class: xl24, align: right"]£35.00[/TD]
[TD="class: xl24, align: right"]£28.00[/TD]
[TD="class: xl24, align: right"]£24.50[/TD]
[TD="class: xl24, align: right"]£19.60[/TD]
[TD="class: xl24, align: right"]£21.00[/TD]
[TD="class: xl24, align: right"]£16.80[/TD]
[TD="class: xl24, align: right"]£17.50[/TD]
[TD="class: xl24, align: right"]£14.00[/TD]
[TD="class: xl24, align: right"]£28.00[/TD]
[TD="class: xl24, align: right"]£22.40[/TD]
[TD="class: xl24, align: right"]£18.00[/TD]
[TD="class: xl24, align: right"]£14.40[/TD]
[TD="class: xl24, align: right"]£40.00[/TD]
[TD="class: xl24, align: right"]£32.00[/TD]
[TD="class: xl24, align: right"]£25.00[/TD]
[TD="class: xl24, align: right"]£20.00[/TD]

[TD="class: xl26"]Pineapple[/TD]
[TD="class: xl24, align: right"]£3.00[/TD]
[TD="class: xl24, align: right"]£2.40[/TD]
[TD="class: xl24, align: right"]£30.00[/TD]
[TD="class: xl24, align: right"]£24.00[/TD]
[TD="class: xl24, align: right"]£65.00[/TD]
[TD="class: xl24, align: right"]£52.00[/TD]
[TD="class: xl24, align: right"]£50.00[/TD]
[TD="class: xl24, align: right"]£40.00[/TD]
[TD="class: xl24, align: right"]£35.00[/TD]
[TD="class: xl24, align: right"]£28.00[/TD]
[TD="class: xl24, align: right"]£45.50[/TD]
[TD="class: xl24, align: right"]£36.40[/TD]
[TD="class: xl24, align: right"]£42.00[/TD]
[TD="class: xl24, align: right"]£33.60[/TD]
[TD="class: xl24, align: right"]£70.00[/TD]
[TD="class: xl24, align: right"]£56.00[/TD]

[TD="class: xl26"]Watermelon[/TD]
[TD="class: xl24, align: right"]£3.00[/TD]
[TD="class: xl24, align: right"]£2.40[/TD]
[TD="class: xl24, align: right"]£30.00[/TD]
[TD="class: xl24, align: right"]£24.00[/TD]
[TD="class: xl24, align: right"]£65.00[/TD]
[TD="class: xl24, align: right"]£52.00[/TD]
[TD="class: xl24, align: right"]£50.00[/TD]
[TD="class: xl24, align: right"]£40.00[/TD]
[TD="class: xl24, align: right"]£35.00[/TD]
[TD="class: xl24, align: right"]£28.00[/TD]
[TD="class: xl24, align: right"]£45.50[/TD]
[TD="class: xl24, align: right"]£36.40[/TD]
[TD="class: xl24, align: right"]£42.00[/TD]
[TD="class: xl24, align: right"]£33.60[/TD]
[TD="class: xl24, align: right"]£70.00[/TD]
[TD="class: xl24, align: right"]£56.00[/TD]

[TD="class: xl26"]Melon[/TD]
[TD="class: xl24, align: right"]£27.50[/TD]
[TD="class: xl24, align: right"]£23.38[/TD]
[TD="class: xl24, align: right"]£28.00[/TD]
[TD="class: xl24, align: right"]£23.80[/TD]
[TD="class: xl24, align: right"]£8.50[/TD]
[TD="class: xl24, align: right"]£7.23[/TD]
[TD="class: xl24, align: right"]£18.00[/TD]
[TD="class: xl24, align: right"]£15.30[/TD]
[TD="class: xl24, align: right"]£30.00[/TD]
[TD="class: xl24, align: right"]£25.50[/TD]
[TD="class: xl24, align: right"]£20.00[/TD]
[TD="class: xl24, align: right"]£17.00[/TD]
[TD="class: xl24, align: right"]£21.00[/TD]
[TD="class: xl24, align: right"]£17.85[/TD]
[TD="class: xl24, align: right"]£17.50[/TD]
[TD="class: xl24, align: right"]£14.88[/TD]

[TD="class: xl26"]Cherries[/TD]
[TD="class: xl24, align: right"]£24.00[/TD]
[TD="class: xl24, align: right"]£19.20[/TD]
[TD="class: xl24, align: right"]£35.00[/TD]
[TD="class: xl24, align: right"]£28.00[/TD]
[TD="class: xl24, align: right"]£42.00[/TD]
[TD="class: xl24, align: right"]£33.60[/TD]
[TD="class: xl24, align: right"]£24.50[/TD]
[TD="class: xl24, align: right"]£19.60[/TD]
[TD="class: xl24, align: right"]£40.00[/TD]
[TD="class: xl24, align: right"]£32.00[/TD]
[TD="class: xl24, align: right"]£45.00[/TD]
[TD="class: xl24, align: right"]£36.00[/TD]
[TD="class: xl24, align: right"]£21.00[/TD]
[TD="class: xl24, align: right"]£16.80[/TD]
[TD="class: xl24, align: right"]£28.00[/TD]
[TD="class: xl24, align: right"]£22.40[/TD]

[TD="class: xl26"]Strawberry[/TD]
[TD="class: xl24, align: right"]£24.50[/TD]
[TD="class: xl24, align: right"]£19.60[/TD]
[TD="class: xl24, align: right"]£24.00[/TD]
[TD="class: xl24, align: right"]£19.20[/TD]
[TD="class: xl24, align: right"]£40.00[/TD]
[TD="class: xl24, align: right"]£32.00[/TD]
[TD="class: xl24, align: right"]£45.00[/TD]
[TD="class: xl24, align: right"]£36.00[/TD]
[TD="class: xl24, align: right"]£35.00[/TD]
[TD="class: xl24, align: right"]£28.00[/TD]
[TD="class: xl24, align: right"]£17.50[/TD]
[TD="class: xl24, align: right"]£14.00[/TD]
[TD="class: xl24, align: right"]£29.50[/TD]
[TD="class: xl24, align: right"]£23.60[/TD]
[TD="class: xl24, align: right"]£21.00[/TD]
[TD="class: xl24, align: right"]£16.80[/TD]

[TD="class: xl26"]Grapes[/TD]
[TD="class: xl24, align: right"]£50.00[/TD]
[TD="class: xl24, align: right"]£40.00[/TD]
[TD="class: xl24, align: right"]£45.00[/TD]
[TD="class: xl24, align: right"]£36.00[/TD]
[TD="class: xl24, align: right"]£35.00[/TD]
[TD="class: xl24, align: right"]£28.00[/TD]
[TD="class: xl24, align: right"]£40.00[/TD]
[TD="class: xl24, align: right"]£32.00[/TD]
[TD="class: xl24, align: right"]£80.00[/TD]
[TD="class: xl24, align: right"]£64.00[/TD]
[TD="class: xl24, align: right"]£42.00[/TD]
[TD="class: xl24, align: right"]£33.60[/TD]
[TD="class: xl24, align: right"]£25.00[/TD]
[TD="class: xl24, align: right"]£20.00[/TD]
[TD="class: xl24, align: right"]£60.00[/TD]
[TD="class: xl24, align: right"]£48.00[/TD]

[TD="class: xl26"]Pomegranate[/TD]
[TD="class: xl24, align: right"]£10.00[/TD]
[TD="class: xl24, align: right"]£9.00[/TD]
[TD="class: xl24, align: right"]£7.00[/TD]
[TD="class: xl24, align: right"]£6.30[/TD]
[TD="class: xl24, align: right"]£2.00[/TD]
[TD="class: xl24, align: right"]£1.80[/TD]
[TD="class: xl24, align: right"]£3.00[/TD]
[TD="class: xl24, align: right"]£2.70[/TD]
[TD="class: xl24, align: right"]£5.00[/TD]
[TD="class: xl24, align: right"]£4.50[/TD]
[TD="class: xl24, align: right"]£8.50[/TD]
[TD="class: xl24, align: right"]£7.65[/TD]
[TD="class: xl24, align: right"]£9.00[/TD]
[TD="class: xl24, align: right"]£8.10[/TD]
[TD="class: xl24, align: right"]£1.75[/TD]
[TD="class: xl24, align: right"]£1.58[/TD]

[TD="class: xl26"]Plum[/TD]
[TD="class: xl24, align: right"]£15.00[/TD]
[TD="class: xl24, align: right"]£12.00[/TD]
[TD="class: xl24, align: right"]£12.00[/TD]
[TD="class: xl24, align: right"]£9.60[/TD]
[TD="class: xl24, align: right"]£8.00[/TD]
[TD="class: xl24, align: right"]£6.40[/TD]
[TD="class: xl24, align: right"]£17.50[/TD]
[TD="class: xl24, align: right"]£14.00[/TD]
[TD="class: xl24, align: right"]£18.50[/TD]
[TD="class: xl24, align: right"]£14.80[/TD]
[TD="class: xl24, align: right"]£3.00[/TD]
[TD="class: xl24, align: right"]£2.40[/TD]
[TD="class: xl24, align: right"]£14.00[/TD]
[TD="class: xl24, align: right"]£11.20[/TD]
[TD="class: xl24, align: right"]£20.00[/TD]
[TD="class: xl24, align: right"]£16.00[/TD]

[TD="class: xl26"]Pear[/TD]
[TD="class: xl24, align: right"]£15.00[/TD]
[TD="class: xl24, align: right"]£13.50[/TD]
[TD="class: xl24, align: right"]£8.00[/TD]
[TD="class: xl24, align: right"]£7.20[/TD]
[TD="class: xl24, align: right"]£10.00[/TD]
[TD="class: xl24, align: right"]£9.00[/TD]
[TD="class: xl24, align: right"]£12.50[/TD]
[TD="class: xl24, align: right"]£11.25[/TD]
[TD="class: xl24, align: right"]£16.00[/TD]
[TD="class: xl24, align: right"]£14.40[/TD]
[TD="class: xl24, align: right"]£6.00[/TD]
[TD="class: xl24, align: right"]£5.40[/TD]
[TD="class: xl24, align: right"]£25.00[/TD]
[TD="class: xl24, align: right"]£22.50[/TD]
[TD="class: xl24, align: right"]£20.00[/TD]
[TD="class: xl24, align: right"]£18.00[/TD]

[TD="class: xl26"] [/TD]
[TD="class: xl24, align: right"]£20.00[/TD]
[TD="class: xl24, align: right"]£16.00[/TD]
[TD="class: xl24, align: right"]£18.00[/TD]
[TD="class: xl24, align: right"]£14.40[/TD]
[TD="class: xl24, align: right"]£14.00[/TD]
[TD="class: xl24, align: right"]£11.20[/TD]
[TD="class: xl24, align: right"]£12.00[/TD]
[TD="class: xl24, align: right"]£9.60[/TD]
[TD="class: xl24, align: right"]£30.00[/TD]
[TD="class: xl24, align: right"]£24.00[/TD]
[TD="class: xl24, align: right"]£25.00[/TD]
[TD="class: xl24, align: right"]£20.00[/TD]
[TD="class: xl24, align: right"]£17.50[/TD]
[TD="class: xl24, align: right"]£14.00[/TD]
[TD="class: xl24, align: right"]£21.00[/TD]
[TD="class: xl24, align: right"]£16.80[/TD]

[TD="class: xl26"] [/TD]
[TD="class: xl24, align: right"]£10.00[/TD]
[TD="class: xl24, align: right"]£9.00[/TD]
[TD="class: xl24, align: right"]£20.00[/TD]
[TD="class: xl24, align: right"]£18.00[/TD]
[TD="class: xl24, align: right"]£17.50[/TD]
[TD="class: xl24, align: right"]£15.75[/TD]
[TD="class: xl24, align: right"]£14.00[/TD]
[TD="class: xl24, align: right"]£12.60[/TD]
[TD="class: xl24, align: right"]£18.00[/TD]
[TD="class: xl24, align: right"]£16.20[/TD]
[TD="class: xl24, align: right"]£16.00[/TD]
[TD="class: xl24, align: right"]£14.40[/TD]
[TD="class: xl24, align: right"]£8.00[/TD]
[TD="class: xl24, align: right"]£7.20[/TD]
[TD="class: xl24, align: right"]£12.50[/TD]
[TD="class: xl24, align: right"]£11.25[/TD]

[TD="class: xl26"] [/TD]
[TD="class: xl24, align: right"]£3.00[/TD]
[TD="class: xl24, align: right"]£2.40[/TD]
[TD="class: xl24, align: right"]£30.00[/TD]
[TD="class: xl24, align: right"]£24.00[/TD]
[TD="class: xl24, align: right"]£65.00[/TD]
[TD="class: xl24, align: right"]£52.00[/TD]
[TD="class: xl24, align: right"]£50.00[/TD]
[TD="class: xl24, align: right"]£40.00[/TD]
[TD="class: xl24, align: right"]£35.00[/TD]
[TD="class: xl24, align: right"]£28.00[/TD]
[TD="class: xl24, align: right"]£45.50[/TD]
[TD="class: xl24, align: right"]£36.40[/TD]
[TD="class: xl24, align: right"]£42.00[/TD]
[TD="class: xl24, align: right"]£33.60[/TD]
[TD="class: xl24, align: right"]£70.00[/TD]
[TD="class: xl24, align: right"]£56.00[/TD]

</tbody>

I have done one row manually:

Version:1.0 StartHTML:0000000170 EndHTML:0000002679 StartFragment:0000001551 EndFragment:0000002626 SourceURL:file://localhost/Users/agi-sony/Documents/Sorting.xls <style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl24 { border: 0.5pt solid windowtext; }.xl25 { font-weight: 700; background: none repeat scroll 0% 0% rgb(255, 204, 153); }</style>

<col style="" width="85"> <col span="16" width="75"> <tbody>
[TD="class: xl25, width: 85"]Apple[/TD]
[TD="class: xl24, width: 75, align: right"]£35.00[/TD]
[TD="class: xl24, width: 75, align: right"]£29.75[/TD]
[TD="class: xl24, width: 75, align: right"]£28.00[/TD]
[TD="class: xl24, width: 75, align: right"]£23.80[/TD]
[TD="class: xl24, width: 75, align: right"]£27.50[/TD]
[TD="class: xl24, width: 75, align: right"]£23.38[/TD]
[TD="class: xl24, width: 75, align: right"]£24.50[/TD]
[TD="class: xl24, width: 75, align: right"]£20.83[/TD]
[TD="class: xl24, width: 75, align: right"]£20.00[/TD]
[TD="class: xl24, width: 75, align: right"]£17.00[/TD]
[TD="class: xl24, width: 75, align: right"]£17.50[/TD]
[TD="class: xl24, width: 75, align: right"]£14.88[/TD]
[TD="class: xl24, width: 75, align: right"]£14.00[/TD]
[TD="class: xl24, width: 75, align: right"]£11.90[/TD]
[TD="class: xl24, width: 75, align: right"]£8.00[/TD]
[TD="class: xl24, width: 75, align: right"]£6.80[/TD]

</tbody>

 

[bigblase]

Hi,
Could you upload the excel file so I can download it?
Thanks

 

[Marcol]

Try this
Copy the headers to Sheet2

With Sheet2
In A2

=IF(Sheet1!A2="","",Sheet1!A2)

Drag/Fill Down
In B2

=IF(ISODD(COLUMN(A1)),LARGE(INDEX((Sheet1!$B$1:$Q$1=Sheet1!$B$1)*Sheet1!$B2:$Q2,,),INT(COLUMN(A1)/2)+1),INDEX(Sheet1!$B2:$Q2,MATCH(A2,Sheet1!$B2:$Q2,0)+1))

Drag Across to Column Q, then Down

This should work provided there are no Duplicate "Old Prices" in any one Row.

 

[MASOODAHMAD]

Sorry, bigblase, I could not find how to upload the file in the forum

 

[MASOODAHMAD]

Sorry Marcol, It is not working!!!

There are Duplicate Old and New prices. Please suggest some other formula or VBA.

 

[bigblase]

I copy hiker95's signature which is a great help to upload files: "If you are not able to give us screenshots you can upload your workbook to Box Net, mark the workbook for sharing, and provide us with a link to your workbook."

 

[Marcol]

Try this ...
With Sheet2
Drag A2 Down

In A2

=IF(ISODD(COLUMN(A1)),ROUND(LARGE(INDEX((Sheet1!$B$1:$Q$1=Sheet1!$B$1)*(Sheet1!$B2:$Q2+(COLUMN($A:$P)/10^6)),,),INT(COLUMN(A1)/2)+1),2),
INDEX(Sheet1!$B2:$Q2,MOD(LARGE(INDEX((Sheet1!$B$1:$Q$1=Sheet1!$B$1)*(Sheet1!$B2:$Q2+(COLUMN($A:$P)/10^6)),,),COLUMN(A:A)/2)*10^6,100)+1))

Drag Across to Column Q, then Down

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Products	Old Price	New Price	Old Price	New Price	Old Price	New Price	Old Price	New Price	Old Price	New Price	Old Price	New Price	Old Price	New Price	Old Price	New Price
2	Apple	35.00	29.75	28.00	23.80	27.50	23.38	24.50	20.83	20.00	17.00	17.50	14.88	14.00	11.90	8.00	6.80
3	Guava	28.00	22.40	25.00	20.00	22.50	18.00	21.00	16.80	20.00	16.00	17.50	14.00	15.00	12.00	12.50	10.00
4	Mangoes	40.00	32.00	35.00	28.00	28.00	22.40	25.00	20.00	24.50	19.60	21.00	16.80	18.00	14.40	17.50	14.00
5	Pineapple	70.00	56.00	65.00	52.00	50.00	40.00	45.50	36.40	42.00	33.60	35.00	28.00	30.00	24.00	3.00	2.40
6	Watermelon	70.00	56.00	65.00	52.00	50.00	40.00	45.50	36.40	42.00	33.60	35.00	28.00	30.00	24.00	3.00	2.40
7	Melon	30.00	25.50	28.00	23.80	27.50	23.38	21.00	17.85	20.00	17.00	18.00	15.30	17.50	14.88	8.50	7.23
8	Cherries	45.00	36.00	42.00	33.60	40.00	32.00	35.00	28.00	28.00	22.40	24.50	19.60	24.00	19.20	21.00	16.80
9	Strawberry	45.00	36.00	40.00	32.00	35.00	28.00	29.50	23.60	24.50	19.60	24.00	19.20	21.00	16.80	17.50	14.00
10	Grapes	80.00	64.00	60.00	48.00	50.00	40.00	45.00	36.00	42.00	33.60	40.00	32.00	35.00	28.00	25.00	20.00
Sheet2

 

[MASOODAHMAD]

Hi Blase,

Here is the link to the file:

https://www.box.com/s/azu08vedd98my6h0rhu4

 

[MASOODAHMAD]

Hi Marcol,

I tried your formula, copied the first formula in A2 and the latter in B2, here is my result:

The cell range is :
Row 1 is blank
Values range: A2:Q13


Version:1.0 StartHTML:0000000105 EndHTML:0000012381 StartFragment:0000001255 EndFragment:0000012335 <style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl24 { }</style>

Apple

Guava

Mangoes

Pineapple

Watermelon

Melon

Cherries

Strawberry

Grapes

Pomegranate

Plum

Pear

<tbody>
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]
[TD="width: 75"][/TD]

[TD="class: xl24, align: right"]35.0[/TD]
[TD="class: xl24, align: right"]29.8[/TD]
[TD="class: xl24, align: right"]28.0[/TD]
[TD="class: xl24, align: right"]27.5[/TD]
[TD="class: xl24, align: right"]24.5[/TD]
[TD="class: xl24, align: right"]20.8[/TD]
[TD="class: xl24, align: right"]20.0[/TD]
[TD="class: xl24, align: right"]17.5[/TD]
[TD="class: xl24, align: right"]17.7[/TD]
[TD="class: xl24, align: center"]#REF![/TD]
[TD="class: xl24, align: right"]14.9[/TD]
[TD="class: xl24, align: right"]12.6[/TD]
[TD="class: xl24, align: right"]14.0[/TD]
[TD="class: xl24, align: right"]11.9[/TD]
[TD="class: xl24, align: right"]8.0[/TD]
[TD="class: xl24, align: right"]6.8[/TD]

[TD="class: xl24, align: right"]28.0[/TD]
[TD="class: xl24, align: right"]25.0[/TD]
[TD="class: xl24, align: right"]22.5[/TD]
[TD="class: xl24, align: right"]18.0[/TD]
[TD="class: xl24, align: right"]21.0[/TD]
[TD="class: xl24, align: right"]16.8[/TD]
[TD="class: xl24, align: right"]20.0[/TD]
[TD="class: xl24, align: right"]16.0[/TD]
[TD="class: xl24, align: right"]17.5[/TD]
[TD="class: xl24, align: right"]14.0[/TD]
[TD="class: xl24, align: right"]12.0[/TD]
[TD="class: xl24, align: right"]12.5[/TD]
[TD="class: xl24, align: right"]11.2[/TD]
[TD="class: xl24, align: right"]15.0[/TD]
[TD="class: xl24, align: right"]10.0[/TD]
[TD="class: xl24, align: right"]8.0[/TD]

[TD="class: xl24, align: right"]40.0[/TD]
[TD="class: xl24, align: right"]32.0[/TD]
[TD="class: xl24, align: right"]35.0[/TD]
[TD="class: xl24, align: right"]28.0[/TD]
[TD="class: xl24, align: right"]28.0[/TD]
[TD="class: xl24, align: right"]22.4[/TD]
[TD="class: xl24, align: right"]25.0[/TD]
[TD="class: xl24, align: right"]21.0[/TD]
[TD="class: xl24, align: right"]22.4[/TD]
[TD="class: xl24, align: right"]24.5[/TD]
[TD="class: xl24, align: right"]19.6[/TD]
[TD="class: xl24, align: right"]17.5[/TD]
[TD="class: xl24, align: right"]18.0[/TD]
[TD="class: xl24, align: right"]14.4[/TD]
[TD="class: xl24, align: right"]14.0[/TD]
[TD="class: xl24, align: right"]11.2[/TD]

[TD="class: xl24, align: right"]70.0[/TD]
[TD="class: xl24, align: right"]65.0[/TD]
[TD="class: xl24, align: right"]45.5[/TD]
[TD="class: xl24, align: right"]36.4[/TD]
[TD="class: xl24, align: right"]42.0[/TD]
[TD="class: xl24, align: right"]33.6[/TD]
[TD="class: xl24, align: right"]40.0[/TD]
[TD="class: xl24, align: right"]32.0[/TD]
[TD="class: xl24, align: right"]35.0[/TD]
[TD="class: xl24, align: right"]28.0[/TD]
[TD="class: xl24, align: right"]24.0[/TD]
[TD="class: xl24, align: right"]50.0[/TD]
[TD="class: xl24, align: right"]3.0[/TD]
[TD="class: xl24, align: right"]2.4[/TD]
[TD="class: xl24, align: right"]1.9[/TD]
[TD="class: xl24, align: right"]30.0[/TD]

[TD="class: xl24, align: right"]70.0[/TD]
[TD="class: xl24, align: right"]65.0[/TD]
[TD="class: xl24, align: right"]45.5[/TD]
[TD="class: xl24, align: right"]36.4[/TD]
[TD="class: xl24, align: right"]42.0[/TD]
[TD="class: xl24, align: right"]33.6[/TD]
[TD="class: xl24, align: right"]40.0[/TD]
[TD="class: xl24, align: right"]32.0[/TD]
[TD="class: xl24, align: right"]35.0[/TD]
[TD="class: xl24, align: right"]28.0[/TD]
[TD="class: xl24, align: right"]24.0[/TD]
[TD="class: xl24, align: right"]50.0[/TD]
[TD="class: xl24, align: right"]3.0[/TD]
[TD="class: xl24, align: right"]2.4[/TD]
[TD="class: xl24, align: right"]1.9[/TD]
[TD="class: xl24, align: right"]30.0[/TD]

[TD="class: xl24, align: right"]30.0[/TD]
[TD="class: xl24, align: right"]25.5[/TD]
[TD="class: xl24, align: right"]27.5[/TD]
[TD="class: xl24, align: right"]23.4[/TD]
[TD="class: xl24, align: right"]23.8[/TD]
[TD="class: xl24, align: right"]18.0[/TD]
[TD="class: xl24, align: right"]21.0[/TD]
[TD="class: xl24, align: right"]17.9[/TD]
[TD="class: xl24, align: right"]20.0[/TD]
[TD="class: xl24, align: right"]17.0[/TD]
[TD="class: xl24, align: right"]19.9[/TD]
[TD="class: xl24, align: center"]#REF![/TD]
[TD="class: xl24, align: right"]17.5[/TD]
[TD="class: xl24, align: right"]8.5[/TD]
[TD="class: xl24, align: right"]15.3[/TD]
[TD="class: xl24, align: right"]13.0[/TD]

[TD="class: xl24, align: right"]45.0[/TD]
[TD="class: xl24, align: right"]36.0[/TD]
[TD="class: xl24, align: right"]40.0[/TD]
[TD="class: xl24, align: right"]32.0[/TD]
[TD="class: xl24, align: right"]28.0[/TD]
[TD="class: xl24, align: right"]24.5[/TD]
[TD="class: xl24, align: right"]28.0[/TD]
[TD="class: xl24, align: right"]42.0[/TD]
[TD="class: xl24, align: right"]24.0[/TD]
[TD="class: xl24, align: right"]19.2[/TD]
[TD="class: xl24, align: right"]21.0[/TD]
[TD="class: xl24, align: right"]16.8[/TD]
[TD="class: xl24, align: right"]19.6[/TD]
[TD="class: xl24, align: right"]15.7[/TD]
[TD="class: xl24, align: right"]15.4[/TD]
[TD="class: xl24, align: right"]35.0[/TD]

[TD="class: xl24, align: right"]36.0[/TD]
[TD="class: xl24, align: right"]28.8[/TD]
[TD="class: xl24, align: right"]35.0[/TD]
[TD="class: xl24, align: right"]28.0[/TD]
[TD="class: xl24, align: right"]29.5[/TD]
[TD="class: xl24, align: right"]23.6[/TD]
[TD="class: xl24, align: right"]24.5[/TD]
[TD="class: xl24, align: right"]19.6[/TD]
[TD="class: xl24, align: right"]21.0[/TD]
[TD="class: xl24, align: right"]40.0[/TD]
[TD="class: xl24, align: right"]19.2[/TD]
[TD="class: xl24, align: right"]45.0[/TD]
[TD="class: xl24, align: right"]17.5[/TD]
[TD="class: xl24, align: right"]14.0[/TD]
[TD="class: xl24, align: right"]15.7[/TD]
[TD="class: xl24, align: right"]24.0[/TD]

[TD="class: xl24, align: right"]80.0[/TD]
[TD="class: xl24, align: right"]64.0[/TD]
[TD="class: xl24, align: right"]60.0[/TD]
[TD="class: xl24, align: right"]35.0[/TD]
[TD="class: xl24, align: right"]50.0[/TD]
[TD="class: xl24, align: right"]40.0[/TD]
[TD="class: xl24, align: right"]42.0[/TD]
[TD="class: xl24, align: right"]33.6[/TD]
[TD="class: xl24, align: right"]36.0[/TD]
[TD="class: xl24, align: right"]40.0[/TD]
[TD="class: xl24, align: right"]32.0[/TD]
[TD="class: xl24, align: right"]25.6[/TD]
[TD="class: xl24, align: right"]32.0[/TD]
[TD="class: xl24, align: right"]45.0[/TD]
[TD="class: xl24, align: right"]25.0[/TD]
[TD="class: xl24, align: right"]20.0[/TD]

[TD="class: xl24, align: right"]10.0[/TD]
[TD="class: xl24, align: right"]9.0[/TD]
[TD="class: xl24, align: right"]9.0[/TD]
[TD="class: xl24, align: right"]8.1[/TD]
[TD="class: xl24, align: right"]8.5[/TD]
[TD="class: xl24, align: right"]7.7[/TD]
[TD="class: xl24, align: right"]8.1[/TD]
[TD="class: xl24, align: right"]7.0[/TD]
[TD="class: xl24, align: right"]6.3[/TD]
[TD="class: xl24, align: right"]3.0[/TD]
[TD="class: xl24, align: right"]5.0[/TD]
[TD="class: xl24, align: right"]4.5[/TD]
[TD="class: xl24, align: right"]2.7[/TD]
[TD="class: xl24, align: right"]2.4[/TD]
[TD="class: xl24, align: right"]1.8[/TD]
[TD="class: xl24, align: right"]2.0[/TD]

[TD="class: xl24, align: right"]20.0[/TD]
[TD="class: xl24, align: right"]8.0[/TD]
[TD="class: xl24, align: right"]18.5[/TD]
[TD="class: xl24, align: right"]14.8[/TD]
[TD="class: xl24, align: right"]15.0[/TD]
[TD="class: xl24, align: right"]12.0[/TD]
[TD="class: xl24, align: right"]14.0[/TD]
[TD="class: xl24, align: right"]11.2[/TD]
[TD="class: xl24, align: right"]14.0[/TD]
[TD="class: xl24, align: right"]11.2[/TD]
[TD="class: xl24, align: right"]9.6[/TD]
[TD="class: xl24, align: right"]17.5[/TD]
[TD="class: xl24, align: right"]9.6[/TD]
[TD="class: xl24, align: right"]12.0[/TD]
[TD="class: xl24, align: right"]3.0[/TD]
[TD="class: xl24, align: right"]2.4[/TD]

[TD="class: xl24, align: right"]25.0[/TD]
[TD="class: xl24, align: right"]22.5[/TD]
[TD="class: xl24, align: right"]20.0[/TD]
[TD="class: xl24, align: right"]10.0[/TD]
[TD="class: xl24, align: right"]16.0[/TD]
[TD="class: xl24, align: right"]14.4[/TD]
[TD="class: xl24, align: right"]15.0[/TD]
[TD="class: xl24, align: right"]13.5[/TD]
[TD="class: xl24, align: right"]12.2[/TD]
[TD="class: xl24, align: right"]8.0[/TD]
[TD="class: xl24, align: right"]11.3[/TD]
[TD="class: xl24, align: right"]10.1[/TD]
[TD="class: xl24, align: right"]7.2[/TD]
[TD="class: xl24, align: right"]12.5[/TD]
[TD="class: xl24, align: right"]6.0[/TD]
[TD="class: xl24, align: right"]5.4[/TD]

</tbody>

 

[Marcol]

Your original posted sample didn't make it clear that some of your values in thr data table were formula derived.

You should really round these cells to some acceptable degree of accuracy.
e.g.
=ROUND(D11-(D11*10%),5)

To handle the file as it stands try this in B2

=IF(ISODD(COLUMN(A1)),ROUND(LARGE(INDEX((Sheet1!$B$1:$Q$1=Sheet1!$B$1)*(Sheet1!$B2:$Q2+(COLUMN($A:$P)/10^[COLOR=#ff0000]9[/COLOR])),,),INT(COLUMN(A1)/2)+1),2),
INDEX(Sheet1!$B2:$Q2,MOD(LARGE(INDEX((Sheet1!$B$1:$Q$1=Sheet1!$B$1)*(Sheet1!$B2:$Q2+(COLUMN($A:$P)/10^[COLOR=#ff0000]9[/COLOR])),,),COLUMN(A:A)/2)*10^[COLOR=#ff0000]9[/COLOR],100)+1))

This should clear the #REF! errors