Algorithm To Sort An Array Of Data In Vba
Jun 4, 2007
Im looking for an algorithm to sort an array of data, deleting multiple values whithin the array and shifting the values down the array to leave no gaps. the crude set of loops i am currently using is:
For f = 0 To 100
For g = f + 1 To 100
If LineArray(g) = LineArray(f) Then LineArray(g) = ""
Next g
Next f
which does work and serves my purpose but is messy as it leaves blank gaps in the array.
View 2 Replies
ADVERTISEMENT
Dec 2, 2009
I have this module that is supposed to look in column A and if it sees duplicates it looks in column B (with the date) and erases the entire row where the date is older in column B. I am attaching a sample to better illustrate it.
The highlighted in yellow rows are were I have duplicates and I want the row where the date is in red border to remain.
The module I have so far erases the newer records instead of the older.
This is the file:
Sample sorting.xls
Here is the
View 3 Replies
View Related
Jun 30, 2014
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.
View 3 Replies
View Related
Mar 1, 2014
I'm trying to sort 2D array not via worksheet, using VBA array. But I failed. excel file enclosed (Sorting by Return and xlAscending).
View 2 Replies
View Related
Sep 27, 2009
On from a solution I am using in the sheet which uses very well an Array Formula.
In the attached book I have a sheet with a bunch of tables called Site Table. In the sheet called Working Sheet I want to get the text Site Names and list them in Column A. Sorted would be nice not required but I can't think of way to avoid blanck rows without a manual sort. I tried many versions of Find without a solution as well.
View 5 Replies
View Related
Oct 20, 2009
I am trying sort a bunch of different ranges. So I am trying to use the same sort code but run an array of ranges through. This code is for only two ranges
CA3:CD200 and CF3:CI200. I want to pass the ranges as variables through the sort code but I cannot get it to work.
View 4 Replies
View Related
Jul 11, 2007
two parts:
1.
The following is the VBA version of what i've been using:
Range("A1:J1000").Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:= _
Range("E2"), Order2:=xlAscending, Key3:=Range("F2"), Order3:=xlDescending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
now arrays are bigger than 1000 rows>> even bigger than 65536.
Such an array is stored in "arrBIG" and dimed as (1 to 10, 1 to 100000). The goal is to sort it by the 4'th, then 5th then 6th values in the first dimension - the one that is 1 to 10 - i call this the column.
arrBIG.Sort Key1:=4, Order1:=xlAscending, Key2:= _
5, Order2:=xlAscending, Key3:=6, Order3:=xlDescending _.................
View 9 Replies
View Related
Aug 15, 2006
I would like to sort the items in an array.
View 2 Replies
View Related
Nov 18, 2006
I have a string (PreString) that gets its values from a procedure call (PreResult). The string consists of 4 values for each loop which I then split into the y array.
I then want to transpose the array to the worksheet for sorting.
I don't really get the transpose to work as I want to. I want every 4:th y to be printed on a new row (x). I don't really know how to use the Ubound function to get it right.
Should I make a 2 dimensional array instead ? If so, how is that made ?
Dim x As Integer, z As Integer
Dim var_Status As Integer
Dim var_Week As String
Dim var_HoursPerWeek As Integer
Dim PreString As String
i = var_StartWeekNr
j = var_RangeNumberOfWeeks
z = 0
x = 0
var_Status = 0
var_Week = ""
var_HoursPerWeek = 0
PreString = ""
View 8 Replies
View Related
Jan 28, 2009
This just a shot in the dark, but does anyone have a custom function that calculates the check digit for a cusip? http://en.wikipedia.org/wiki/CUSIP
The algorithm is listed above.
View 3 Replies
View Related
Sep 25, 2009
Have an excel table with following data:
- ID
- number of bottles
- number of bottle crates (there are 20 bottles in one one bottle crate)
201688194000bottles
20168819200crates
2016883812000bottles
20168838600crates
201688396400bottles
20168839320crates
201688809000bottles
20168880600bottles
20168880480crates...................
I need to write a macro which will do this operation for each ID:
(bottles/20)-crates = x
and if "x" is not 0 then write down the value of "x".
There are two points I would like to point out:
- One ID may contain 3 or more rows (see 20168880)
- The macro will work with hundreds IDs so the algorithm should be fast (but it is not necessary)
View 3 Replies
View Related
Dec 4, 2007
Anyone have an excel sheet that automatically generates secret santa selections?
Or explain an algorithm so I can implement it on excel?
View 9 Replies
View Related
Feb 5, 2008
I am trying to solve the nearest neighbour algorithm with vba
For example, I have a table as the following
0 1 2 3 4 5 1
10
5
15
20
2 10
8
9
7
3 5...........
View 9 Replies
View Related
Mar 4, 2010
I 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
View 9 Replies
View Related
Oct 1, 2008
I have a string that I need to sort.
Below code works beautifully but doesn't sort numbers, any advice to get this to sort numbers as well?
Function Alphabetize(ByVal sText As String) As String
Dim sWords() As String, sTemp As String
Dim i As Long, j As Long, n As Long
'-- clean up text
For i = 1 To Len(sText)
Select Case Mid$(sText, i, 1)
Case " ", "a" To "z", "A" To "Z"
Case Else: Mid$(sText, i, 1) = " "
End Select
Next
'-- remove leading and trailing spaces....................
View 9 Replies
View Related
Nov 12, 2006
I have a dynamic number of rows each with three colums of values. These varaibles I want to fill an array with but I don't know how.
Now I've just "concated" these three variables into a string and then I intend to split the string into rows by the third comma. (see below)
I think it's easier though to use an array and I really appreciate some assistance. Please tell me also the best way to sort the array. It will be sorted by var_Status which is an integer. (sort order: max positive to max minimum)
PreString = PreString & var_StartWeek & ", " & var_Status & ", " & var_Totalh & ", "
View 9 Replies
View Related
Jun 7, 2008
I have been trying to take a variant array that has 6 columns, output it to a new worksheet (although I would prefer to just sort the array but can't get that working - how I can do this please feel free), sort the worksheet by 2 different columns, and then move these values back into the original array. I think I have the dumping and sorting down but I can't figure out the putting back into the array part. Here is the code I have thus far. varRecords is the array I am dumping to the new worksheet.
Dim dumpSheet As Worksheet
Set dumpSheet = Sheets.Add
Range(Cells(1, 1), Cells(1, UBound(varRecords))) = varRecords
Range("A1:F" & numRows).Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range _
("A2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
View 5 Replies
View Related
Apr 8, 2008
I wonder could anyone help me with a problem i'm having, I have a table showing a basic RSA algorithm in excel, for example,
M 13 2197 19 8893887173 13 M
N 14 2744 5 788125 14 N
etc etc,
How the numbers are derived isn't important, what i want to do is create a visual basic form, to allow a user to input a letter, for example M in a text box, click a button, and then the 5 related digits are shown.
So the user inputs M into a text box clicks a button, then 13, 2197, 19, 8893887173 and 13 is output to 5 other text boxes.
View 9 Replies
View Related
May 30, 2006
Given a list of numbers and a list of 'allowable' operators, come up with a specific total. ("The numbers game" on countdown on BBC2.... ooooh Carol Voderman)
For example, given the numbers 1, 3, 4 and 5 with valid operators +, -, /, * come up with a solution equal to 15. All numbers must be used once only. Use operators as often as required. The specific example above was given to me and I personally think its not solvable (might be a practical joke). But I am wondering is it possible to prove this through assertions or develop an algorithm to solve such puzzles. I can get 14 and 16, but not 15....
View 8 Replies
View Related
May 18, 2009
How can I sort an array of data based on the selection from a drop-down menu? And can it be 'secondary' sorted with the result from a second drop-down (ascending or descending is unimportant)?
View 2 Replies
View Related
Feb 14, 2014
In the attached example file, I have two tabs: options and sets. The options tab is to be populated from a form. The sets tab is to draw data from the options tab to create teaching sets. I have managed to create an array formula that does this for me, but what I would like it to do is sort my resulting list alphabetically. For reasons that I won't go into, I need the data on the sets tab to remain in three columns: First Name, Surname and Form.
This is the array formula I am using at the moment: AliGW - Example.xlsx
View 14 Replies
View Related
Jul 7, 2009
i'm working on floyd algorithm macro for a project,
problem is, the spreadsheet matrix is huge, it's a 1043 x 1043 spreadsheet
And this is taking wayyy too long, i've been waiting for almost 2 days already..
here's the file :
http://ifile.it/6v2j39f
(You start the macro using "run" command in "input" sheet)
So is there any way to speed it up?
Or if anyone got a supercomputer, could you help me process this? :p
I'm thinking about changing the 10^5 value into infinite value, but i can't find a way to display infinite value..
And here's some reference for Floyd's algorithm,
http://ifile.it/12p5zle
I have tried to split the algorithm to make it process one iteration at a time, but i've been waiting like 2 hours with no result,
and that's for 1 iteration, can't imagine how long it takes for 1043 iterations
You can do this by deleting the.
View 14 Replies
View Related
Feb 17, 2012
I have a database of approximately 250000 email addresses and I would like to see if there is some way to use Excel to analyse the strings in order to identify potential 'non-unique' individuals.
For example, if I have the following email addresses:
12345@example.com
12346@example.com
12347@example.com
or
example1@example.com
example2@example.com
etc
Is there some way of extracting common text strings, lets say where there is a difference of 1 character / digit between strings (rather than alphabetising and searching manually)?
View 2 Replies
View Related
Aug 8, 2009
I want to display the path result from a Floyd Algorithm matrix output..
You can download the excel file here : http://ifile.it/lw4tgic
It consists of 1044 nodes, and we need to find the path between 2 nodes for ALL pairs..
Now, to find the path between 2 nodes, we need to :
1. Find the resulting cell of corresponding 2 nodes (y to x = z)
2. Update the path with that value (y z x)
3. For all the direct pairs in the current path (yz, zx), find the resulting cell
4. If destination (x) not the same with result (z) then repeat step 1 for that pairs.
5. repeat until x = z for all pairs.
Maybe it's better if i use an example....
we'll use T01 to E78 as an example
T01-E78 = if you look in the table,
y (vertical) = T01,
x (horizontal) = E78
z (result) = E77, so
T01-E78 = E77, so path = T01-E77-E78
check if result of T01-E77 = E77 (x = z?)...........................
View 10 Replies
View Related
May 30, 2014
I have two lists in different columns, which are defined ranges. I want to extract them to a unique list with an array formula and at the same time sort albafetically, without duplicates, like this:
List1
List2
Final List
Audi
Ford
[Code] ....
So far, I have this formula which is working in what concerns removing duplicates and exctract a unique list. But it is not ordering alfabeticaly.
{=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($D$1:D1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($D$1:D1, List2), 0))), "")}
View 2 Replies
View Related
Jul 11, 2007
On one sheet (KPI) I have either the values "x" or "" in the range A84:A89 to mark wheter to use the corresponding project in the range B84:B89. On the sheet X-ref I have the same project names in range T4:Y4 and a corresponding target value in T8:Y8
What I want is the sum (or average) of the marked-projects target-values. The result should end up in KPI!G31.
In other words I want
=sumif(A84:A89;"x";'X-ref'!T4:Y4)
but it doesn't work since the first range is an column-array and the second range is an row-array.
View 5 Replies
View Related
Oct 7, 2013
I 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 Related
Apr 16, 2013
I 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
View 1 Replies
View Related
May 22, 2012
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 Related
Feb 25, 2014
I 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.
View 2 Replies
View Related