Sorting Algorithm Doesn't Sort As Needed
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
ADVERTISEMENT
Sep 11, 2006
I have a column containing 365 entries, one for each day of the year. This I intend to send out to Army bands, to have them fill in their forecast of engagements. I do not want them to be able to amend the original diary, but to scroll to the bottom of the list, add a new entry with the applicable date and then be able to 'sort' back to chronological order. This would then allow a separate entry for every engagement to allow me to interrogate the spreadsheet. As I have protected the sheet, the 'sort' facility does not function even though when protecting I ticked the box to allow users to 'sort'.
View 2 Replies
View Related
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
View Related
Jan 13, 2014
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.
View 2 Replies
View Related
May 18, 2009
I have data being fed from an application where it's difficult to sort the rows into ascending order. So is there a way that VBA can sort them and produce a value according to the order?
For example, Column A contains 5 rows.
Row 1's value is 4
Row 2's value is 1
Row 3's value is 2
Row 4's value is 3
Row 5's value is 5
If I were to sort these into ascending order, I'd get 1, 2, 3, 4, 5 in Column A. But I can't sort the rows so I need code to tell me which is the lowest number (1), which is the second lowest number (2), etc.
The purpose of the code will be to put values in Column B according to what's found in Column A. For the the lowest number, 1, the value in the cell next to it in Column B would be "a". For number 2, it would be "b", etc.
View 9 Replies
View Related
Jun 28, 2013
I want to sort columns A,C:P and use column C as the column that I sort and the other rows will move with column C. I want column B to stay and not move with the sort. How can I do this.
View 2 Replies
View Related
Aug 29, 2006
Is it possible to keep the sort icon available on a worksheet which is protected? I have issued a spreadsheet to colleagues which contains formulas so I have protected it, but I have now been informed that they need to be able to sort the data according to a ref number.
I thought of using code (which I'm not very good at) and used some from another excel document, but couldn't get it to work...the code was ....
View 9 Replies
View Related
Jun 1, 2007
In the attached workbook, sorting the dates in column M results in absolutely nothing happening. The dates are formatted as dates (dd/mm/yyyy). The dates in column M are arrived at by adding a number of days (formatted as Number) to another date, the value of which was determined by an array formula. When I retype the actual date into another column and sort that, I get it sorted. Why does the other sort not work? BTW - I actually need to sort column M with column N.
View 2 Replies
View Related
Apr 11, 2007
I'm using the excel built in function to sort columns with my macro. However since my columns have 64,000 entries it takes a while for the computer to sort it.
View 14 Replies
View Related
Jan 30, 2008
In my example file are 4 columns. I placed auto filter to columns B and C. If column B sorted to ascending then this changes formulas in column D. I attached workbook also to understand my problem. If you try to sort column B to ascending you will see the problem in column D
View 4 Replies
View Related
Nov 25, 2013
I have two slicers that are in a hierarchy. These are attached to a Pivot table whose data source is an OLAP cube. Every Partner Parent is part of a Partner Group:
Partner Parents Slicers.png
My problem is that when I click SI Alliance in Partner Group, the Partner Parent slicer does not re-sort in any way. The corresponding selected Partner Parents are scattered throughout the alphabetical list. However, in a different document, I have slicers in a similar situation, except their Pivot table’s data source is a SQL Server database. When I click on a member of the higher up group (Accenture Global Client) the lower level group (Microsoft Account Name) sorts to show only the selected values at the top of the list:
This is with all selected - Accenture Slicers All.png
This is with just one Accenture Global Client selected. Note how the selected Microsoft Account Names have moved to the top of the list - Accenture Slicers Selected.png
How I can configure my Partner Parent/Partner Group slicers to behave like these Accenture/Microsoft slicers? Is there something that I can change in Excel or in the OLAP cube to make this happen? I have already tried right-clicking the slicer and going to Slicer Settings. The settings on the Parent/Partner Group slicers mimic those of the Accenture/Microsoft slicers exactly.
View 4 Replies
View Related
Jul 12, 2009
I am putting together multiple worksheets with dumped data that should sort themselves by the press of a button. Each entry has a 'code' and a value and they are sorted by the 'code'.
At the moment i have the first worksheet sorting correctly and i am trying to program the second worksheet to sort data into the existing worksheets if they exist or create a new worksheet if the data doesn't have it's own worksheet.
Here is the code i am working with.
View 12 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
Oct 24, 2011
Code below. I need it to NOT run if the sheet week2 doesn't exist. Currently it gives a runtime error '9' out of range. This is due tot he sheet not being present because sometimes it is not generated.
Code:
Sub RemoveColWeek2sheet()
Dim ColNo As Integer
Dim rng As Range
Set rng = ThisWorkbook.Sheets("Week2").UsedRange
[Code] .........
View 6 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
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
Jun 19, 2014
So I have two sets of data, I've attached a worksheet which has them both. The datasets have different intervals, but I need to drag "Colour" from Data2 into Data1 and associate it with the correct interval. Sometimes the intervals match up and sometimes they don't - which could be a problem, but as long as its within a small spread it's fine. The "Colour" column in Data1 is filled with my desired result, the source being Data2.
ManyToMany_Example.xlsx
View 3 Replies
View Related
Sep 13, 2008
way to take 18 different workbooks, each containing the same 5 worksheets, and create a TOC that reference the workbook name only.
For Example, I would like to have one workbook with the first worksheet as an index or a table of contents that lists the name of 18 workbooks. When I hover my cursor over the name of a particular workbook in the index or TOC, it will open that workbook where I can now view 5 worksheets of that workbook.
Each workbook will contain the same 5 worksheets.
I am using excel 2002
View 9 Replies
View Related
Oct 12, 2008
I'm trying to make a spreadsheet for work so when im not there they can just enter the day for example sunday and what they have and then it will show them what they need to prep
View 4 Replies
View Related
Apr 9, 2014
I have a big list with names, money spent and date. I need somehow with a formula to do:
Take all the money spent on some specific day from a specific person an put it into a field.. and this for every date of the week and for everyname... Im gonna upload a photo.
Name
Money
Date
Daniel
5
01.03.2014
[Code] ........
I need the list of the left to produce me list of the right somehow, at least just the sum of everything spent everyday.
View 7 Replies
View Related
Jul 3, 2014
I need identifying correct title and corresponding company name. For this I need a UDF which looks for first occurrence of title having event or marketing or meeting word in data and if found the remove other titles and company names already separated with "/".
Sample Data
Producer, Target BTC@Periscope/Events Manager, Recognition Events@Minneapolis Park & Recreation Board/Event Producer@Events by JLS
Required Result
Events Manager, Recognition Events@Minneapolis Park & Recreation Board
Sample Data 2
Sales Associate@Teavana/Event Assistant@City of Saint Paul
Required Result 2
Event Assistant@City of Saint Paul
Sample Data 3
Sales and Catering Manager@Bunker Hills Golf Course at Town & Country Caterers/Marketing Coordinator@Town & Country Caterers
Required Result 3
Marketing Coordinator@Town & Country Caterers
View 3 Replies
View Related
Jul 31, 2009
I am trying to get a MsgBox to appear if the cell value within a given range exceeds a certain number value in my case "8". The only issue I am experiencing is that the code seems to take into account any input from any cell, not just the range specified, and additionally since there are 7 columns in the range the MSgBox pops up 7 times, and I need it only once and if the cell value exceeds "8" only when input.
ideally i would like to do a named range as there as there is a block of data where this will need to repeat.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
With ActiveSheet
For Each cell In Range("J40:P40")
If cell.Value [A35].Value Then
MsgBox "Red Cell cannot exceed 8 hours per shift. Please correct this."
End If
Else
End If
Next
End With
End Sub
View 9 Replies
View Related
Jul 9, 2014
See the screen shot I have attached: ramp rate.jpg
Basically I have to trawl through tonnes of data every day and I would like an automatic way to do it for me. I am calculating the ramp up rate on an Autoclave oven so these figures are temperatures.
I have various ranges for each section of a cure cycle so I will use this one example.....
I need to calcuate the ramp up rate between 115°C and 140°C
I need to find the min and max between 115°c and 140°c then count the cells within the min and max, minus the min from the max then divide by how many cells there are inbetween
(MAX-MIN)/CELL COUNT BETWEEN THE TWO
Is this possible?
View 3 Replies
View Related
Jan 16, 2007
When I click a command button an InputBox pops up asking me to enter a set of initials. When I enter the initials and click OK all of the rows with the initials in column 'H' are transfered to another worksheet. However if I was searching for say the initials 'AP' and I only entered 'A' and clicked OK, it will still transfer all the corresponding data with an 'A' in column 'H'.
In other words I want the data that is entered into the inputbox to be exact or nothing gets transfered.
View 9 Replies
View Related
