I need to create a custom sort which will cause entries "TBD" and blank to appear at the bottom of my spreadsheet. Any other value will be sorted alphabetically. I'm not sure how to create my sort list to do this. I tried "*, TBD " but this did not work.
View 2 RepliesList File Name with multiple exceptions. I have this
View 2 Replies View RelatedI'm having problem with the ISBLANK function. I have attached my workbooks if someone would care to look at them. My macro basically loads two lists from other workbooks (old & new (attached)). It then finds out which entries are unique to each list, and places them in the EXCEPTIONS sheet.
Column C in these sheets should say TRUE or FALSE as to whether the corresponding cells in Column B are blank but it does not work. Book1.xls contains my macro.
Old.xls and New.xls will need to be selected when prompted.
Does excel 2013 have a fence way to sort a list of alpha numeric numbers and alphabetize in this order A - Z and then 0 - 9.
Ex) Apple, Greg, Rob, Sand, 123, 126, 1000, 2001
To establish the order for on duty personnel every month, I need to find a proper formula.
I have attached the workbook.
So, when I pick out a month from a drop list in cell A1, in column B returns (based on a formula) net work days of that month.
In range H:T, I have a table with the personnel names (in the header) and the individual holidays.
What I want is to have a formula in column C, that returns the name of the first available person (not on holiday in that day), in exactly the same order as it is in the table's header.
Attached File : ON DUTY.xlsm‎
I'm currently using a lookup table to determine programs to run on a machine, however I have 3 exceptions to this and can not get my code to work.
Currently the code looks at column D for the type, then column F for the thickness of material. It then calculates the etch time required using the rate (all shown as 1 currently) on the cal_sheet and then rounds this up to a whole number. This is then compared to a second table where the program details are listed against etch times.
I have a macro that loops through a column of text and removes all text after a hyphen. Example: AU9929-PK becomes AU9929. I need to set up some exceptions that when it processes certain text it does not truncate it. Example: AU9929-ASST stays as AU9929-ASST. So far there are twelve exceptions but this could increase.
Note: The list which includes shop number, description, sku, sku description, quantity, unit price and more is sorted in a particular order as other operations are performed on it that requires it be in a specific order.
I am using Excel 2007. I have a list of 100 names all ranked from 1-100. Is there a way to create a macro that can filter out the top 20, a specific name and also any names with the cell colours blue and yellow?
At the moment using an advanced filter based on criteria I can filter out the top 20 and the specific name I want but can't work out how to leave the coloured rows in as well.
I have a workbook (Data) that I am entering "job title" into column G. Based on key words in the job title I am then manually entering "level" into Column V.
I have created a worksheet (Level Matrix) that has the following
Column A = Job title
Column B - Level
Column C = Exceptions
What I have in column A are the key words - as an example *Director* (wildcard Director Wildcard); in column B is the word Director
What I want the system to do is look for the word DIRECTOR anyplace in the job title entered into Column G of the data worksheet (hence why I have the title between wildcards. IF it finds it then I want to add the level automatically (from Column B of the Level Matrix worksheet).
However if the compare finds any of the words in the title that match any of the words in the Exceptions Column (Column C of the Level Matrix) then I DO NOT WANT To autoload the Director level- it would load DNA. As an example- an Art Director would not load "Director" becasue the word "art" is one of the words in the exceptions column of the Level Matrix.
I have a workbook with timesheet records for a list of resources from multiple locations. These resources have logged time against the projects which is captured on a monthly basis. These time records are against holidays declared by the company. I would like to see if there are any of them who have logged time incorrectly on a holiday.?
View 4 Replies View RelatedWhen using conditional formatting, I have it set to the following:
C2=60 (Reference Cell)
C3=5 (Reference Cell)
Conditional Format Settings
If C3 >= C2/12 then pattern set to Green
If C3 < C2/12 then pattern set to Red
Here is the problem - when you have a value in C3, everything is fine, it's either green or red. If C3 is blank, it defaults to green because the conditional format is true.
Is there anyway to add something in there to have no color when C3 is not populated?
I am having a problem combining two databases. I have database E and F. I need to take any price that is $0.00 from Database E and replace it with the data from database F. The catch is that there isn’t always a price to replace it with. Also, there are parts in database E that do not appear at all in database F. Below is an example (there are about 20,000 lines of data total)
In the examples above I have placed both databases together. Column four has the database designation. As you can see the 1748 Hose reducer has a price for F but none for E.
Basically I need some formula like the following:
If (part number xxx) and (part number XXX) from column 1 are the same, replace the price data from E with the price data from F but only if E = $0.00
Is this possible?
Basically I have the code to Delete All named ranges in active workbook, but I need it to skip over two named ranges called Categories and Length. Is there a way to adjust this to delete all named ranges in active workbook except a named range Categories and another called Length
Dim rName As Name
For Each rName In ActiveWorkbook.Names
rName.Delete
Next rName
I am calculating blood sugars for my daughter and putting them in a spreadsheet. The formula is simple:
Blood sugar - target blood sugar / 20 which yields the amount of insulin that she needs to take. I want this cell to be blank unless I enter a value in the cell above it.
Next, I take the value from that formula and add it to the amount of insulin that she takes for eating.
The value in the top formula can be 0 or less, but, if the value in the bottom one is less than 0, I want the cell to be blank.
I am trying to sort a fairly long list of tasks into a custom calendar order. I have created the custom list as Sept, Oct, Nov, Dec, Jan etc through to Aug, with one additional heading of "Recurring". The list won't seem to sort by the column where these headings occur. See attachment.
View 7 Replies View RelatedIs it possible to sort numerous worksheets based off a list of cells that are the names of the worksheets within the same file? For example my worksheet names are:
YTD Texas
YTD Florida
Period Texas
Period Florida
I can sort the sheets by alpha but it puts the two YTD worksheets together when I need the two Texas sheets side by side (I need this on a file that contains over 100 worksheets otherwise I would do it manually) I was wondering if I could create my order of sheet name in another worksheet and reference that list through vba code?
I am trying to get a list to sort automatically. I am running a competition over an 11 week period and entering weekly data that is totalled on a separate "Totals" sheet.
This "Totals" sheet, in turn, feeds a "Scores" sheet which I want to automatically sort in descending order of score in order to show the current positions in the competition.
The data in the list isn't entered directly but comes from formulas that reference cells in other workbooks.
I have found ways of sorting a list when you enter data into the list itself but what I need is a macro that will update the sort order automatically whenever a new value is entered in one of the other workbooks.
I have a wedding list of names and addresses in one long column. The names occur every 5th line. They are not in alphabetical order.
View 9 Replies View RelatedI have a large list of phrases, about 30,000 all in Col A. (From A3 to be exact)
What I want is to sort by number of characters.
I know excel can sort A-Z,,, but I can't see by number of characters??
Is there anyway I can sort this list by No of Characters please??
List does include letters and numbers.
But they are mainly 3-4 keyword phrases.
This is so from a large list all the least amount of characters will be at the top of the list so I can easily see them,, and probably delete most of the 1,2,3,4 character returned results.
sorry for restarting this thread but i started one similar on saturday and the person I was talking to has not yet responded
I want to know if it is possible to adjust this code line:
Case "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"
from the macro below to instead get the case names from a list on a worksheet... the reason for this is that the names of the worksheets I want sorted will change periodically.
Sub SortDaysoftheWeek()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"
With ws
.Activate
.Range("A6:G256").Sort Key1:=Range("A6"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Select
Next ws
End Sub
I have created a validation list which automatically adds any new values a user enters.
Is there any way of sorting the validation list every time a new value is added to the list?
I have a list of Y-intercepts (Column B - highlighted in Yellow) and I'd like to sort it based on a certain value (in J4) and get the 10 closest numbers above that value and the 10 closest values below that value followed by the remaining numbers.
View 7 Replies View RelatedI have to sort the data as follows:
DI has top priority, MATT is next and SAM is last in the priority list. I attach the list of data and desired result.
I have a Macro that I have recorded that autosorts several columns for me. This works fine as I have 5 columns that need to be sorted in a particular way.
The problem is that I have a custom list which I stored using the: Tools>Options>Custom List tab. Now this works fine on my pc as I have the custom list stored on my PC.however the problem is when someone else uses it on their PC it may no longer work as they won't have my custom list stored on their PCs.
Is there a way to store the custom list in a Macro and then use that list to sort to the criteria needed. For example "One, Two, Three, Four" will not store in alphabetically, so hope would I be able to sort so that they would appear in a chronological manner? Below is the code for the Macro I recorded. As you can see one of the lines says "OrderCustom:=6"; this I am assuming is reading from the list I created.
Is it possible to autosort a drop-down list based on the number of times an item is selected? A drop-down might have 500 lines so it is user-friendly if the top lines are those selected by users the most often.
View 4 Replies View RelatedI have following code, which sorts data if user "double clicks" on cells A1, B1, C1, or D1. If the user "double clicks" on cell D1, I want to sort by column D but I want to apply a custom list. The data is in the "Custom Lists" table but I can't figure out how to apply to my code.
The sequence/sort order of the list is as follows:
aaa+, aaa, aaa-, aa+, aa, aa-, a+, a, a-, bbb+, bbb, bbb-, bb+, bb, bb-, b+, b, b-, ccc+, ccc, ccc-, cc+, cc, cc-, c+, c, c-, ddd+, ddd, ddd-, dd+, dd, dd-, d+, d, d-
code is as follows:
I'm new here, google helped me to find this place. Hope someone here can help me a bit on my excel spreadsheet. I have one list of numbers and it need to be sorted under Perm condition so the list become shorter. The function should go through the listed number and output new list after sorting it. I have attached my excel here.
View 7 Replies View RelatedI have the below code that sorts a list of Doors that I have in row C17 downwards. Door 54, Door 7, Door 109 etc. The list is feeding a drop down box, people find the door they were looking for, in the said drop down box.
Code:
Sub ListSorter()
Dim LastRow As Long
LastRow = Range("C" & Rows.Count).End(xlUp).row
[Code]....
I wondered if there was some code I could add to the end of this, that would the sort worksheets, which are all named after each cell in the list, in the same order.
I have a list of 562 records. In order to generate a targeted mailing list, I would like to display) only those records meeting the criteria:
=SUMPRODUCT(--(M2:M562>0),--(U2:U562>0)) .
I am trying to create a custom sort list by going to preferences/custom lists and typing 37 items into a new list in Excel. It will not take all of the items after I type them in. Is there a limit as to how many items can be in the list. It seems to only take 18-20 items out of the 37 and erases the rest.
View 9 Replies View Related