Excel 2007 :: Creating Automatic Sort Of Overall Rankings?
Feb 22, 2013
I am building a country ranking model which ranks several different macroeconomic indicators and applies a weighting to each of the indicators. I have an overall ranking column which is an average of all the ranks. The problem is whenever I change any of the individual indicator's weighting it obviously affects the country's overall ranking. Is there a way that the model can automatically "re-sort" the overall rankings without me having to manually click on the sort by smallest to largest filter each time?
I have been struggling to convert an array of weekly data rankings into monthly rankings using the closest date to month end and cant seem to find a way. see below
spx indexspgsci Indexief us equityspx indexspgsci Indexief us equity 24/01/201212431/12/2011???20/01/201213430/11/201113/01/201213431/10/2011 6/01/201213430/12/201114323/12/201114316/12/20111429/12/20111322/12/201113225/11/2011132
How do I convert them to a monthly rank18/11/2011132using the closest to month end date 11/11/20111324/11/201113228/10/201113221/10/201113214/10/2011132
I am working on a spreadsheet that keeps track of upcoming contract and service renewals. Within the spreadsheet there is a column named "Status"; using a formula based on the renewal and engagement dates this column will automatically state one of the following -
"Contract Status OK" "Up for Renewal" "Renewal Due"
When I open the the file I would like excel to email 2 specific email addresses. The email should only contain data from the rows that state "up for renewal"; this email will act as trigger for my team to engage with the suppliers in question.
Please find attached a daily copy of a spreadsheet that is used to monitor train running times.
The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.
The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.
Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.
For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)
What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.
To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.
My problem is two fold.
Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.
Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.
I have a column of the date of sales. (several hundred lines long) and each line has the name of the item sold. I need to complete a report showing how many sales of each item per date. Date of sale in one column and the name of the product in another.
I have written a 2007 workbook which contains 4 simple macros. One of the macros automates the process of saving the print range as a .pdf file. It works fine on my pc but when I send it to others to use, when they try the macros, they all return a 1004 runtime error.
I am struggling with doing a sort, of a group of alpha characters within a cell (Excel 2007). My data was exported from Oracle 8 as a varchar2 and consists of letters.
Example of what I have BAC GHL DFE MEG
Example of what I want ABC GHL DEF EGM
All I could find were these complex looking solutions involving cursors, plsql, etc. I'm hoping there's a simple solution, maybe within Excel. I've tried different formatting, made sure no other characters were sharing the cells, and tried typing fresh data into a cell (instead of the exported data).
What I have done is entered code to auto generate the date in column O whenever data is entered or altered in column A. Here is that code:
VB: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then _ Target.Offset(0, 14).Value = Now
End If End Sub
What I am trying to do now, is create a column that will take the information from O and do a sort of COUNTIF function that will count how many items of data were entered on a certain date by the day. For a clearer example, I want it to tell me how many items were entered/altered on 7/23. But I also want it to continuously calculate it for each date after that. Preferably automatically, but if a macro is needed I can create an update button.
Once it can achieve that I would like to create a dynamic graph that will automatically (or via macro button) update to show the last 5 days. It should display the date and how many items were entered that day.
I'd like to use two columns / labels to create a colorful bar chart that just piles each field value on top of each other and shows the amount (and percent of total if I can) along the first column's values next to the bar chart something like below..
So column 1 is the "entity name" and column 2 is the "value/amount".
I can't make this chart look right at all. I have Excel 2007.
The first part of this code is fine and completes all the borders. The second part is to the all the workbook by Data Sort in ascending order starting with
Column C Then Q Then column V
But also knowing to search to the last line. I think i might be close but not close enough for this to work.
Sub Macro2() Dim LR As Long, i As Integer Dim mysheet As Worksheet
Sheets("Test").Select LR = Range("A" & Rows.Count).End(xlUp).Row With Range("A9:AD" & LR)
I've set up an email to be sent via a macro (excel 2007 and outlook) and I expanded the body of the message and it now comes up with an error "Compile error: invalid outside procedure"
It was working when I only had 4 lines and now it's not working.
I'm trying to sort all .xls files in a folder by modified date. I want most recent at top because then I will be cycling through and using the most recent version of files that have similar names.
Excel 2007.
The code I have so far is this:
Code: Sub autolink() Dim folderPath As String Dim FileName As String Dim wb As Workbook
[Code]....
But I need something before it sort the folder by the modified (or better creation date) because there are several files with similar names. I know know, I could put a date in the file name and look for that but I don't control the naming of the files.
I already have a Macro button built that hides values in column A. The next step I am trying to perform is a custom sort on column B in this order "Backordered", "Sourced", Shipped", and "Received". Here is my code that I have so far but where to begin adding in code to make this button sort.
I'm trying to create a very simple order form. It's been a while since I've done this in Excel, and I couldn't find an answer when I searched. (I may be a lousy searcher, though.)
I want the user to enter a quantity in Column E of the "Common Items" worksheet, and have all the rows with quantities copied to the "Order" worksheet. (On the "Order" copy below, I just did a copy/paste to show the desired effect.)
I'm pretty sure this can be accomplished with an If/Then, but I'm lost! Optimally, they'd enter their quantities, click on the Order sheet and hit print.
In my Excel file, I have one worksheet with about 36 columns and 2500 rows of data.
I am looking for a way to create an automatic filter which will look at one column and filter out 13 specific phrases, placing the entire rows which contain the phrases in a new worksheet in the same file. Every row that does not contain one of the phrases I would like to place in another new worksheet in the same file.
I want to run this macro/add-in on multiple files.
I have a coloum of data which is currently updating of 4 digit numbers so something like col 1 would be 3423 col 2 would be 6543 and so on and so on. This list will be constantly growning is there a way to constantally sort these into numerical numbers lowest first? The data would be on coloum B the other sort range would be in coloum a.
I'm trying to write one macro that filters a set of data based on 2 criteria and then automatically sorts the filtered data by date. The filter works fine but when I then try to write code that handles the automatic sort, I get the following message when I run the macro: "Run-Time Error 1004: the sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."
I realize it would only take one or two more clicks to sort this data manully after the macro is run, but the lazy person in me is trying to eliminate this step. Does anybody have any suggestions as to why the sort is not working? I parsed basic sorting code, but it hasn't worked. The other part of this that is causing me trouble is that the number of filtered rows will vary, so I'm only needing to sort the visible range.
I have a spreadsheet that ranks the 500 components of the S&p 500 on an ongoing intraday basis. I would like to have the sheet sorted automatically as it is updated. I have very limited VB skills. Also, I heard there is a feature on excel 2007 that performs this function.
i have one excel sheet (refer attach) i give some example 7 to 8 customers, but in sheet nos. Of customer name a to z suppose to 3000 customer list. Now i want to create automatic customer alfa- numaric code.
Don't know if macro is better, but I would like to try it with function
I have 2 sheets.
Sheet 1 Column A: Name Column B: Points
Exemple: Name Points Mary 40 Johnny 20 Erick 60 Ralph 30
I want Sheet 2 to get data from Sheet 1 and sort by descending order automatically.
Then it should be
Sheet 2
Name Points Erick 60 Mary 40 Ralph 30 Johnny 20
I don't want to do it manually every time I change some value; I want it to do automatically; so if I change, for example, Erick's points from 60 to 10 in Sheet 1, Erick should appear on the last place in Sheet 2, with his new value (10)
a way, without using a macro, to have a specific column automatically sort data being pulled in from another column, so that even if the data from the initial column gets changed, that the destination column will take that change into effect and update the sort automatically?
I want to sort a column in Excel 2007 which is having dates. Normally, we use oldest to newest or newest to oldest option in sort & filter. But i am unable to find this option in my excel sheet. how can I find this option in Sort & Filter.
Note: I have imported this data column from Access 2007
I have more issues with this spreadsheet, which is beginning to drive me bonkers. Its a rank issue, with a few problems, questions:
Looking at the second table on the page, I want to rank the data for all colums except for column I in a descending fashion. IE - Highest to lowest. But column I needs to be ranked lowest to highest. I think I did this correctly, but I could be wrong.
Is it possable for the rankings to record equals? IE, the TO column has that, but in REB there should be another tie (9th and 10th) but there is not.
Further to this, the third table should have I28, and E35 listed as Ties. How do I do this for the entire table?
i have a list of names in column A and a corresponding score for each name in column S. on a stats sheet B10:B15 was trying to make a top 5 list using =LARGE(Week1!S15:S46,1) ... with Large i am able to get my top 5 from the week1 sheet but how can i assign the corresponding top5 persons from column A15:A45 in week1 to the stats sheet in column A10:A15
I have ranked the times of races for 30 participants. The results are in a column. I want to display the points given in the next column. The only points given are for 1st through 6th place. 1st=6pts, 2nd=5pts, 3rd=4pts, 4th=3pts, 5th=2pts, 6th=1pt, 7th place through 30th place =0. Times, therefore ranks, will change with each race, so this information needs to be in every cell in that column.
NAME TIME RANK PTS Joe 12.05 2 5 Mary 13.00 3 4 Sue 10.57 1 6 Sam 15.04 8 0
I figured out the rank formula, but I can't figure out how to assign the points.
