Rearrange data based on values proximity/range and add an identifier to 'group' the data

S

somdai

New Member
Joined
Oct 2, 2012
Messages
8
I have 3 columns of data.
[TABLE="width: 144"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]C
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]160
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]132
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]50
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]250
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]128
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]48
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]160
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]129
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]41
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]169
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]132
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]40
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]74
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]126
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]33
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]74
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]126
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]33
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]172
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]121
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]53
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]253
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]119
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]52
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]170
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]120
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]46
[/TD]
[/TR]
</TBODY>[/TABLE]

Is there a way to rearrange the data in such a way that values are within a range of 10 in each column as shown below?
Can you add an 'identifier' to 'group' the data?

[TABLE="width: 192"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Identifier
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]160
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]132
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]50
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]160
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]129
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]41
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]169
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]132
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]40
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]74
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]126
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]33
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]74
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]126
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]33
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]172
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]121
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]53
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]3
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]170
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]120
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]46
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]3
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]250
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]128
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]48
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]4
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]253
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]119
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]52
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]4
[/TD]
[/TR]
</TBODY>[/TABLE]


Thanks & Cheers!!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
somdai said:
I have 3 columns of data.
[TABLE="width: 144"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]C
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]160
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]132
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]50
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]250
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]128
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]48
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]160
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]129
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]41
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]169
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]132
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]40
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]74
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]126
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]33
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]74
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]126
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]33
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]172
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]121
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]53
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]253
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]119
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]52
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]170
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]120
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]46
[/TD]
[/TR]
</TBODY>[/TABLE]

Is there a way to rearrange the data in such a way that values are within a range of 10 in each column as shown below?
Can you add an 'identifier' to 'group' the data?

[TABLE="width: 192"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Identifier
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]160
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]132
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]50
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]160
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]129
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]41
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]169
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]132
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]40
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]74
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]126
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]33
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]74
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]126
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]33
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]172
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]121
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]53
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]3
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]170
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]120
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]46
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]3
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]250
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]128
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]48
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]4
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]253
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]119
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]52
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]4
[/TD]
[/TR]
</TBODY>[/TABLE]


Thanks & Cheers!!
Click to expand...


I believe that sorting the columns in ascending order would come about as close as anything else.
 
Upvote 0
Thanks JLFWhiz. Sorting in ascending order would give me a close result but not quite the result I am looking for. Could there perhaps be a formula that I could use?
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Report with Grouping
2
Replies
19
Views
426
DanteAmor
DanteAmor
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
269
Cquake
C
A
  • Solved
rearranging data based on headers and get rid of merged cells
2
Replies
15
Views
513
kvsrinivasamurthy
K
L
  • Question Question
How to make a dynamic horizontal line data range in excel?
Replies
3
Views
522
Eric W
Eric W
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
588
awoohaw
awoohaw

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top