Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data to show in a series 1,2,3
We have a large worksheet used by many users that may have various filters on
various columns at any given time. Column A contains data 0 thru 8. I need code to add to a macro that will sort the worksheet by the VISIBLE data in Col A as follows: 0 must always be at the top, then the first visible row with a 1 would be next, then the first visible row with a 2, then the next row a 1, then a 2, etc...the remaining rows with 3-8 may just show in ascending order all 3's, all 4's etc...so that column A will look like 0,0,0,1,2,1,2,1,2,1,2,3,3,3,3,4,4,4,....etc This would be a great help. -- Holly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data to show in a series 1,2,3
Holly,
Which version of Excel are you using? I'm using Office 2007 and it allows you to apply a custom sort to data that has been filtered. As a quick, general thought, you could copy the visible data to another worksheet and then sort the copied data. If you customize the toolbar (or QAT), you can find a control called "Select Visible Cells" that will allow you to select only visible data. Let me know if this helps. Best, Matthew Herbert "HOLLY" wrote: We have a large worksheet used by many users that may have various filters on various columns at any given time. Column A contains data 0 thru 8. I need code to add to a macro that will sort the worksheet by the VISIBLE data in Col A as follows: 0 must always be at the top, then the first visible row with a 1 would be next, then the first visible row with a 2, then the next row a 1, then a 2, etc...the remaining rows with 3-8 may just show in ascending order all 3's, all 4's etc...so that column A will look like 0,0,0,1,2,1,2,1,2,1,2,3,3,3,3,4,4,4,....etc This would be a great help. -- Holly |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data to show in a series 1,2,3
Hi,
I am using excel 2003 to write the code but many of our users have been upgraded to 2007. I can look at the option you mention on an 07 machine, and as long as that sort can be preset to automatically appear to other users, then it would work. The reason I wanted code is because most of our users are not computer savvy at all, zilch. I have already built a macro that refreshes the entire workbook which pulls data from several sources etc. The main worksheet that I need this code for is huge. Copy and paste just is not an option. -- Holly "Matthew Herbert" wrote: Holly, Which version of Excel are you using? I'm using Office 2007 and it allows you to apply a custom sort to data that has been filtered. As a quick, general thought, you could copy the visible data to another worksheet and then sort the copied data. If you customize the toolbar (or QAT), you can find a control called "Select Visible Cells" that will allow you to select only visible data. Let me know if this helps. Best, Matthew Herbert "HOLLY" wrote: We have a large worksheet used by many users that may have various filters on various columns at any given time. Column A contains data 0 thru 8. I need code to add to a macro that will sort the worksheet by the VISIBLE data in Col A as follows: 0 must always be at the top, then the first visible row with a 1 would be next, then the first visible row with a 2, then the next row a 1, then a 2, etc...the remaining rows with 3-8 may just show in ascending order all 3's, all 4's etc...so that column A will look like 0,0,0,1,2,1,2,1,2,1,2,3,3,3,3,4,4,4,....etc This would be a great help. -- Holly |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data to show in a series 1,2,3
I attempted the 2007 custom sort function but I am working on a spreadsheet
with over 5000 rows. Does this mean I have to create a custom sort with 5000 entries? I am confused. The other aspect to this is I have header rows, actually have 3 rows of header data...which may affect how this function works. I think code is the answer....please help! -- Holly "HOLLY" wrote: Hi, I am using excel 2003 to write the code but many of our users have been upgraded to 2007. I can look at the option you mention on an 07 machine, and as long as that sort can be preset to automatically appear to other users, then it would work. The reason I wanted code is because most of our users are not computer savvy at all, zilch. I have already built a macro that refreshes the entire workbook which pulls data from several sources etc. The main worksheet that I need this code for is huge. Copy and paste just is not an option. -- Holly "Matthew Herbert" wrote: Holly, Which version of Excel are you using? I'm using Office 2007 and it allows you to apply a custom sort to data that has been filtered. As a quick, general thought, you could copy the visible data to another worksheet and then sort the copied data. If you customize the toolbar (or QAT), you can find a control called "Select Visible Cells" that will allow you to select only visible data. Let me know if this helps. Best, Matthew Herbert "HOLLY" wrote: We have a large worksheet used by many users that may have various filters on various columns at any given time. Column A contains data 0 thru 8. I need code to add to a macro that will sort the worksheet by the VISIBLE data in Col A as follows: 0 must always be at the top, then the first visible row with a 1 would be next, then the first visible row with a 2, then the next row a 1, then a 2, etc...the remaining rows with 3-8 may just show in ascending order all 3's, all 4's etc...so that column A will look like 0,0,0,1,2,1,2,1,2,1,2,3,3,3,3,4,4,4,....etc This would be a great help. -- Holly |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort data to show in a series 1,2,3
I'm not sure if this will do what you want. But, try this one. This
macro adds auxiliary column with a header, _Number_, for recovering original order. I'm using Excel 2003. Sub TestMacro() Dim startrow As Long, lstrow As Long, auxcol As Long Dim i As Long, k As Long, l As Long Dim TarFst As Range, TarA As Range, rng As Range Application.ScreenUpdating = False startrow = 1 lstrow = startrow Do While (Cells(lstrow, "A") < "") lstrow = lstrow + 1 Loop lstrow = lstrow - 1 auxcol = Cells(startrow, Columns.Count).End(xlToLeft).Column If Cells(startrow, auxcol) = "_Number_" Then If Application.Max(Columns(auxcol)) < lstrow Then MsgBox "New data is added. First, show all data, and" _ & " sort by _Number_" & Chr(10) _ & "Second, clear all data in _Number_" _ & Chr(10) & "Then, start again" Exit Sub End If Else auxcol = auxcol + 1 Cells(startrow, auxcol) = "_Number_" For i = startrow + 1 To lstrow Cells(i, auxcol) = i Next End If Set TarFst = Range(Cells(startrow, "A"), Cells(lstrow, "A")) k = 1 l = 1 For Each rng In TarFst.SpecialCells(xlCellTypeVisible) Select Case rng.Value Case 0 rng = "0," & rng.Value Case 1 rng.Value = k & "," & rng.Value k = k + 1 Case 2 rng.Value = l & "," & rng.Value l = l + 1 Case Else rng.Value = "a," & rng.Value End Select Next Set TarA = Range(Cells(startrow, "A"), Cells(lstrow, auxcol)) TarA.Sort Key1:=Cells(startrow, "A"), Order1:=xlAscending, _ Header:=xlYes On Error Resume Next For Each rng In TarFst.SpecialCells(xlCellTypeVisible) rng = Split(rng.Value, ",")(1) Next End Sub Keiji HOLLY wrote: I attempted the 2007 custom sort function but I am working on a spreadsheet with over 5000 rows. Does this mean I have to create a custom sort with 5000 entries? I am confused. The other aspect to this is I have header rows, actually have 3 rows of header data...which may affect how this function works. I think code is the answer....please help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show series name on the chart - but not at every data point | Charts and Charting in Excel | |||
Show only specific Series in the data table | Charts and Charting in Excel | |||
How to show 5 data series on one chart | Charts and Charting in Excel | |||
Do not show a portion of the data series if the value is 0 | Charts and Charting in Excel | |||
how do i sort a worksheet data to show repetitve data and show | Excel Worksheet Functions |