Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default How do I match values in a sub set after a sort?

This is kind of complex I think, but I'm hoping someone might have an easy solution. I need to sort two columns for match parings, but the second column needs to match as closely to the nearest value of the grouping ahead of it. In other words, I need the sort to reverse itself after each whole number break in the first column. Here is an example:

Raw data
Score Comp
20 5
20 9
20 0
19 12
19 4
19 3
19 1
18 8
18 7
18 7

How I want it to come out:

Score Comp
20 9
20 5
20 0
19 1
19 3
19 4
19 12
18 8
18 7
18 7

The problem is simply doing a double sort gets this:

20 9
20 5
20 0
19 12
19 4
19 3
19 1
18 8
18 7
18 7

Any suggestions? Thank you all very much for your help.

David
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,045
Default How do I match values in a sub set after a sort?

On Thu, 10 Jan 2013 02:44:58 +0000, DavidOakland wrote:


This is kind of complex I think, but I'm hoping someone might have an
easy solution. I need to sort two columns for match parings, but the
second column needs to match as closely to the nearest value of the
grouping ahead of it. In other words, I need the sort to reverse itself
after each whole number break in the first column. Here is an example:

Raw data
Score Comp
20 5
20 9
20 0
19 12
19 4
19 3
19 1
18 8
18 7
18 7

How I want it to come out:

Score Comp
20 9
20 5
20 0
19 1
19 3
19 4
19 12
18 8
18 7
18 7

The problem is simply doing a double sort gets this:

20 9
20 5
20 0
19 12
19 4
19 3
19 1
18 8
18 7
18 7

Any suggestions? Thank you all very much for your help.

David


I would use a macro to do this. First sort by Score and Comp descending; then select and sort evey 2nd group in Comp Ascending.
The macro below assumes your data is an a region that includes A2 and is bounded by a blank row and column. It also assumes that there are no empty cells in column 1.
If the above is not the case, then you will need to provide a realistic example.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.
It will run on the active worksheet, so ensure that is the one your are viewing.

==============================================
Option Explicit
Sub SortAltCol2()
Dim r2Sort As Range
Dim rSubRange As Range
Dim c As Range, v As Variant
'Assume sort range is the region including A2
Set r2Sort = Range("a2").CurrentRegion
ActiveSheet.Sort.SortFields.Clear
r2Sort.Sort key1:=r2Sort.Columns(1), order1:=xlDescending, _
key2:=r2Sort.Columns(2), order2:=xlDescending, _
Header:=xlYes

'Now sort subranges of column 2
'Only need to do every other
Set c = r2Sort(2, 1)
Do Until c.Value = ""
NextChange c
Set rSubRange = c.Offset(columnoffset:=1)
NextChange c
Set rSubRange = rSubRange.Resize(rowsize:=c.Row - rSubRange.Row)
If Not Intersect(rSubRange, r2Sort) Is Nothing Then
rSubRange.Sort key1:=rSubRange.Columns(1), order1:=xlAscending, _
Header:=xlNo
End If
Loop
End Sub

Private Sub NextChange(c As Range)
Dim v As Variant
v = c.Value
Do Until v < c.Value
Set c = c(2)
If c.Value = "" Then Exit Do
Loop
End Sub
========================================
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match formula to match values in multiple columns K[_2_] Excel Discussion (Misc queries) 2 April 22nd 10 10:22 AM
Match formula to match values in multiple columns K[_2_] Excel Programming 2 April 21st 10 01:38 PM
Sort by Large, Offset from Match, Duplicate values problem JWC Excel Excel Discussion (Misc queries) 2 March 17th 10 12:14 PM
how can we get unique values in match function for same match key. Xcel[_2_] Excel Worksheet Functions 11 December 7th 07 09:13 PM
How do I match identical values in 2 columns and then sort? Godswatch Excel Discussion (Misc queries) 1 November 9th 05 07:55 PM


All times are GMT +1. The time now is 07:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"