Is it possible to A-Z sort a list, and instruct certain cells to be sorted by
a different word than is entered into that cell? For example, sort the cell
containing "Bordeaux" as if it were the word"France" so that Bordeaux will
always be grouped with the cells containing "France."

And/or, in a scenerio with two columns and many rows is it possible to A-Z
sort by the first column, except where the word "see" appears in a cell in
the second column Excel uses the word that follows "see" as the term by which
that row is sorted. For example, the word in the first cell is "Bordeaux"
and in the second cell is "see France," rather than sorting this with the
B's, it is grouped with all rows that contain "France" in the first column.

Thanks for any help!

HM

Sink,

I think I'd do a post process. ie. Do the sort, then search for words like
Bordeaux and move it under France. I'm assuming you'd have a list of the
words that should be 'custom sorted.'

Mike

"sinkhole" wrote:

Is it possible to A-Z sort a list, and instruct certain cells to be sorted by
a different word than is entered into that cell? For example, sort the cell
containing "Bordeaux" as if it were the word"France" so that Bordeaux will
always be grouped with the cells containing "France."

And/or, in a scenerio with two columns and many rows is it possible to A-Z
sort by the first column, except where the word "see" appears in a cell in
the second column Excel uses the word that follows "see" as the term by which
that row is sorted. For example, the word in the first cell is "Bordeaux"
and in the second cell is "see France," rather than sorting this with the
B's, it is grouped with all rows that contain "France" in the first column.

Thanks for any help!

HM

Thanks, but the list is so long, that I'd like to institute a sorting order
that would take care of the post process automatically rather than me doing
it all manually.

Thanks for your reply.

"crazybass2" wrote:

Sink,

I think I'd do a post process. ie. Do the sort, then search for words like
Bordeaux and move it under France. I'm assuming you'd have a list of the
words that should be 'custom sorted.'

Mike

"sinkhole" wrote:

Is it possible to A-Z sort a list, and instruct certain cells to be sorted by
a different word than is entered into that cell? For example, sort the cell
containing "Bordeaux" as if it were the word"France" so that Bordeaux will
always be grouped with the cells containing "France."

And/or, in a scenerio with two columns and many rows is it possible to A-Z
sort by the first column, except where the word "see" appears in a cell in
the second column Excel uses the word that follows "see" as the term by which
that row is sorted. For example, the word in the first cell is "Bordeaux"
and in the second cell is "see France," rather than sorting this with the
B's, it is grouped with all rows that contain "France" in the first column.

Thanks for any help!

HM

Create a helper column that contains the real word, then sort that.
You could populate this with a VLOOKUP of desired value. If the VLOOKUP
errors (no real word applicable) use the word in the column.

NickHK


"sinkhole" ...
Thanks, but the list is so long, that I'd like to institute a sorting
order
that would take care of the post process automatically rather than me
doing
it all manually.

Thanks for your reply.

"crazybass2" wrote:

Sink,

I think I'd do a post process. ie. Do the sort, then search for words
like
Bordeaux and move it under France. I'm assuming you'd have a list of the
words that should be 'custom sorted.'

Mike

"sinkhole" wrote:

Is it possible to A-Z sort a list, and instruct certain cells to be
sorted by
a different word than is entered into that cell? For example, sort the
cell
containing "Bordeaux" as if it were the word"France" so that Bordeaux
will
always be grouped with the cells containing "France."

And/or, in a scenerio with two columns and many rows is it possible to
A-Z
sort by the first column, except where the word "see" appears in a cell
in
the second column Excel uses the word that follows "see" as the term by
which
that row is sorted. For example, the word in the first cell is
"Bordeaux"
and in the second cell is "see France," rather than sorting this with
the
B's, it is grouped with all rows that contain "France" in the first
column.

Thanks for any help!

HM

Thanks, the issue is that the list is so long that to create a helper colomn
would take as long or longer than just manually arranging the minority of
rows which need to be sorted by the real word. Thanksk for your response.

"NewsNet" wrote:

Create a helper column that contains the real word, then sort that.
You could populate this with a VLOOKUP of desired value. If the VLOOKUP
errors (no real word applicable) use the word in the column.

