Assign numbers in block of 10 starting from lower to higher

[motilulla]

Hello,</SPAN></SPAN>

In the column "D" I got the numbers disordered, they can be from 0 to 3000 max. </SPAN></SPAN>
I need to assign them in column "E" in blocks of 10 (1-10, 11-20...etc.) starting from lower to higher as per example shown below.</SPAN></SPAN>

	A	B	C	D	E

<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0 "><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Result[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]173[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]134[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]157[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]399[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]41[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]42[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]43[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]44[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]46[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]47[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]49[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]51[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]254[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]52[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]53[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]54[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]56[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]57[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]58[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]261[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]59[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]559[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]61[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]158[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]62[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]63[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]236[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]64[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]65[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]66[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]67[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]68[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]69[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]71[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]340[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]72[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]73[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]74[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]76[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]116[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]78[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]79[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]323[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]81[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]82[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]394[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]83[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]84[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]85[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]86[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]88[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]326[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]89[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]90[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]91[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]92[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]93[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]428[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]94[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]95[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]96[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]339[/TD]
[TD="align: right"]14[/TD]

</TBODY>

Sheet1​

Thanks In Advance </SPAN></SPAN>
Using version 2000</SPAN></SPAN>

Regards,</SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

Hello, may be I clear it a bit more assign block of (1 to 10, number 1), block of (11 to 20, number 2)... and so on</SPAN></SPAN>

Regards,</SPAN>
Moti</SPAN></SPAN>

 

[Fluff]

I cannot figure out what you are trying to do, as your results don't make any sense to me.
Are you looking for numbers 1-10 to be 1, 11-20 to be 2 etc regardless of how many numbers exist in those blocks?
Or do you want the 10 smallest numbers (ignoring 0) to be 1, the next batch of 10 numbers to be 2 etc?

 

[motilulla]

I cannot figure out what you are trying to do, as your results don't make any sense to me.
Are you looking for numbers 1-10 to be 1, 11-20 to be 2 etc regardless of how many numbers exist in those blocks?
Or do you want the 10 smallest numbers (ignoring 0) to be 1, the next batch of 10 numbers to be 2 etc?

Hello Fluff, yes it is bit confusing I got in the column "D" 0 to max 3000 number without any repetitions, so as you guess (ignoring the 0 it is correct) and looking for numbers 1-10 to be 1, 11-20 to be 2 and 21-30 to be 3... and so on this is exactly what I want the result in the column "E" </SPAN></SPAN>

Thank you</SPAN></SPAN>


Regards,</SPAN>
Moti</SPAN></SPAN>

 

[shg]

In E5,

=INT((D5+9)/10)

 

[motilulla]

In E5,

=INT((D5+9)/10)

Wow shg, what a beautiful formula spot on worked as wanted query solved!</SPAN></SPAN>

Thank you for your time and help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[shg]

You're welcome.