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?
PS I'm using Excel 2007.
View 3 Replies
ADVERTISEMENT
Jan 23, 2012
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
View 4 Replies
View Related
Jun 16, 2014
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.
View 2 Replies
View Related
Jan 4, 2014
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.
DAILY TRAIN RUNNING.xlsx
View 3 Replies
View Related
Jun 25, 2014
I have a challenge trying to get excel to recognize yearly intervals.
Let me explain further:
Interval Percentage Yr1 Yr2 Yr3 Yr4 Yr5 Yr6 Yr7 Yr8 Yr9 Yr10 Yr11 Yr12
3yrs 10%
I want excel to insert the 10% into the cells every 3yrs.
I have attached a dummy workbook detailing my issue
Interval problem 1.xlsb
View 2 Replies
View Related
Oct 24, 2011
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.
View 3 Replies
View Related
Apr 20, 2012
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.
View 6 Replies
View Related
Apr 1, 2013
how to soft vaue of date in excel 2007. only arrange by date (not including month).
for examples:
it like that:
13/07/2012
15/01/2012
18/04/2012
View 1 Replies
View Related
Nov 17, 2011
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).
View 8 Replies
View Related
Nov 20, 2013
I'm using Excel 2007.
Is there a way to write a formula for the below?
Basically i want Excel to remove the duplicates in column D and show the results in column E (so my formula will start in cell E4)
Excel 2007DE3DATARESULT4BRAND1BRAND15BRAND2BRAND26BRAND1BRAND37BRAND38BRAND39BRAND210BRAND111BRAND3Sheet3
View 2 Replies
View Related
Jul 24, 2014
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 am using Office 2007.
View 1 Replies
View Related
Oct 22, 2011
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.
View 4 Replies
View Related
May 23, 2012
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)
[Code] .......
View 2 Replies
View Related
Mar 16, 2014
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.
strbody = "Hi," & vbNewLine &_
"abc." & vbNewLine &_
"def." & vbNewLine & vbNewLine & _
"ghi" & InputBox("Enter Response date (dd/mm/yyyy)") & vbNewLine & vbNewLine & _
"jlk." & vbNewLine & vbNewLine & _
"lmn" & vbNewLine & vbNewLine & _
"ABC" & vbNewLine & vbNewLine & _
"XYZ" & vbNewLine & _
[code]....
View 3 Replies
View Related
Jul 24, 2012
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.
View 7 Replies
View Related
Jun 20, 2013
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.
Sub Inbound()
ActiveSheet.Cells.EntireColumn.Hidden = False
ActiveSheet.Cells.EntireRow.Hidden = False
Application.ScreenUpdating = False
[Code] ........
Excel 2007/Windows 7
View 1 Replies
View Related
Dec 3, 2012
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.)
Excel 2007
A
B
C
D
E
F
1
Item Number
Description
Unit
Price
Qty
Total
2
BX-2B1324X
0.9% NaCL 1000ml Bags
CS
$23.52
5
$117.60
[Code] ....
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.
View 5 Replies
View Related
Jun 21, 2007
I am running Excel 2003.
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.
View 9 Replies
View Related
Jul 27, 2006
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.
View 3 Replies
View Related
May 24, 2007
Is this possible and if so, how do you do it?
For example, a team scores 10 points and moves up three places. Easy to do manually with sort but what about automatically?
View 9 Replies
View Related
Mar 21, 2007
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.
View 9 Replies
View Related
Jan 11, 2008
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.
View 7 Replies
View Related
Dec 12, 2009
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.
View 2 Replies
View Related
Mar 15, 2014
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)
View 1 Replies
View Related
Jun 17, 2014
Column A = Product code
Column E = Expiry Date
How to make macro code automatic sort by expiry date (oldest to newest) then product code (A to Z)
After i put new item in product code & the expiry date...
I like to auto sort every time i put new item in next row.. and the result will be ber25 1/20/2015 will be on top of ber25 1/21/2015
Code
Description
Beginning
U/M
Expiry Date
Act10
Activo 10K
1
Bag
1/20/2015
[Code] ..........
View 1 Replies
View Related
Mar 6, 2008
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?
View 9 Replies
View Related
Jun 4, 2014
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
View 9 Replies
View Related
Mar 24, 2009
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?
View 12 Replies
View Related
Nov 17, 2007
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
View 9 Replies
View Related
Apr 14, 2006
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.
View 9 Replies
View Related