NickHK


"sinkhole" ...
Thanks, but the list is so long, that I'd like to institute a sorting
order
that would take care of the post process automatically rather than me
doing
it all manually.

Thanks for your reply.

"crazybass2" wrote:

Sink,

I think I'd do a post process. ie. Do the sort, then search for words
like
Bordeaux and move it under France. I'm assuming you'd have a list of the
words that should be 'custom sorted.'

Mike

"sinkhole" wrote:

Is it possible to A-Z sort a list, and instruct certain cells to be
sorted by
a different word than is entered into that cell? For example, sort the
cell
containing "Bordeaux" as if it were the word"France" so that Bordeaux
will
always be grouped with the cells containing "France."

And/or, in a scenerio with two columns and many rows is it possible to
A-Z
sort by the first column, except where the word "see" appears in a cell
in
the second column Excel uses the word that follows "see" as the term by
which
that row is sorted. For example, the word in the first cell is
"Bordeaux"
and in the second cell is "see France," rather than sorting this with
the
B's, it is grouped with all rows that contain "France" in the first
column.

Thanks for any help!

HM

Sinkhole,

Without a list of French terms to accompany your long list there is no code
that you can write that will accomplish this sort. You must have a list of
terms that should be sorted under France otherwise the code will not know.
VBA does not intuitively know which words you want sorted under France. You
must tell it.

If you can provide a list then code can be written.

Mike

"sinkhole" wrote:

Thanks, the issue is that the list is so long that to create a helper colomn
would take as long or longer than just manually arranging the minority of
rows which need to be sorted by the real word. Thanksk for your response.

"NewsNet" wrote:

Create a helper column that contains the real word, then sort that.
You could populate this with a VLOOKUP of desired value. If the VLOOKUP
errors (no real word applicable) use the word in the column.

NickHK


"sinkhole" ...
Thanks, but the list is so long, that I'd like to institute a sorting
order
that would take care of the post process automatically rather than me
doing
it all manually.

Thanks for your reply.

"crazybass2" wrote:

Sink,

I think I'd do a post process. ie. Do the sort, then search for words
like
Bordeaux and move it under France. I'm assuming you'd have a list of the
words that should be 'custom sorted.'

Mike

"sinkhole" wrote:

Is it possible to A-Z sort a list, and instruct certain cells to be
sorted by
a different word than is entered into that cell? For example, sort the
cell
containing "Bordeaux" as if it were the word"France" so that Bordeaux
will
always be grouped with the cells containing "France."

And/or, in a scenerio with two columns and many rows is it possible to
A-Z
sort by the first column, except where the word "see" appears in a cell
in
the second column Excel uses the word that follows "see" as the term by
which
that row is sorted. For example, the word in the first cell is
"Bordeaux"
and in the second cell is "see France," rather than sorting this with
the
B's, it is grouped with all rows that contain "France" in the first
column.

Thanks for any help!

HM

Mike,

Thanks a lot for your response. I must admit that I am not familiar with
programming, but I will give this a shot. In fact, literally, I don't know
how to apply what you've written, ie. where to enter the code, and if I copy
it exactly or what, but I will fool around with it tomorrow.

Thanks again for your interest,
HM

"crazybass2" wrote:

Sinkhole,

I went back and read your "And/or" from your first post. The following code
can work with this.


Option Explicit
Dim MyRange As Range

