Numbers Not Sorting Correctly
Jul 31, 2013
I have three columns expressing death dates (date, month, year). The months and dates are finally sorting correctly within the same year, but I have it set to sort by year first.
The column starts off fine with 1860, goes up to 2013, then starts back at 1884 and goes back up to 2009. I can't find any difference between the two groups: there are some of the same years represented, there are blank month and date cells in both. I have a worksheet with birth dates that sorted fine; the only difference I can find is that birth dates only go up to 1997, but the column is formatted as General, so the turn of the century shouldn't be a problem.
I've tried highlighting the whole thing before sorting as well as selecting one cell as usual. I've also tried formatting the column as Text. I can't think of anything else.
View 14 Replies
ADVERTISEMENT
Oct 29, 2013
Using Excel 2007, I have a very simple spreadsheet made up of only two worksheets that I am using to track the songs played by a band during a current tour.
One worksheet is called "Summary" and the other is called "Setlists". In the Setlists worksheet I list every show played (10 shows so far), and every song played from each show. In the Summary worksheet I have very song listed that has been played during the tour in one column (A), then the number of times that song has been played in another column (B). Column B, the number of times each song has been played, is populated by a COUNTIF function that looks at the Setlists worksheet and counts each instance of each song.
What I want to do, very simply, is to sort on column B in order to display the list of songs from the most played to least played. That is where I run into a problem. When I sort my list of numbers, I end up with a seemingly random list that certainly isn't from most to least and I can't figure out why.
Is it possible that the COUNTIF function which populates that column of numbers is somehow throwing off the sort?
View 6 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
Oct 10, 2008
I am having an issue with Excel not adding three numbers correctly.
C10 = .82
C11 = .18
C12 = .70
C14 = Sum(C10:C12)
I also tried C10 + C11 + C12
Excel keeps telling me the answer is 1.69!
View 9 Replies
View Related
Aug 26, 2009
I should sort numbers in ascending order from left to right, but the numbers sort only from right to left.
This is what I want:
1.3.2.2
1.4.1
2.1.1.1.1
3.2.3.12
3.3.1
And this is how it goes:
2.1.1.1.1
3.3.1
1.4.1
1.3.2.2
3.2.3.12
There are also numbers that have letter at the end (1.2.3a), and the cell format is text.
View 10 Replies
View Related
Feb 25, 2009
I have three columns that are added together. Depending on where I need them to go, I need three options. For example, E6,E7,&E8 are added up, on E9, I need to be able to direct it to "A @ E10", "B @ E11", or "C @ E12"
View 3 Replies
View Related
May 28, 2014
I'm attempting to create a formula that'll sort a range of numbers and then return the corresponding values based on a specific place within the sorted range.
In this case, I'd like the following numbers sorted smallest to largest:
15, 9, 11, 19, 22, 17, 24, 19, 17, 24
The result would return something like:
9, 11, 15, 17, 17, 19, 19, 22, 24, 24
Then I'd like it to return the value based off of the formula in cell U13 & U14. In this case, it'd return the value in the 2nd and the 9th position (bolded above). I tried to use INDEX(), but it's returning the values in the unsorted list. I'd like it to return values from a sorted list. Also, I'm trying to solve this using a formula (not Sort & Filter) or VBA.
View 1 Replies
View Related
Oct 15, 2008
I have managed to produce a table which uses formulas to calculate the amount of 'points' someone/a team has accumulated over a period of time however it seems that for some reason or another I cannot sort the table into order of 'who has the most points'
PosPlayerPlayedWonDrawLostPoints
1stDean120939
2ndJaymo1239018
3rdMacca1229115
4thRoss1219212
above is the final standings but as you can see the 'sort' function isnt working, and if i do any other sort the name of the person and the points no longer correspond... i.e. it ends up putting the names in alphabetical order and leaving all other columns as they currently are, or it changes the points column, leaving the names in the same place...
View 14 Replies
View Related
Nov 13, 2008
I have a list of 2,000 plus numbers and the list contains numbers and text. When I sort or filter, the only option is sort A to Z (or Z to A), but the result is all of the numbers are sorted, then the numbers with letters are sorted. How can I sort both? I attached a excel example of what I am looking for.
View 4 Replies
View Related
Oct 20, 2009
I have some numbers in a column which due to other cells not yet being filled in are returning a supressed #div/0! error. This is fine, but when i go to sort the column it puts them in the wrong order. I would like to record a macro, and assign it to the column header in order to sort the column.
View 5 Replies
View Related
Apr 14, 2008
I'm trying to develop this formula for days now.
how can I not list repeated nums from a array example:
675 863 765 982 856
no repeat nums: 675, 863, 982, 856
as 765 was repeat of 675 it should not be used.
keep in mind same goes of 3-4-5... repeats,
View 10 Replies
View Related
Nov 15, 2006
I have a spreadsheet with three Columns. Column A contains Account numbers that are both Numeric & AlphaNumeric for eg 3000, 3001A, 4001, 4501D.
I need VBA code that will forst sort the Numeric accounts 3000, 3001, 3002 etc and then then AlphaNumeric Codes 3001A, 3002B, 4501D etc
View 9 Replies
View Related
Dec 15, 2006
I have numbers like this and their sorting wrong. I want to sort in decending order by last digit to the right.
How it's sorting now: (sorting by first number sequence)
12/31/05a
12/31/05
11/01/06
10/31/06
I would like it to sort like this:
11/01/06
10/31/06
12/31/05a
12/31/05
These WERE text numbers that were formulated to read like dates. Is there any way to munipulate how to sort? I'm just looking to sort it like it was a date. I get text files in formats like this:
09062006 and I formulated them to read 09/06/06 like it would be a date.
View 9 Replies
View Related
Jun 17, 2014
A column holds numbers and numbers with suffixes. The conventional sort function pushes the text values to the bottom. I need them sorted along with the numbers.
Microsoft's KB says to add a column formatted as text and then to RETYPE ALL OF THE VALUES! Splendid. But my file has hundreds of records. Re-typing all of them would be a major pain. On the other hand, if I had started typing while trying to find a way to sort the darn thing I might be done by now.
Here's what I know so far: If we create a column, format it as text and then populate with 1, 2, 3, 4, 1a, 2a, 3a, 4a, etc. it will sort exactly correct (1, 1a, 2, 2a...) after selecting the "sort numbers and numbers stored as text separately" when the Sort Warning appears. If you try to create the sample I typed here, you need to be careful that the area is formatted as text BEFORE entering the text. You apparently cannot change the format of an existing column of data to Text or if you do it does not have the desired effect when sorting. It might be useful to know if this can be done too. MS suggests not.
View 11 Replies
View Related
Aug 3, 2014
I've question in sorting: I've 2 column in one datasheet. one contain value of numbers and the other contain names of employes. i want to sort the name of the emplyes by the values that is on the first column.
Anther question is how to make it dynamically so ill not sorting it every time that the values of the first column is changing.
View 1 Replies
View Related
May 10, 2013
There must be something I'm doing wrong, because I have a list of numbers and when I select Data --> Sort --> Column G, I am not given the option of sorting "From smallest to largest". Only from "A to Z" or viceversa.
View 3 Replies
View Related
Jun 7, 2013
I'm having some problems sorting a list of numbers that some are larger than others, but it would sort it by how large the number is in regards to 123 or 45... 45 would go first instead of 123... I tried to change the column to numbers but it still won't work. as well as in the custom sort section. I'll try to upload a sample...
View 11 Replies
View Related
Aug 15, 2012
I am trying to do a VLOOKUP using a list based on our Part numbers in Excel 2007. When I do a normal sort it groups the numbers 1 thru 9 by the number of digits, giving me multiple list of 1 thru 9. When I do my lookup it will not find the shorter numbers. Is there some way to sort these Part numbers in order regardless of the number of digits?
View 2 Replies
View Related
May 26, 2014
I currently have a six column table that I would like to:
1. Take in an input in the form of a number and a letter in two separate columns. (Most Recent and Second Most Recent are purely output)
2. Sort the inputs from most recent to least recent - only store the most recent two.
3. An update only occurs if Current differs from Most Recent i.e. no repeat values.
Example:
Begin with a six column table (E/O stands for even or odd).
Current
E/O
Most Recent
E/O
Second Most Recent
E/O
[code]....
Place a number/letter in the current section - which most recent also takes the value of.
Current
E/O
Most Recent
E/O
Second Most Recent
E/O
[code]....
Update the value of current - this moves 10/E to the Second Most Recent entry and replaces Most Recent with the new value.
Current
E/O
Most Recent
E/O
Second Most Recent
E/O
[code]....
Update the value once more - understand the process.
Current
E/O
Most Recent
E/O
Second Most Recent
E/O
[code]....
I tried this with IF statements but the circular referencing makes this seem approach seem difficult.
View 3 Replies
View Related
Jan 7, 2007
I have a fairly large list (1200 rows) of part numbers that I would like to sort. The part number has text and numbers, with a number in the middle. I need excel to ignore this middle number when sorting (but not any of the other numbers). Excel currently sorts like this:
MKDSN 1,5/ 3
MKDSN 1,5/ 3-5,08
MKDSN 1,5/ 4
MKDSN 1,5/14-5,08
when I want it to sort like this:
MKDSN 1,5/ 3
MKDSN 1,5/ 4
MKDSN 1,5/ 3-5,08
MKDSN 1,5/14-5,08
The first few letters in the part number or the numbers at the end aren't consistant. But it's always the numbers after the "/" and before the "-" (where there is one) that I want to ignore.
At the very least I would I need the list in alphabetical order. I don't need the list to be sorted by the "ignored number" at all. Meaning, I would be happy with this result:
MKDSN 1,5/ 3
MKDSN 1,5/ 4
MKDSN 1,5/ 2
MKDSN 1,5/14-5,08
MKDSN 1,5/ 3-5,08
MKKDS 2/24
MKKDS 2/20-3,5
MKKDS 2/ 2-3,5
I don't mind getting rid of "/" or spaces or "," in the part number, but I would prefer to not split the part number between two columns.
I tried creating a custom list, but there are just too many varieties to list them all.
View 8 Replies
View Related
Mar 2, 2009
Need to sort the following in excel:
(2,000.000)(1,000.000)(500.000)(200.000)200.000 500.000 1,000.000 2,000.000
to the following:
2,000.000 (2,000.000)1,000.000 (1,000.000)500.000 (500.000)200.000 (200.000)
How can I sort like this.
View 9 Replies
View Related
Mar 1, 2014
with this problem (which I've been trying for weeks to solve).
I have a column ("C") of 640 numbers, which I need to randomly sort and place into the adjoining (ten thousand) columns. I then need to correlate each of these 10k columns with column "B".
The problem I have with the attached (which works for small numbers), is that when I increase the table size to 640 rows and anything more than a dozen or so columns, it takes forever to calculate and Excel often crashes.
random ver 1.04 280214.xlsx
View 1 Replies
View Related
Mar 28, 2014
I have a worksheet with a row of mixed cells near the top. By "mixed", I mean some cells have only numeric data (numeric-cells) and some have only words (word-cells).
GOAL: I want to sort the columns to the following order:
First all the columns with only numeric-cells in the mixed row, with those columns sorted in DESCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order.
So far all I have been able to accomplish is:
First all the columns with only numeric-cells in the mixed row, with those columns sorted in ASCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order. This was accomplished by a simple sort using ASCENDING order on the mixed row.
OR
First all the columns with only word-cells in the mixed row, with those columns sorted in DESCENDING alphabetic order of the word-cells in the mixed row; followed by the columns with only numeric-cells in the mixed row sorted by DESCENDING numeric order. This was accomplished by a simple sort using DESCENDING order on the mixed row.
This may sound as confusing to you as it does to me, so I have attached an example file showing hypothetical Input Data and the Desired Result.
I did the example file in multiple steps, but that's too painful in the general case where calculations determine what is in which cell in the mixed row. All data are calculated, even the word-cell data AND so dependent on other data, and the columns in the mixed row that are word-cells and those that are numeric cells don't remain the same.
Eventually I'd like to do this with a macro, so if VBA has some power to do this that EXCEL commands don't, then that's fine.
I could probably live with the final result as numeric-cell columns sorted in DESCENDING order followed by word-cell columns sorted in DESCENDING order, but I haven't even been able to accomplish that AND it's not quite the goal.
View 3 Replies
View Related
Oct 13, 2008
I want to sort the list like this:
1) If there is a zero (null) value in all 3 months, these records should be at the bottom sorted by record name (I did not show this field in my file).
2) If there is a non-zero (non-null) value in any of the 3 months, the records will be sorted with each other by total change.
Is there a way to do this without me doing sorts multiple times and manually moving rows of data around (which is what I have done to arrive at the list I have attached)? I am not experienced with VBA or Macros, and would prefer a detailed explanation if a solution is using either method.
View 2 Replies
View Related
Oct 16, 2011
I am trying to sort some data which contains numbers and letters and can't seem to find out how to personalise the sorting function on excel. Is it possible to sort cell with a mixture of letters and numbers?
Here is a link to a file to show how far I have got! [URL] .......
View 5 Replies
View Related
Jul 1, 2013
I wanna sort these numbers in descending order and related to each others in excel 2010.
H
G
F
E
D
C
B
A
1383.99
1533.954
831.197
1533.954
1533.954
1383.99
700
1533.954
1
[Code] .........
About descending and related to each others, I mean for example : #700 in column A,B,C,D,E,F,G,H places in the same row and the same happen to #1533.954 and others. About the numbers that are The One ( like 549.894), I need to put them in its column and a new row with empty cells in its other columns. In the end i want a table like this:
H
G
F
E
D
C
B
A
1533.954
1533.954
1533.954
[Code] .........
View 5 Replies
View Related
Aug 14, 2007
how to use some form of the saveas dialog within excel without quotes appearing in the filename that is passed to the dialog.
For example:
ChDir ("J:TestFolder")
x = Application.GetSaveAsFilename("Test.xls", "Excel files (*.xl*),*.xl*", 1, "Custom Dialog Title")
The above code displays the saveas dialog form but has "Test.xls" surrounded by quotes. Normally, there would be no quotes around the specified filename.
Is there another way I can do this or will I just have to live with it?
View 3 Replies
View Related
Mar 3, 2014
I should i make this formula
A customer is eligible for a discount if the customer’s 2009 sales are greater than or equal to 200000 OR if the customers First Order was placed in 2012. " If its true it would be yes and if false no"
So far i have in the IF function
=IF([@[2009 sales]]>= 200000 ,"Y","N")
How should i insert if the customers OR First Order was placed in 2012?
View 2 Replies
View Related
Jan 24, 2007
I have used the Excel program for a number of years to print out "Jukebox Title Strips" for my Jukebox. I have used the same "formula" for cell height, width, etc. during that time.
The outline of the title strips are preprinted and perforated on an 8.5" x 11" sheet. I recently tried to make some new strips and the printing does not line up correctly as it always had done in the past. Now, the titles are not high enough on the top portion of the sheet, and not low enough on the bottom portion of the sheet. So, it is not that I could just raise everything up or lower everything down to line up correctly... it now has to be changed in both directions. Of the 20 title strips on the sheet, the only ones that line up correctly are the two that are halfway down.
I thought that somehow the file had become corrupted, so I made a new template, but I still have the same problem.
Are there any suggestions of what I could do?
In the links below it shows an example of a Title Strip page.
View 14 Replies
View Related
Jan 29, 2009
I have built a UserForm (Excel 2003) that looks up a Row dependant on Criteria and displays the values on the User Form.
View 9 Replies
View Related
