sort data into ranges like in the example below:
1,2,3,4,5,6,10,11,12,15,19,21,27,28,29,30 [Each value is in a cell vertically]
into
1 to 6
10 to 12
15
19
21
27
28 to 30
provide the vba code for this ?
I currently have two tables in one worksheet showing the sales of different region.
The problem is, when I sort the data in the table (I can't used the named ranges as it should exclude the first row which is the header), is there a way I can make it dynamic too??
**Attached, please find the example spreadsheet, I have only written the code for the first table.
I have been asked to create an attendance worksheet where employee names and data are entered on a "main" worksheet and hours are entered on monthly worksheets. The names on the monthly worksheets are referenced from the main worksheet. Therefore, if I add a name and do a sort, the names on all pages will move, but the data will not. I imagine I will need an ID column to help sort. How do I make a macro to do the sort?
View 2 Replies View RelatedOnce again, I'm seeking some further assistance to an issue that I have yet to resolve.
I have several rows of data which I'd like to be able to sort by the info in column "A".
The data rows are not all consecutive. The range for which the data resides are:
Range("A25:A73,A85:A105,A145:A193"), When I sort I want my data to stay within these limits/ranges.
Sub DynaSort()
Dim wsSheet As Worksheet
iRow = ActiveSheet.Columns("A").End(xlDown).Row
For Each wsSheet In Worksheets
Select Case wsSheet.CodeName
Case "Sheet2", "Sheet3", "Sheet4"
wsSheet.sort.SortFields.Clear
Range("A3:I" & iRow).Select
wsSheet.sort.SortFields.Add Key:=Range("F2:F" & iRow) _
, SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
wsSheet.sort.SortFields.Add Key:=Range _
("H2:H" & iRow), SortOn:=xlSortOnValues, order:=xlDescending, DataOption:= _ ...................
The problem that I has is that I cannot put focus on a cell after the sort. Xl keeps the columns selected and then when I'm trying to put in the next data Excel selects all the rows in Sheet1 also. I know how to get rid of it and continue, the users on the other hand are not that experienced with excel. fun thing, even thou the, Range.value is inside the IF it putt "pucko" in sheet1. I have a code that copies the data and then put some several functions in each sheet, after that I call the sort routine.
I've created this fantasy football programe to calculate all the scores from all matches in this years European Championships.
Basically everybody ranks each team from 1 to 16, giving 16 points to the country you think will win the european championship, 15 points to the runner up, and so on down to the country, you think is the weakest on just 1 point.
Everytime I try and change one of the values in the 'Results' tab I get an "Compile Error/Syntax Error".
What this macro should do is sort all the data in the "League Table" tab into a highscore table list.
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("b1"). CurrentRegion.Sort _
Key1:=Range("h2"), Order1:=xldescending,
Key2:=Range("c2"), Order2:=xldescending,
key3:=Range("e2"), order3:=x1descending,
Header:=xlYes
Application.EnableEvents = True
End Sub
can anyone modify below macro to sort this data:
before sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-6-5-99-2-1-99-4-3
3-99-7-8-99-5-6-99-8-9
4-99-8-7-99-6-5-99-9-8
etc
after sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-5-6-99-1-2-99-3-4
3-99-7-8-99-5-6-99-8-9
4-99-7-8-99-5-6-99-8-9
etc
Sub SortNoncontiguousRanges()
Dim rRange As Range
Dim lArea As Long
'10 rows in columns B and C
'10 rows in columns E and F
'10 rows in columns H and I
Set rRange = Range("B1:C10,E1:F10,H1:I10")
With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
.Sort Key1:=.Cells(1, 1), _
Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
End With
Next lArea
End With
End Sub
I'm trying to adapt this macro (without success) to sort a number of ranges rather than just one:
Dim rng As Range: Set rng = Range("B11:F45")
With rng
.Sort Key1:=Range("C11"), Order1:=xlAscending, Header:=xlGuess
End With
Set rng = Nothing
End Sub
I want to ADD some further parameters
to set Rng H11:L45 and Sort Column I11:I45
to set Rng N11:R45 and Sort Column O11:045
There's many more but I've tried to add in ranges but it won't work.
name data1 data2 total
chris a b 100
a a 100
a a 100
steve b b 90
b a 90
b a 91
jane b a 89
a a 90
a a 92
its hard to line things up in here..... anyway... each name has two more lines of data associated with it. i want to take the 4th column last line for each and sort from greatest to smallest and rearrange all the data but keeping all three lines associated with each person together. one added complication. the number of ppl in this will vary although i can do a count of every three lines and tell exactly how many there will be. so for this purpose we can just keep it at three.
in this case, all three lines associated with jane should be moved up above the three lines of steve but stay below the three lines of chris. and the three lines for each person stay in the same order they are now
I need a listbox or combobox in an excel form whos list is created from 2 ranges. for example first name is range a2:a500 second name is rangne b2:b500. the list box should list both coloums in a single list and return the row number. better still if it does not list blank ranges. i have never used a list or combo box before so please explain in terms a simplton can undrstand
View 7 Replies View RelatedI havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Sub SortMeetings()
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer
zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
End If
Next yCTR
Next iCTR
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies View RelatedI have an excel WS1 set up as DB; I want to keep this sheet for data revision. WS2,3, 4, & 5 will be data that is filtered and sorted, using WS1 as source so I want to auto copy the WS1 data. Can I just auto copy WS1 (how do I do that?) then filter and sort in each WS?
View 3 Replies View RelatedI am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
In the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc.
View 6 Replies View RelatedI have Sheet1 "MASTER" and Sheet2 "Area1" and Sheet3 "Area2" etc...
My MASTER sheet has a list of employee names and the areas they work in. I have employees working in different areas, and I want to pull a list of employee names from the MASTER working in Area1 (sorted on the MASTER sheet) to column A on Sheet2, then pull a list of employee names working in Area2 to column A on Sheet3, etc...
I want it to do this in such a way that if I add an employee to Area1 on the MASTER data, it will populate that employee in the Area1 Sheet.
So basically, I'm looking at one column on the MASTER sheet to see if the area matches. Then looking at another column on the MASTER sheet to get the name. Then taking that name and transposing it to a new sheet corresponding to the area they work in.
I've attached a sample sheet. I want Column A in the Area1 sheet to reflect all names that show up on the Area1 LIST on the MASTER sheet, and nothing else. I'm using VLOOKUP to pull the rest of the data from the MASTER table.
There is a new sample workbook up now. Couldn't update it sooner due to site outage. I've removed irrelevant data to improve readability and focus on what I'm trying to achieve. Again, the main issue is scraping column E from the Master, and populating a list of all employees who match certain values in Column E on the Master in Column A of the other tabs.
I would like to be able to sort the data in this one cell alphabetically without retyping the data .ie use a sort function but only for a cell.
For Example cell A1 contains Chris Brown Andy
No commas there. Names are seperated only by spaces.
What function should I use in order to get Andy Brown Chris ?
In the attached version the "Print Version" tab sorts the values entered into the "Log Entries" tab. When entries are deleted from the "Log Entries" tab they are converted into 'blank' entries and moved to the end in the "Print Version" tab.
However, in earlier versions the "IFERROR" does not appear to work, therefore any blank entries are shown as "#NUM!", which is kind of annoying. If there any way around this? The forumla is shown below. =IFERROR(SMALL('Log Entries'!$A$1:$E$47,ROW('Log Entries'!A1)+COLUMNS('Log Entries'!$A$1:A1)*46-46),"")
to use a Macro in order to batch process a set of data that will have consistent columns, but may have additional rows.
I have attached the file, three tabs.
1st - Sch of Inv(2): The intended goal
The data is sorted first by Georgraphic Location (A), then Strategy (C), then Substrategy (E). Column F should be the same formula that is currently there, but updated for column I once the information is pulled (I""/$F$29*100). The *100 is to only display the % symbol once. If there's a better way to calculate percentages and only show the symbol on the first one, that would also help. Column I is the place to calculate the data from Tab 2. Column K is your check figures, which are within $5 (due to a miskey probably, not important). Cell F29 will be a manually hard-coded number on each sheet (which would be entered before the Macro is initiated).
2nd - Cost Query: The data source sheet
6 columns, 5 have relevant data. Manager shortname is the 'primary key', but won't need to be displayed anywhere. Region-Strategy Code-Substrategy Name correspond to (A), (C) and (E) on Tab 1. The goal is to sum by each of these factors, then display the one sum on Tab 1. The pull will include column F, but it has no actual value. I'd prefer to be able to just keep it on Tab 2.
3rd - Pivot of Cost Query: The step inbetween?
Here is the proof that the data can be easily pivoted to provide all of the values, but I'm having trouble figuring out how to quickly move those values into the predetermined format of Tab 1.
Questions:
Is there something simple that I'm missing to move the data from the pivot into the predetermined format?
How can I use a macro to take the data on Tab 2, move it into a pivot on Tab 3, then move that data to the format (column I) in Tab 1? Is this process easier without the pivot step in the middle?
edit the below code? Having problem with the underlined portion. It is always showing the following error message - "Run-time error '1004' -Application-defined or object-defined error".
Code:
Sub Macro1()
'
' Macro1 Macro
[Code]....
I need to be able to give a grade A, B, C or a D by using a range of scores in an IF function for a markbook as part of my IT coursework.
I have had several failed attempts, and this is one of my many formulas that has failed:
=IF(O6<45,"D",IF(O6=$46:$60,"C",IF(O6<$61:$74,"B",IF(O6>75,"A"))))
I'm trying to create a user request form. In one section of the form, the client will enter in a list of "Allowed Roles". These are roles that they have rec'd approval to use - these roles will span multiple applications. In a tab I've named Metadata, I've got defined ranges indicating the valid roles for particular applications. Want I'm trying to do is attempt to define a cell in the user form for one application that, using data validation, will present them with a drop down list of valid roles to choose. The stumbling block I'm encountering is that I need it to validate against "Allowed Roles" and the valid roles for that app.
For instance:
Allowed Roles range name could contain:
B1 B2 C1 C2 P2 P3 D1 D3 D5 M1 O1
Payroll Roles:
C1 C2 C3 C4 C5 M1 O1
The Payroll Roles (on the metadata tab) are static, not user alterable in any way, and in fact Metadata will be hidden. Allowed Roles will be entered into the form by the client themselves.
Can I use data validation to create a drop down to only allow the user to pick a value that appears in both ranges? So they couldn't select C1 (since it's an Allowed Role, and a Payroll Role), but not C3 (not an allowed roled), or D3 (not a Payroll role).
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
ex) 1.751
2.52
3.753
418
417
416
Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.
Edit: Not sure what happened but file was NOT understandable before. It should be correct now.
Im using a pivot with 2 rows entrees and 1 column entree. I would like to sort my data from the biggest value to the smallest. It works for the first group, but when i sort the second the first change aswell and mess with my data.
View 1 Replies View RelatedI need to sort a database. In the pic you will see what i have and what i need to do.
example.jpg
Excel file. The file has two tabs: 'Input' and 'Master'. The 'Input' tab is for users to input any new records, and the 'Master' tab is to retrieve data from the 'Input' tab. Given that some users may want to insert a row in between (rather than add at the bottom).
I used arrays in the 'Master' tab such as:
={IF(ISERROR(1/Input!E2:E250),"",Input!E2:E250)}
Now there came the problem: in the 'Master' tab, the data cannot be sorted when the arrays are used. Otherwise, there is a warning message: You cannot change part of an array.
I've found that sorting a column next to one which has drop down boxes set up in it, does not take the options for the drop down boxes (data validation ) with them when being re-arranged. Is there a way to make this possible, or an alternative format I should be using? see example:
If item 4 is the only one available from supplier 3, (As shown by the drop down box options) how do I sort by say, department, and still have the correct options on hand? Sorting by department, the items move, but the data validation info stays where it is, leaving the supplier 3 option for item 1, which is not correct.
Is there a simple method to bulk data sort?
Explanation:
I run a workbook with worksheets in calendar months (12)
These consist of names in alphabetical order.
If a new name is added to or deleted from all the sheets (I've cracked how to do this) how can I data sort them without doing it sheet by sheet?
How do I sort chronologically by date?
View 12 Replies View Related
