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 RepliesI 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.
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.
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.
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 RelatedI'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.
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...
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 RelatedI 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.
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,
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
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.
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 RelatedI'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 RelatedI 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 RelatedI 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.
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.
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.
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
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.
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.
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] .......
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?
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] .........
What I have is a bunch of text files and 2 spreadsheets that sort the text files. I’m not sure but I think there might be a better way but the problem is I don’t know VB all that well. Can someone take some time and look over this stuff and let me know if there is actually a better way to make this happen. I also noticed that there are 2 txt file missing and not added in the sorted file (PS3_HIGH, PS3_URGENT)
The text files come from another network and are transferred manually every day to a folder. The purpose of the .xls files is so that someone can look at the counts and try to lower the numbers from day to day. If something is alarming frequently, the current files do not put that information together well. The numbers are still important, but is there a better way to do this sort in excel?
I have a worksheet where users enter an inspection number based on the order a property will be visited, based on the inspection number I want it to automatically enter the information into another worksheet that creates an itinerary which shows this order .
View 4 Replies View Relatedsort the list below:
40105
40106
40107
40105A
40105B
40105C
40105D
40106A
40106B
40106C
and the list will look like this:
4010
4010A
4010B
40105
40105A
40105B
40105C
40105D
40106
A B
1 List 2 things that went well during the meeting
2 Election of Chair Election of Vice Chair
3 Powerpoint Presentation
4 Presentations were informative Everyone at the meeting took part in Q&A
5
6 Group discussed Gardens Upkeep We were mostly in agreement
7
For each meeting attendee (row) they will give their answer to the question (A1).
Some will write no answer, some 1 answer, some 2 answers.
I need to list these answers in another sheet on seperate rows, thus seperate cells, in one column. Without having a gap between each of the answers on that sheet.
I have been wracking my brains and came upon this site searching via that best loved search engine we all know.
I would be very greatful if anyone could help me here.
I'll be watching this closely, nothing else to do.
so I'll be giving quick replies.
I have a column of over 1000 cells with context in it. Each cell as a keyword in parathesis associated with the context. Therefore, different context are associated with the same keyword. However, the cells with the same keywords are spread out all over the 1000 cells.
Example:
cell1: anti-lock brake system (abs)
cell2: baromic pressure (Baro)
cell3: anti brake systematic (abs)
...
Basic solution:
I need to create a vba code that would look through the 1000 cells for a specific keyword and copy all the cells having that keyword in parathesis into another spreadsheet or column, so I can look through them easily. so if i look for (abs) it will give me:
cell1: anti-lock brake system (abs)
cell3: anti brake systematic (abs)
...
Ultimate solution:
The VBA code would be "smart" enough to copy all 1000 cells in keywords order. It would give me all possibilities:
cell1: anti-lock brake system (abs)
cell3: anti brake systematic (abs)
cell2: baromic pressure(Baro)
...
I am extracting data from a website to excel 2010. my problem is the data contains both text, numbers, and sometimes a combination of both.
e.g. hi im ron for more information you can reach me at 6 five 6 four 5 seven 7 three 2 two..
I need it to look like this 6564577322 or 7 * 0 * 2 * 4 * 5 * 6 * 8 * 6 * 2 * 1...i need to look look the same
How do i first format the numbers written in text into numbers and then show only the numbers in a cell minus the text?