Sorting Starts With A Number

Jan 10, 2008

I have data that I am trying to sort that starts with a number and contains a number within, then sometimes ends with a letter. Here is the dataset I will use as example.

1-TP-2
1-TP-4A
1-TP-11A
1-TP-12
13-TP-22A
13-TP-22B
13-TP-102
13-TP-103A
13-TP-103B

Excel wants to sort as:

1-TP-11A
1-TP-12
1-TP-2
1-TP-4A
13-TP-102
13-TP-103A
13-TP-103B
13-TP-22A
13-TP-22B

Is there a formula that I can use to sort the data in numerical order by the last digits exactly like the first dataset? There are all sorts of veriations of the first digits and the last, but the "-TP-" always remains constant.

View 11 Replies


ADVERTISEMENT

Verify A Postcode Format That Starts With A Number Followed By One Or Two Letters

May 5, 2009

How would I verify a postcode format that starts with a number followed by one or two letters, space, number, letter, letter, if correct displays correct if incorrect displays incorrect

View 14 Replies View Related

Conditional Formatting If Number Starts With Specific Digits

Jan 23, 2014

I am trying to apply conditional formatting to highlight the cells in D column if it starts with 91099 or 91004.

I tried to do the same using wild card, and left function but didn't get the desired output.

View 7 Replies View Related

When Starting Number Reaches Zero It Starts To Count In Minus

Mar 8, 2014

I am having trouble setting up my cycle count spread sheet. For instance if my starting number is 1000 and when it reaches 0 or below it starts to count in minus when I just want it to automatically reset to 1000

View 3 Replies View Related

Sorting Number With Text

Jan 11, 2008

sort 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

View 9 Replies View Related

Sorting Number/letter Combos

Mar 11, 2009

I have a spreadsheet with information in columns a-x. In column A there are part numbers like: RH630-34, PH630-343, 6-255, 16-01, 72500, There are may combinations of just numbers, and numbers first letters second, and letter first number second.
All usually seperated by a hyphen. The entire spreadsheet will be sorted by Column A first.

I need to sort them so the order would be numbers first and combo with number letters next. finish product: 6-255, 16-01, 72500, PH630-343, RH630-34. Is this possible? I have seen other posts and suggesting putting spaces before the numbers. That seems to work but in the case of 6-138 and 6-1038 the 6-1038 is first

View 2 Replies View Related

Sorting Combined Number And Data

Apr 20, 2012

How to correctly sort data when it contains both text and numbers.

This is what is currently happening
LA100LA102LA109LA11LA117LA118LA12LA120LA121LA199LA21LA216LA220LA221/SLA227LA229LA23LA230LAK102LAK107LAK11LAK117LAK120

BUT I NEED IT BE IN ORDER LIKE THIS -
LA11LA12LA21LA23LA100LA102LA109LA117LA118LA120LA121LA199LA216LA220LA221/SLA227LA229LA230LAK11LAK102LAK107LAK117LAK120

View 2 Replies View Related

Sorting In A Large Number Of Rows

Oct 29, 2007

I am trying to sort in rows, a large number of rows. I can do text to columns, delimit by colon, and sort individual rows. But I have spreadsheets that are 100s of rows long. Is there anyway to automate?

View 4 Replies View Related

Sorting By The 5th Character In A Part Number

Dec 30, 2008

I have part numbers in a column that look like the numbers listed below. All numbers begin with A, so the default sort begins with the first number. I would like to sort the list using the middle three numbers denoted by the red x's in the first example. Is this possible?

A 385 XXX 0055 A 385 466 0060 A 385 466 0160 A 385 584 7024 A 387 284 0185 A 388 017 0160 A 389 260 1485 A 389 262 0293 A 389 262 4935 A 389 262 9134 A 389 267 2819 A 389 267 3319 A 393 328 0065 A 398 267 3319 A 403 990 0210 A 403 997 0620 A 404 260 0074

View 9 Replies View Related

Sorting Rows With A Combination Of Text And Number?

Aug 3, 2013

I'm currently trying to make a sort macro that sorts a row of data that has a combination of numerical data and the text entry "N.A."

e.g.:

My field:
1
2
N.A
4
5
N.A
9

I want to sort the cells by descending value. But the result appears as:

My field:
N.A
N.A
9
5
4
2
1

But I want to numbers to appear first and have the "N.A."s to be after the number, since I have about 100 columns and some rows have like 30 N.A., which is pretty bad for usability if all that appears in the first screen are "N.A."

EDIT: by the way by ascending the data is fine since it orders by smallest number to largest and THEN the "N.A."

View 5 Replies View Related

Excel Custom Number Ascending Sorting

Jan 17, 2008

