Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by color row
Hi,
I have an excel list where some rows or fields are highlighted in Red. How do I sort and group all the rows highlighted in Red together ? For example: A 1 SKE 2 SDF 3 SOK 4 UEE 5 EIO 6 LKE 7 POE SKE, EIO, UEE fields are highlight in Red. Sort in a way that SKE, EIO, UEE will display in top 3 row. Thanks. On the other hand, how do we sort if the text is highlighted in BOLD. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by color row
The easiest way to do it is to add an extra column on the end and put the
following in it =if(or(A1="SKE",A1="EIO",A1="UEE"),1,0) copy it down and then sort on column b and then a. you can then use autofile to select those three items. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jeff" wrote: Hi, I have an excel list where some rows or fields are highlighted in Red. How do I sort and group all the rows highlighted in Red together ? For example: A 1 SKE 2 SDF 3 SOK 4 UEE 5 EIO 6 LKE 7 POE SKE, EIO, UEE fields are highlight in Red. Sort in a way that SKE, EIO, UEE will display in top 3 row. Thanks. On the other hand, how do we sort if the text is highlighted in BOLD. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by color row
Martin,
I think you misunderstand my requirement. It should be sort by colour. All the rows highlighted in colours should be group together. Thanks. Jeff "Martin Fishlock" wrote: The easiest way to do it is to add an extra column on the end and put the following in it =if(or(A1="SKE",A1="EIO",A1="UEE"),1,0) copy it down and then sort on column b and then a. you can then use autofile to select those three items. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jeff" wrote: Hi, I have an excel list where some rows or fields are highlighted in Red. How do I sort and group all the rows highlighted in Red together ? For example: A 1 SKE 2 SDF 3 SOK 4 UEE 5 EIO 6 LKE 7 POE SKE, EIO, UEE fields are highlight in Red. Sort in a way that SKE, EIO, UEE will display in top 3 row. Thanks. On the other hand, how do we sort if the text is highlighted in BOLD. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by color row
You cannot normally sort by cell formatting like colours or bold or italic.
What you did mention was that some rows namely SKE, EIO, UEE were the one in red so in the new column put a key to say that it was one of the items and then sort on the key and then the original items. Please explain more. As the only other way is to use a macro to sort on the colours but you need to also need to use a working column. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jeff" wrote: Martin, I think you misunderstand my requirement. It should be sort by colour. All the rows highlighted in colours should be group together. Thanks. Jeff "Martin Fishlock" wrote: The easiest way to do it is to add an extra column on the end and put the following in it =if(or(A1="SKE",A1="EIO",A1="UEE"),1,0) copy it down and then sort on column b and then a. you can then use autofile to select those three items. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jeff" wrote: Hi, I have an excel list where some rows or fields are highlighted in Red. How do I sort and group all the rows highlighted in Red together ? For example: A 1 SKE 2 SDF 3 SOK 4 UEE 5 EIO 6 LKE 7 POE SKE, EIO, UEE fields are highlight in Red. Sort in a way that SKE, EIO, UEE will display in top 3 row. Thanks. On the other hand, how do we sort if the text is highlighted in BOLD. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by color row
Martin,
I could send you an excel example and also how it should look like after sorting to your e-mail if you don't mind. Thanks..Jeff "Martin Fishlock" wrote: You cannot normally sort by cell formatting like colours or bold or italic. What you did mention was that some rows namely SKE, EIO, UEE were the one in red so in the new column put a key to say that it was one of the items and then sort on the key and then the original items. Please explain more. As the only other way is to use a macro to sort on the colours but you need to also need to use a working column. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jeff" wrote: Martin, I think you misunderstand my requirement. It should be sort by colour. All the rows highlighted in colours should be group together. Thanks. Jeff "Martin Fishlock" wrote: The easiest way to do it is to add an extra column on the end and put the following in it =if(or(A1="SKE",A1="EIO",A1="UEE"),1,0) copy it down and then sort on column b and then a. you can then use autofile to select those three items. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jeff" wrote: Hi, I have an excel list where some rows or fields are highlighted in Red. How do I sort and group all the rows highlighted in Red together ? For example: A 1 SKE 2 SDF 3 SOK 4 UEE 5 EIO 6 LKE 7 POE SKE, EIO, UEE fields are highlight in Red. Sort in a way that SKE, EIO, UEE will display in top 3 row. Thanks. On the other hand, how do we sort if the text is highlighted in BOLD. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by color row
No problems
martin_fishlock at yahoo.co.uk -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jeff" wrote: Martin, I could send you an excel example and also how it should look like after sorting to your e-mail if you don't mind. Thanks..Jeff "Martin Fishlock" wrote: You cannot normally sort by cell formatting like colours or bold or italic. What you did mention was that some rows namely SKE, EIO, UEE were the one in red so in the new column put a key to say that it was one of the items and then sort on the key and then the original items. Please explain more. As the only other way is to use a macro to sort on the colours but you need to also need to use a working column. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jeff" wrote: Martin, I think you misunderstand my requirement. It should be sort by colour. All the rows highlighted in colours should be group together. Thanks. Jeff "Martin Fishlock" wrote: The easiest way to do it is to add an extra column on the end and put the following in it =if(or(A1="SKE",A1="EIO",A1="UEE"),1,0) copy it down and then sort on column b and then a. you can then use autofile to select those three items. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jeff" wrote: Hi, I have an excel list where some rows or fields are highlighted in Red. How do I sort and group all the rows highlighted in Red together ? For example: A 1 SKE 2 SDF 3 SOK 4 UEE 5 EIO 6 LKE 7 POE SKE, EIO, UEE fields are highlight in Red. Sort in a way that SKE, EIO, UEE will display in top 3 row. Thanks. On the other hand, how do we sort if the text is highlighted in BOLD. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by color row
For interest purposes, this is the code that I sent.
Sub DoPatternSort() Dim rRange As Range Dim lRowStart As Long, lRowEnd As Long, lRow As Long On Error GoTo ErrorEnd Application.ScreenUpdating = False Set rRange = Selection.CurrentRegion lRowStart = rRange.Row + 1 lRowEnd = rRange.Rows.Count rRange.Cells(1, 2).EntireColumn.Insert For lRow = lRowStart To lRowEnd Step 1 rRange.Cells(lRow, 2) = rRange.Cells(lRow, 1).Interior.ColorIndex Next lRow rRange.Sort Key1:=rRange.Cells(2, 2), Order1:=xlDescending, _ Key2:=rRange.Cells(2, 3), Order2:=xlAscending, _ Key3:=rRange.Cells(2, 1), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal rRange.Cells(1, 2).EntireColumn.Delete ErrorEnd: Application.ScreenUpdating = True End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Martin Fishlock" wrote: No problems martin_fishlock at yahoo.co.uk -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jeff" wrote: Martin, I could send you an excel example and also how it should look like after sorting to your e-mail if you don't mind. Thanks..Jeff "Martin Fishlock" wrote: You cannot normally sort by cell formatting like colours or bold or italic. What you did mention was that some rows namely SKE, EIO, UEE were the one in red so in the new column put a key to say that it was one of the items and then sort on the key and then the original items. Please explain more. As the only other way is to use a macro to sort on the colours but you need to also need to use a working column. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jeff" wrote: Martin, I think you misunderstand my requirement. It should be sort by colour. All the rows highlighted in colours should be group together. Thanks. Jeff "Martin Fishlock" wrote: The easiest way to do it is to add an extra column on the end and put the following in it =if(or(A1="SKE",A1="EIO",A1="UEE"),1,0) copy it down and then sort on column b and then a. you can then use autofile to select those three items. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jeff" wrote: Hi, I have an excel list where some rows or fields are highlighted in Red. How do I sort and group all the rows highlighted in Red together ? For example: A 1 SKE 2 SDF 3 SOK 4 UEE 5 EIO 6 LKE 7 POE SKE, EIO, UEE fields are highlight in Red. Sort in a way that SKE, EIO, UEE will display in top 3 row. Thanks. On the other hand, how do we sort if the text is highlighted in BOLD. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accidental Sort | Excel Discussion (Misc queries) | |||
Sort by column problems | Excel Worksheet Functions | |||
How to sort on data field | Excel Discussion (Misc queries) | |||
how to sort anything that looks like a number as a number | Excel Discussion (Misc queries) | |||
Select rows and sort based on type | Excel Discussion (Misc queries) |