Sub SpecialSort()
Dim cell As Range
Application.ScreenUpdating = False
MyRange.Offset(0, 1).Insert
For Each cell In MyRange
If Left(cell.Offset(0, 2), 4) = "see " Then
cell.Offset(0, 1) = Right(cell.Offset(0, 2), Len(cell.Offset(0, 2)) - 4)
& "'"
Else
cell.Offset(0, 1) = cell
End If
Next cell
Range(MyRange, MyRange.Offset(0, 2)).Sort Key1:=Range("B1"),
Order1:=xlAscending, Key2:=Range("A1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
MyRange.Offset(0, 1).Delete
Application.ScreenUpdating = True
End Sub


MyRange should be the range of the list (this code assumes it is one
continuous column of data). The column to the immediate right of MyRange
should have the "see France" notes. The code inserts a column and if there
is a "see " value the value is inserted into this column with an ' after it.
Otherwise the word in the MyRange column is inserts. The ' is added to the
"see " text (ie France') so France is sorted above all the words that have
"see France" in the second column. This is ONLY in the code and is not
visible to the user. After the sort, the inserted column is deleted. NOTE:
This will work as long as you are not using all 256 columns. You will need
to modify the sort command to include ALL of the columns you want sorted with
MyRange. NOTE: You will need to add one column to the right. For instance,
if your list of words is in column C and you want the data in columns B
through F sorted with the words you will need to change the
"Range(MyRange,MyRange.Offest(0,2))" to
"Range(MyRange.Offest(0,-1),MyRange.Offest(0,4))" Likewise, you need to
change Key1 and Key2 to the column to the right of MyRange and the column of
MyRange for the sort.

Best of luck. Let me know if you have any questions or if you don't
understand my explanation.

Mike

"sinkhole" wrote:

Thanks, the issue is that the list is so long that to create a helper colomn
would take as long or longer than just manually arranging the minority of
rows which need to be sorted by the real word. Thanksk for your response.

"NewsNet" wrote:

Create a helper column that contains the real word, then sort that.
You could populate this with a VLOOKUP of desired value. If the VLOOKUP
errors (no real word applicable) use the word in the column.

NickHK


"sinkhole" ...
Thanks, but the list is so long, that I'd like to institute a sorting
order
that would take care of the post process automatically rather than me
doing
it all manually.

Thanks for your reply.

"crazybass2" wrote:

Sink,

I think I'd do a post process. ie. Do the sort, then search for words
like
Bordeaux and move it under France. I'm assuming you'd have a list of the
words that should be 'custom sorted.'

Mike

"sinkhole" wrote:

Is it possible to A-Z sort a list, and instruct certain cells to be
sorted by
a different word than is entered into that cell? For example, sort the
cell
containing "Bordeaux" as if it were the word"France" so that Bordeaux
will
always be grouped with the cells containing "France."

And/or, in a scenerio with two columns and many rows is it possible to
A-Z
sort by the first column, except where the word "see" appears in a cell
in
the second column Excel uses the word that follows "see" as the term by
which
that row is sorted. For example, the word in the first cell is
"Bordeaux"
and in the second cell is "see France," rather than sorting this with
the
B's, it is grouped with all rows that contain "France" in the first
column.

Thanks for any help!

HM

Sinkhole,

OK, I can help you out.

Let say your list of words are in column B on sheet1. Let's also assume
your "see France" references are in column C on the same sheet. What you
want to do is right click the Sheet1 tab. In the popup menu select "View
Code." Doing this will open the VBA editor and you will be viewing the code
for Sheet1 module (it should be blank). Copy and paste the following code
into that window.

Option Explicit
Dim MyRange As Range
Sub SpecialSort()
Dim cell As Range
Application.ScreenUpdating = False
Set MyRange = Range("B:B") '****** Change
MyRange.Offset(0, 1).Insert
For Each cell In MyRange
If Left(cell.Offset(0, 2), 4) = "see " Then
cell.Offset(0, 1) = Right(cell.Offset(0, 2), _
Len(cell.Offset(0, 2)) - 4) & "'"
Else
cell.Offset(0, 1) = cell
End If
Next cell
Range(MyRange, MyRange.Offset(0, 2)).Sort _
Key1:=Range("C1"), _
Order1:=xlAscending, _
Key2:=Range("B1"), _
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
MyRange.Offset(0, 1).Delete
Application.ScreenUpdating = True
End Sub

Now click back over to Sheet1 in Excel and select Tools-Macro-Macros...
You should now see the macro you copied listed as Sheet1.SpecialSort. Select
that macro and click Run. The screen will pause, the cursor will turn to
hour glass and then you should see the sort completed. NOTE: If this is a
long list (1000+ cells) it may take some time to complete.

THINKS THAT MAY NEED TO CHANGE:
If your list...
1.) ...is on a different sheet
Instead of inserting the code into sheet1 you insert (right click sheet tab
and select View Code) it into the sheet the data is on.
2.) ...of words is in a different column you will need to change MyRange and
the Keys.
If words are in column A and references in column B change line....
6 to "Set MyRange = Range ("A:A")"
17 to "Key1:=Range("B1")"
19 to "Key2:=Range("A1")"


If you are still not clear what to do let me know the following and I'll mod
the code to what you need.

1.) What sheet is the list on?
2.) What column is the word list in?
3.) What column is the reference list in?

Mike

"sinkhole" wrote:

Mike,

Thanks a lot for your response. I must admit that I am not familiar with
programming, but I will give this a shot. In fact, literally, I don't know
how to apply what you've written, ie. where to enter the code, and if I copy
it exactly or what, but I will fool around with it tomorrow.

Thanks again for your interest,
HM

"crazybass2" wrote:

Sinkhole,

I went back and read your "And/or" from your first post. The following code
can work with this.


Option Explicit
Dim MyRange As Range

Sub SpecialSort()
Dim cell As Range
Application.ScreenUpdating = False
MyRange.Offset(0, 1).Insert
For Each cell In MyRange
If Left(cell.Offset(0, 2), 4) = "see " Then
cell.Offset(0, 1) = Right(cell.Offset(0, 2), Len(cell.Offset(0, 2)) - 4)
& "'"
Else
cell.Offset(0, 1) = cell
End If
Next cell
Range(MyRange, MyRange.Offset(0, 2)).Sort Key1:=Range("B1"),
Order1:=xlAscending, Key2:=Range("A1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
MyRange.Offset(0, 1).Delete
Application.ScreenUpdating = True
End Sub


MyRange should be the range of the list (this code assumes it is one
continuous column of data). The column to the immediate right of MyRange
should have the "see France" notes. The code inserts a column and if there
is a "see " value the value is inserted into this column with an ' after it.
Otherwise the word in the MyRange column is inserts. The ' is added to the
"see " text (ie France') so France is sorted above all the words that have
"see France" in the second column. This is ONLY in the code and is not
visible to the user. After the sort, the inserted column is deleted. NOTE:
This will work as long as you are not using all 256 columns. You will need
to modify the sort command to include ALL of the columns you want sorted with
MyRange. NOTE: You will need to add one column to the right. For instance,
if your list of words is in column C and you want the data in columns B
through F sorted with the words you will need to change the
"Range(MyRange,MyRange.Offest(0,2))" to
"Range(MyRange.Offest(0,-1),MyRange.Offest(0,4))" Likewise, you need to
change Key1 and Key2 to the column to the right of MyRange and the column of
MyRange for the sort.

Best of luck. Let me know if you have any questions or if you don't
understand my explanation.

Mike

"sinkhole" wrote:

Thanks, the issue is that the list is so long that to create a helper colomn
would take as long or longer than just manually arranging the minority of
rows which need to be sorted by the real word. Thanksk for your response.

"NewsNet" wrote:

Create a helper column that contains the real word, then sort that.
You could populate this with a VLOOKUP of desired value. If the VLOOKUP
errors (no real word applicable) use the word in the column.

NickHK


"sinkhole" ...
Thanks, but the list is so long, that I'd like to institute a sorting
order
that would take care of the post process automatically rather than me
doing
it all manually.

Thanks for your reply.

"crazybass2" wrote:

Sink,

I think I'd do a post process. ie. Do the sort, then search for words
like
Bordeaux and move it under France. I'm assuming you'd have a list of the
words that should be 'custom sorted.'

Mike

"sinkhole" wrote:

Is it possible to A-Z sort a list, and instruct certain cells to be
sorted by
a different word than is entered into that cell? For example, sort the
cell
containing "Bordeaux" as if it were the word"France" so that Bordeaux
will
always be grouped with the cells containing "France."

And/or, in a scenerio with two columns and many rows is it possible to
A-Z
sort by the first column, except where the word "see" appears in a cell
in
the second column Excel uses the word that follows "see" as the term by
which
that row is sorted. For example, the word in the first cell is
"Bordeaux"
and in the second cell is "see France," rather than sorting this with
the
B's, it is grouped with all rows that contain "France" in the first
column.

Thanks for any help!

HM