Sort Column Of Number & Numbers With Alpha Characters Attached
Jan 7, 2010
Have a spreadsheet that contains a column of 3 digit numbers as well as 3 digit numbers with 2 trailing alpha characters.
Example:
376
377
421
376AB
376XY
377NC
421GQ
421EF
Need to sort by this column, but, with the parameter of sorting first by the numeric only, and then by numeric with alphas. So, the above list would look like this sorted properly:
376
376AB
376XY
377
377NC
421
421EF
421GQ
View 9 Replies
ADVERTISEMENT
Mar 6, 2007
I have columns and rows that are populated with one of three letters: P, S, & T. At the bottom of the column and at the end of the row, i'd like to assign a numeric score to the entire column/row that is based on converting the characters to a numeric eqivelant. P = 3, S = 2 and T = 1. I'm guess a case statement would be used, but not too sure here. So, I'd need the code behind the worksheet as well as the formula(s) used for the score. I've attached an example.
View 3 Replies
View Related
Aug 8, 2009
I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).
Option Explicit
Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function
Two requests:
1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?
2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"
View 5 Replies
View Related
Aug 27, 2013
How can you tell what a column number as referenced by the column alpha identifiers.
Ex: Column AN would equal what column no?
View 2 Replies
View Related
Apr 24, 2009
Attached spreadsheet - the cell highlighted in yellow.
This is the formula I currently have, but it isn't giving me the results I am looking for:
=IF(TYPESVC="CompanyX", COUNT(Companies!I:I), " ")
Basically, IF the Companies tab, last column has 'CompanyX' written in it - then I want to count the number of responses provided in the Q2N column.
Same formula would be copied over to the other cells to the right as well (for Q5, Q6, Q7, etc.)
View 5 Replies
View Related
Jul 24, 2009
I am trying to capture alpha characters entered into a textbox. If a character is (something) then a variable is (something).
View 2 Replies
View Related
Aug 2, 2012
I'm trying to sort by a column with one to four digit numbers, any of which may be followed by a single letter, i.e. 1,2,3 4A,5, 10,11A,75,101A,600,705,1010B,1011A.
I'd like them to sort in the above order. I am only able to have excel 2003 sort all of the numeric cells first, followed by the cells with the alpha character.
View 4 Replies
View Related
Dec 30, 2008
Ive written the macro below to sort Column A according to the length of the cell values in Ascending order. Ive done this by writing the length to Column B for each value and then sorting on column B. Surely there is a more elegant method, perhaps using arrays.
Sub SortIt()
Range("A1").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Value = Len(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending
End Sub
View 5 Replies
View Related
Nov 3, 2009
I've got a worksheet here that i need to update every week. The problem i'm having is that i have a table of values that's drawing values in from another larger table. For example in B9 the cell is referenceing the I2 cell in another sheet. When i pull down the formula in B9 i want the cell below to reference J2 and so on. In other words, I only want the alpha characters to increment. Tried messing around with dollar signs but it doesn't seem to be doing what i want.
View 2 Replies
View Related
Mar 5, 2009
I have an application where there is a column A with values of F1 through
F83. There is data in columns B,C,D,E,F AND G.
From time to time there is an addition in column A, the value being F1A or F2A etc.
The rows then have to be sorted so that F1A follows F1 and the next row would start with F2 rather than F10.
View 2 Replies
View Related
Dec 15, 2006
I'm doing a sort of product codes which are of different character length with some numeric and others alpha-numeric.
A small section of the sort is below.
I want it to end up like this:
4040
4040A
4040B
4040BTO
4040C
4040E
40400
40409
41402
42402
42402A
43400
But the sort ends up like this:
4040
40400
40409
4040A
4040B
4040BTO
4040C
4040E
41402
42402
42402A
43400
In other words, I would like product codes where the last character is a letter to be sorted ahead of product codes where the last character is a digit. The way Excel is normally set up, it sorts in just the opposite way.
View 9 Replies
View Related
Oct 22, 2009
In the ID column of the attached excel file whenever I convert the numbers stored as text to numbers it results in a weird transformation.
e.g. an ID of 480610141001 becomes 4.8061E+11. When I do the same process in the name column, which has similar numbers, it will give the correct result following the same process.
View 2 Replies
View Related
Oct 5, 2007
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
View 5 Replies
View Related
Jun 26, 2013
I have a data set that shows numbers for categories over time. I would like to add the data label to the numeric value and then sort the data by the original number. Is this possible?
Here's an example
A 1 2 3 4
B 5 7 4 1
C 8 0 2 1
D 3 4 9 5
I would like each column sorted independently based on the data in that column, but I want the results to have the label from column 1 concatenated with the value in the other columns such as this:
A1 C0 C2 B1
D3 A2 A3 C1
B5 D4 B4 A4
C8 B7 D9 D5
Each column is sorted by the numeric value, but has the name label from column 1 attached to it.
View 1 Replies
View Related
Jun 3, 2014
I have on the first tab a screen where you can fill in the name of the .doc or .pdf file.
That works fine, but now I would like that when I fill in a number and click on test, it opens te .doc and .pdf file that is attached for that number.
If you take a look at the pdf file you will see a second and a third tab.
First column is the number they need to fill in and the second column is the name of the .doc or .pdf that needs to be opened with that specific number.
How to link it and open both files for that specific number.
But not every number has a .doc as well as a .pdf.
View 5 Replies
View Related
Jan 23, 2009
I have data in column C of rows 25 to 45. The data varies in number form.
What i would like to do is have some VBA code that i can place into a macro which will sort the numbers in column C of rows 25 to 45 putting the highest number at the the top (C25). But i need to move the complete row.
View 9 Replies
View Related
Nov 29, 2012
I have a list of files in folders.
When I make the index of those folder, i have an excel list with in column A these numbers
1.1.1.1 (so the file 1 in the 1st subsubfolder in the 1st subfolder in the first folder)
1.1.1
1.2.1.1
1.2.2
1.3.1
etc... The list goes on with 1000s rows.
And in column B I have the names of the files
Unfortunately, when i sort the spreadsheet with the number on column A, the order is not good....
So I would like to find a solution to sort things in the good order.
View 7 Replies
View Related
Jan 22, 2010
I want to sort on number of words, i.e. if a cell contains 1 or 2 words etc, with the cells containing 1 word coming first then cells containing 2 words. Also, if possible, first the cells with fewer characters.
View 9 Replies
View Related
Apr 23, 2012
I am trying to write a formula, I have 6 sets of criteria with a lower and higher range, if the number falls within the criteria I would like it to return the Alpha number,
eg, 104, will return D
MinMaxReturn030A3160B6190C91150D151240E241360F
View 7 Replies
View Related
Feb 5, 2007
i need to see what cell in the collum is empty. how can i do that in vb?
View 6 Replies
View Related
Feb 14, 2003
I have three columns of values that are in no particular order that need to be sorted in ascending order in another column.
View 9 Replies
View Related
Dec 17, 2012
I have 3 columns of data: col. A = name (random order), col. B = Net #, Col. C = Gross #.
I am using =LARGE(C$1:C$4466,ROWS($D$1:D1) to Automatically sort col C in decending order.
I would Like to do another decending sort but only the values in Col C that corespond to a particular name in Col A. Can I imbed a index match function combination within the large function to do this?
Name
Net
Gross
All Sort
SortA
SortB
SortC
SortD
A
508
-200.129
101.685
[Code] ..........
View 6 Replies
View Related
Aug 12, 2007
I am attempting to sort a column with mixed letters and numbers. However, I have been totally unable to get them to sort the way I want them.
What I have is:
K600
K2
K2A
K2B
K159
K159A
K159B
K1
K1A
K1B
K428
K8
K8A
K8B
etc, etc. The prefix letter is only a K - no other letters. However, the suffix letters can be anything from A to E (at the present time)
I need to sort them numberically from K1 in descending order ie:
K1
K1A
K1B
K2
K3
K4
K4A
K4B
etc etc etc
View 9 Replies
View Related
Jun 8, 2014
I would like to create a formula that will sort the numbers in column B, there are also equal numbers in this column
The difficult part is that there is text in column A, and is linked to the numbers
So if numbers are sorted the names should be sorted in the same way as well.
View 3 Replies
View Related
Jul 20, 2009
I am comparing to columns of data from MS Project using Excel. In one column are the resource names for the project tasks. In addition to the names are the designations for % utilized. So the column's data will look like this:
John Jones[15%],Mary Smith, Fred Arguello[240%].
I want to compare this column with another resource names column which will never have utilization values - only the names.
In order to do that I need to "mask" the non alpha characters out of the first string.
View 9 Replies
View Related
Sep 28, 2013
I have an index of 80,000 names from an index. Some names appear in multiple volumes and on multiple pages within a volume. While the name is the same, they are different people. The Roman numeral is the volume and the numeric is the page number.
Example of original data: Joe Shmoe V-225, 310 VIII-22, 86, 110
I have separated the data into separate columns. Now I have:
Col 1 Col 2 Col 3 Col 4 Col 5 Col 6
Joe Shmoe V-225 310 VIII-22 86 110
At this point, I want to combine the Roman numeral in Col 2 with Col 3 and 4 and combine the Roman numeral in Col 4 with Col 5 and 6.
There are way too many records for me to manually enter the Roman numeral where missing.
View 7 Replies
View Related
Jan 8, 2014
I have a sheet with thousands of rows of ID's which are made up of a series of letters, numbers, underscores and or spaces. Within the sheet there are numerous duplicates so I need to able extract the numerical element, which is the key data to be able to identify the duplicates.
The numbers I need to extract are mainly 6 and 7 digits long however don't always sit in the same place within the ID. I don't need the 3 digit number sequences, just the 6 & 7 number sequences
e.g
AB_123_456789_Jones
CD_131_7654321_Smith
AB_123_Jones_456789
I've found a formula that strips out all the numbers, however it also included the 3 digit numbers which I don't want. I've also used Text To Columns to break up the ID across a number of columns however that leaves me with 2 and sometimes 3 columns where a 6 digit ID appears
Is there a simple way to extract the number if it's a certain length? i.e. 6 or 7 digits long
View 14 Replies
View Related
Jul 18, 2006
I am trying to create a formula (or if VBA is better) where when a name is put into a field the next alpha mumber is created. Have attached example.
View 3 Replies
View Related
Jun 17, 2009
I need a formula to drag down the attached that will place zeros infront of any numbers that don't have 9 digits. so if a part number consists of 6 digits then i need 3 zeros in front of it, if a part number consists of 9 digits then i dont need any zeros proceeding it.
View 2 Replies
View Related
Jan 17, 2008
I have a pretty basic spreadsheet which requires a quantity and a unit price (which may change) and then the total. I find that if there is no number in the quantity cell, it reads it as if there is a "1" in there. Can anyone offer assistance on how to get around this without placing zero in that spot?
View 9 Replies
View Related