I need to sort numbers in a specific ascending way in excel but I'm not sure how to create a filter, number format or custom sort which will do it.

Sample Data:
245.1
245.10
245.11
245.12
245.2
245.3
245.4
245.13
245.14
245.15
245.19
.................

Sort Result:
245.1
245.10
245.11
245.12
245.13
245.14
245.15
245.16
245.17
245.18
245.19
245.2
245.3
245.4
245.5
245.6
245.7
245.8
245.9

Expected Sort result:
245.1
245.2
245.3
245.4
245.5
245.6
245.7
245.8
245.9
245.10
245.11
245.12
245.13
245.14
245.15
245.16
245.17
245.18
245.19

View 9 Replies View Related

Number A Colum For Sorting As Data Base

Jun 11, 2008

totaly new to Excel (just using two weeks) as a database

I need to number a colum, 1 to what ever, so that I can use that colum to re-sort the data base back into original order.

View 9 Replies View Related

If The Cell Starts With ...

Oct 24, 2008

I am trying to do this: - If the Cell starts with a 9 and is 5 digits long, then the action is taken.

It seems fairly simple, but I am still stucked since I do not know the syntax for this specific thing.

View 5 Replies View Related

Combobox Starts From Last Row?

May 16, 2013

with this code, how can I make my combobox1 starts with the last record ??

Code:

Private Sub UserForm_Initialize()Dim cell As RangeWith Worksheets("Sheet1")For Each cell In .Range("C2:C" & .Cells(Rows.Count, 3).End(xlUp).Row)If Not IsEmpty(cell) Then ComboBox1.AddItem cell.ValueNext cellEnd With

View 3 Replies View Related

If Cell Starts With 74 In VBA

Sep 23, 2008

i am trying to write this piece of VBA which is part of a bigger VBA project.

Here is my code at the momment

Sub Macro8()
'
' Macro8 Macro
' Macro recorded 22/09/2008 by SIDDIR

Dim AccCol As String
Dim breakdown As String
Dim reinscode As String

AccCol = Range("A2").Select
breakdown = Range("AC2").Select

reinscode = "74"

If Left(AccCol, 2) = reinscode Then breakdown = "Reinsurance"

Exit Sub

End Sub

Basically trying to check if cell in AccCol strats with 74. If it does, it gives me the answer of "reinsurance" in cell in Breakdown.

This loops each cell

Can't get this working. I know i can do this in a formula, but in need to incorporate this in a bigger VBA project.

View 9 Replies View Related

SUM Columns B And C If Column A Starts With One?

Mar 11, 2014

Column A has numbers starting with 1, 2, or 3 and Columns B & C have various amounts. I would like to get totals or columns B & C for Column A numbers starting with 1.

Tried a few things including SUMIF(A:A,"1*",B:C) with no luck

View 5 Replies View Related

Popup Q/A Box When Macro Starts

Mar 27, 2009

I would like to have a question box pop up w/Yes and No Buttons at the start of my macro to choose between two different sub routines. Therefore, MsgBox would say: Import New Yard Inventory?

If Yes, then it would run the alternate subroutine. If No, Then it would run macros normal routine. Not quite sure how to do this. Would the msg box have to be a form box where, a representive value would have to get filled into a cell? Then
reference if cell value is true ,then or would it register the value to memory so then, the code would have to to call the given value then run code if value it true?

View 3 Replies View Related

Enter Starts The Macro

Apr 17, 2008

I want a macro to start when I enter anything in a cell. For example anytime I enter a value in cell " D1 " a macro should start.

View 9 Replies View Related

VBA Codes: First Ends In G Second Starts In G

Apr 28, 2006

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, Range("e273:g284")) Is Nothing Then
Set rng = Range("e273:g284")
ElseIf Not Intersect(Target, Range("g273:j284")) Is Nothing Then
Set rng = Range("g273:j284")
ElseIf Not Intersect(Target, Range("j273:l284")) Is Nothing Then
Set rng = Range("j273:l284")
End If
Application.EnableEvents = False
If Not rng Is Nothing Then
If Application. CountIf(rng, Target.Cells(1, 1).Value) > 1 Then
MsgBox "This vehicle is booked out at this time"
Target.ClearContents
Target.Cells(1, 1).Select
End If
End If
Application.EnableEvents = True
End Sub

the code is perfect for what i need it to do but the only problem i have is that the codes roll on from each other... I.E:- E273:G284 - G273:J284 - J273:L284. first ends in G second starts in G, Second ends on J third starts on J. for some reason this doesnt work, the first code gets the prority and works but the second works in all the columns except the first one.. in this case the first code is fine, second actually works from H not G and third works from K not J

View 2 Replies View Related

Determinde Which Button Starts The Code

Feb 4, 2009

