VBA Question: DATA SORT.

[sjc4]

Hey guys, I have a simple VBA question for you. Here it goes:

I have designed a macro to sort two columns of data. The macro sorts the data by matching cells from the two columns. The matching cells are sorted to be at the top of the sheet. The cells that have no match are then put below the last matching cell. So about half way down my sheet I have two columns of unmatching data. I want to add a VBA code that will do a seperate data sort on the non-matching cells, but without effecting the matching cells which are sorted above them. I have an if statement written so I think should be able to use something along the lines of IF cell "has no match" data sort by name. This way I can see the non-matching columns alphabetically rather than in some arbitrary order.

Any suggestions?

 

[Ruddles]

It's much easier to suggest code modifications if we can see the code (so we can see what variables are available to use), and easier still if we can see the worksheet (so we know what the data looks like and where it is).

When posting code, please post it between CODE tags - the # icon in the advanced editor toolbar.

When posting sections of worksheet, place borders around the cells and select a small-ish font size like 8pt or 9pt.

And preview the message before you click Submit to ensure it's easily understandable.

 

[sjc4]

I need to re sort the non-matching data hence the example below:

A1 B1 C1 D1 E1
ABC 5000 ABC 5000 Match
ABC 5000 ABC 5000 Match
ABC 5000 ABC 5000 Match
ABC 5000 ABC 5000 Match
ABC 5000 ABC 5000 Match
ABC 5000 ABC 5000 Match
ABC 5000 ABC 5000 Match
XZY 2131 ASA 3231 No Match
ADA 123 DAD 1999 No Match
XZRY 6731 ALD 35531 No Match
NOA 123 IIOP 15599 No Match

 

[sjc4]

Okay, will do. I'll post it in a bit.

It's much easier to suggest code modifications if we can see the code (so we can see what variables are available to use), and easier still if we can see the worksheet (so we know what the data looks like and where it is).

When posting code, please post it between CODE tags - the # icon in the advanced editor toolbar.

When posting sections of worksheet, place borders around the cells and select a small-ish font size like 8pt or 9pt.

And preview the message before you click Submit to ensure it's easily understandable.

 

[Ruddles]

The reason for placing borders around worksheet extracts before posting them is so that potential respondents can copy and paste your sample data directly into their own worksheets and not have to manipulate it to make it resemble the poster's description of it (which can lead to misunderstandings).

 

[Ruddles]

Based on what you've posted already, it seems that you want to sort the data on column E first (to separate the matched data from the unmatched data, then maybe sort on columns A/B as a secondary sort key.

Try this:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Sub SortMatchedUnmatched()[/FONT]
 
[FONT=Fixedsys]Dim iLastRow As Long[/FONT]
[FONT=Fixedsys]Dim ws As Worksheet[/FONT]
 
[FONT=Fixedsys]Set ws = ThisWorkbook.Sheets(1)[/FONT]
 
[FONT=Fixedsys]iLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row[/FONT]
 
[FONT=Fixedsys]With ws.Sort[/FONT]
[FONT=Fixedsys]  .SortFields.Clear[/FONT]
[FONT=Fixedsys]  .SortFields.Add Key:=Range("E2:E" & iLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/FONT]
[FONT=Fixedsys]  .SortFields.Add Key:=Range("A2:A" & iLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/FONT]
[FONT=Fixedsys]  .SortFields.Add Key:=Range("B2:B" & iLastRow), SortOn:=xlSortOnValues, Order:=xlAscending,   DataOption:=xlSortNormal[/FONT]
[FONT=Fixedsys]  .SetRange Range("A1:E" & iLastRow)[/FONT]
[FONT=Fixedsys]  .Header = xlYes[/FONT]
[FONT=Fixedsys]  .MatchCase = False[/FONT]
[FONT=Fixedsys]  .Orientation = xlTopToBottom[/FONT]
[FONT=Fixedsys]  .SortMethod = xlPinYin[/FONT]
[FONT=Fixedsys]  .Apply[/FONT]
[FONT=Fixedsys]End With[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

If sorting on columns A/B aren't sufficient to sort the unmatched data, simply add keys for columns C/D:-

[FONT=Fixedsys]  .SortFields.Add Key:=Range("C2:C" & iLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/FONT]
[FONT=Fixedsys]  .SortFields.Add Key:=Range("D2:D" & iLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/FONT]

 

[sjc4]

Thank you for the response. I will try your code. I have a VBA/Macro for the matching data but now I need to re-sort the non-matching data alphabetically.

Based on what you've posted already, it seems that you want to sort the data on column E first (to separate the matched data from the unmatched data, then maybe sort on columns A/B as a secondary sort key.

Try this:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Sub SortMatchedUnmatched()[/FONT]
 
[FONT=Fixedsys]Dim iLastRow As Long[/FONT]
[FONT=Fixedsys]Dim ws As Worksheet[/FONT]
 
[FONT=Fixedsys]Set ws = ThisWorkbook.Sheets(1)[/FONT]
 
[FONT=Fixedsys]iLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row[/FONT]
 
[FONT=Fixedsys]With ws.Sort[/FONT]
[FONT=Fixedsys] .SortFields.Clear[/FONT]
[FONT=Fixedsys] .SortFields.Add Key:=Range("E2:E" & iLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/FONT]
[FONT=Fixedsys] .SortFields.Add Key:=Range("A2:A" & iLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/FONT]
[FONT=Fixedsys] .SortFields.Add Key:=Range("B2:B" & iLastRow), SortOn:=xlSortOnValues, Order:=xlAscending,   DataOption:=xlSortNormal[/FONT]
[FONT=Fixedsys] .SetRange Range("A1:E" & iLastRow)[/FONT]
[FONT=Fixedsys] .Header = xlYes[/FONT]
[FONT=Fixedsys] .MatchCase = False[/FONT]
[FONT=Fixedsys] .Orientation = xlTopToBottom[/FONT]
[FONT=Fixedsys] .SortMethod = xlPinYin[/FONT]
[FONT=Fixedsys] .Apply[/FONT]
[FONT=Fixedsys]End With[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

If sorting on columns A/B aren't sufficient to sort the unmatched data, simply add keys for columns C/D:-

[FONT=Fixedsys] .SortFields.Add Key:=Range("C2:C" & iLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/FONT]
[FONT=Fixedsys] .SortFields.Add Key:=Range("D2:D" & iLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal[/FONT]

 

[sjc4]

I was more-so thinking of a code that would see the cells that do not match, and then re-sort them alphabetically.

In this example, I have already created a macro to sort the matching data. But I need that macro to also re-sort the non-matching data alphabetically.

A1 B1 C1 D1 E1
ABC 5000 ABC 5000 Match
ABC 5000 ABC 5000 Match
ABC 5000 ABC 5000 Match
ABC 5000 ABC 5000 Match
ABC 5000 ABC 5000 Match
ABC 5000 ABC 5000 Match
ABC 5000 ABC 5000 Match
XZY 2131 ASA 3231 No Match
ADA 123 DAD 1999 No Match
XZRY 6731 ALD 35531 No Match
NOA 123 IIOP 15599 No Match <!-- / message -->

 

[Ruddles]

My code will admittedly re-sort the matched data as well as the unmatched data, but the matched data won't be changed. The unmatched data on the other hand should become sorted.

If that's not acceptable, post your code and I'll modify it so it only sorts the unmatched data.