In my workbook I have a few buttons that start procedures that end with a messagebox.

I also have 1 button that runs all seperate procedures
When I use this code I want to skip the msgbox that belongs to each individual procedure.

View 8 Replies View Related

Check If A Cell Value Starts With A Letter

Nov 2, 2009

I have a workbook and I want to check in a cell, if the value in the cell starts with "S" or "D". In VB.net there is a method .StartsWith to check the starting letter in a file. Is there a way to check the starting letter in the cell?

View 2 Replies View Related

Chart X Axis Starts 1 Day Before Data

Nov 18, 2011

So I added a x-y scatter series to a line chart (in order to add a vertical line to the chart) and now I'm having a problem with the x-axis. It lets me select the start and end date, but when I put it on auto is starts 1 day before the data does. For example, if my data starts on 5/1/2011, the x-axis starts on 4/1/2011 and the there is no data point at that date.

I could manually change this, but the data is dynamic so the start date changes. Since I can do formulas in the format axis tab (date+1).

View 9 Replies View Related

Excel 2010 :: AutoFilter Starts In Row 2

Apr 6, 2013

If I apply an autofilter in my worksheet the autofilter dropdownmenu stands in row 2 and not in row 1. How is this possible?

View 3 Replies View Related

Macro That Starts When The File Gets Saved

Oct 19, 2009

I found this for my problem (creating a macro that starts when the file gets saved): Private Sub App_WorkbookBeforeSave()

I tried to use it, but the macro didn't do anything when I saved the file.

View 9 Replies View Related

Subtract Time Value Which Starts Today And Ends Next Day?

Jan 31, 2014

I want to subtract a time value which starts today and ends the next day. Is there a way to achieve it as i get error when i do that.

View 3 Replies View Related

Auto Populate Into A Column That Starts In Cell E5?

Jul 2, 2013

I am trying to auto populate into a column that starts in cell E5 and references cell C5 (along with a dataset in N and O). In cell E6, I copied and pasted this formula and changed all of the C5 values to C6. When trying to use the auto populate cross hair tool, excel changes all N and O cells, from O165 to O166, O167 when they should have remained O165 and N165 for every cell.

=IF(OR(C5<MIN(N9:N165),C5>MAX(N9:N165)),"",IF(C5=MAX(N9:N165),INDEX(O9:O165,
MATCH(C5,N9:N165,1),1),(((INDEX(O9:O165,MATCH(C5,N9:N165,1)+1,1)-INDEX(O9:O165,
MATCH(C5,N9:N165,1),1))/(INDEX(N9:N165,MATCH(C5,N9:N165,1)+1,1)

[Code].....

View 3 Replies View Related

Conditional And Cumulative Subtraction That Starts Over Again When The Value In A Column Changes

Feb 5, 2010

I am attempting to perform a cumulative subtraction function that starts over again when the value in a column changes. I have a list that is sorted by a column for item number. The other two columns show the amount we have in stock and the amount being ordered against that stock. The order of rows IS relevant and cannot be changed.

Sample output is attached, with the "given" data outlined in a bold border. The only way I have figured out to do this is to nest a whole lot of IF statements in a formula, which is not very elegant or easy to do, and is limited by the number of IF statements that can be nested.

View 2 Replies View Related

Search Down Column And Delete 13 Characters If Starts With 20

Jul 31, 2014

in VBA how would I script to search down column and if the cells starts with "20" delete the first 13 characters?

also if it finds more than 10 empty rows it cuts the loop otherwise I may be waiting a while

View 7 Replies View Related

Select Button And Starts Populating Column

May 4, 2007

I have this macro for a button that needs to start populating a column
with Xs. I want to populate cell F2 with data from a different sheet with Xs.

ub Create_button_macro()
Dim sButton

Set sButton = ActiveSheet.Buttons.Add(156.75, 36.75, 73.5, 22.5)
With sButton
.Characters.Text = "Program 1"
With .Font
.Name = "Arial"
End With
.OnAction = "Button1_Click"
End With

End Sub

Sub Button1_Click()

End Sub

View 9 Replies View Related

Delete Entire Row If Cell In Column Starts With Asterisk

May 9, 2008

I'm using the following code to delete rows that I don't want to include and I've ran into some more things that need to be deleted...

For lLoop = RngCol.Rows.Count To 2 Step -1
Select Case RngCol(lLoop, 1)
Case " Date:", "Skill:", "Agent Name", "~*", "*Train*"
RngCol(lLoop, 1).EntireRow.Delete
End Select
Next lLoop

An example of "~*" would be: ***SICARII***
An example of "*Train*" would be: Ozgrid Train1

It's not recognizing these new cases. Do I have to utilize FIND? (since CTRL+F does work with the given cases)

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved