I have a problem ranking a large dataset(more than 30000 rows, 16 different columns need to be ranked). My problem is that I dont want the ranks to have gaps when there are ties.
See how it should be in table below.
Ext P$
Rank
Should be
2,128.34
1
1
[Code]...
I do have a working solution with an array formula similar to this, but it slows down my macro (30 minutes instead of 10 seconds) as I need it to calculate 16 times
Ranking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.
I am facing some problem with ranking formulae. Attached is a file illustrating them.
Problem 1: Ranking without zero I have a set of data for products. They have a number next to it. Some of the products have a ZERO value next to them. I want to rank the data in an ascending order while ignoring the products with ZERO value next to them.
Problem 2: Ranking on multiple factors I have a set of data for 50 products. There are 5 factors based on which I want to pick the best and the worst. Each factor has a value which can be a negative number, zero or positive number.
This is what I am doing presently: Ranking the products on each factor in a separate column. Points are assigned based on the ranking, i.e. if the ranking of "product A" on Factor 1 is 32, then it gets 32 points It is assumed that equal importance [weight] is given to each factor, thus average points is calculated based on ranking of each product on each factor. Average points are again ranked in descending order The top & bottom 10 are picked from this ranking.
Number of products and number of factors can be different for each analysis. Presently I am giving equal importance to the rankings for each of the factors, what if I have to give varied importance [weight] to the factors.
I have a list of data where some of the cell values are the same. I want to rank them uniquely but when I use the RANK function the cell values that are the same are given the equivalent rank. Is there any way of assigning the equivalent values a sequential ranking that is unique?
So in this example rather than producing the rank as this:
i am using this formula with military ranks as the primary value to look up. i know i am doing it correctly because it works with looking up values in another excel workbook when the rank is CPT, PFC, PV2, SPC, SGT, SSG, etc... but it does not work for any rank that is abbreviated with a number as the first character, such as 1LT, 2LT, or 1SG. instead i get a #N/A, which is false. i have looked all over the internet to include this website but cannot find an answer.
I am building a dashboard for a project, which is going to be on a separate worksheet for easy printing and providing only needed data from my database. I have attached a small example in the format I will actually be using as I think the formula is going to be specific to my dashboard.
Attached file, you should see a column "Rank" highlighted orange, with certain rows being numbered. My objective is to display certain data from that particular row, and display it (scroll to the right to see the dashboard display layout) on the dashboard. I think my main goal is to have the specified data displayed in ranked order 1-10 from the top down, and I don't know where to start with that....the other possiblity is to just have the specified data display in ascending order of the zip codes as long as something other than <blank> is in the "Rank" field.
I have a large table of data which is currently just in alphebetical order. I also have a blank template of the exact table format on another sheet in the same document.
I am trying to create a macro which will pull everything into the current blank table but ranks each row by the overall score (%).
The situation requires some context. I have a spreadsheet that is pulling data in from another location. It is simply a 'Location!A1' link - the source data updates daily as it is a Business Objects report that I dump out and I want to then bring that info into another spreadsheet.
My challenge is attempting to order the data as it comes in without having to manually sort the Business Objects report everyday. Because the link to the BO report is simply a cell look up, I have to take the data as it comes.
I've tried the rank formula according to a price (as this is what it needs to be ranked by) but because some products are the same price as each other, it simply doubles up (so if there's two products at $10, it ranks them both the same e.g. 4). Once I get the right rank, I can then vlookup the right order in a way I can then reference properly. EXAMPLE 180814.xlsx
I have a sheet diplaying 5 columns of percentages which I need to rank in descending order, i.e. assign rank 1 to the largest percentage and 5 to the lowest. See the attached sheet.
I'm creating a Speech and Debate calculater that presents winners names by place according to their score. If you look at Tab HSSE Results it will read the inputed information from HS Speech English. I want the names of the students to appear in the correct ranking order in the respected grade rankings chart. I currently have a countif formula which reads the grade, but I think I need to link that with an index formula which I'm having problems with.
I have a workbook which scores tender's, the calculations behind the front sheet work fine, I would now like to show where the tender ranks against a set of criteria. I have attached a sample and typed into the cell D12 what I am trying to with this list of criteria below starting in cell C15.
I'm having a problem with a list that I've created. The list is in cell A1, the base data for the list is in the range B1:B50. The problem is that data in this range is dynamic, i.e. it has formulas and depending on the result of these formulae the cells in the range either have a value or the cell is left blank. The problem this causes is that the list ends up having gaps in it because it uses blank cells as well. And this is despite me specifcally ticking " Ignore Blank " in the Data Validation menu where I'm creating the list.
I have around 2368 rows for in each column and I have around 8 columns and what I need to do is to remove any gaps. I do not know how to attach picture here, but I can explaining it in words.
A1: 0.9 A2: A3: A4: A5: -0.09 A6: A7: 0.4
Is there a way to eliminate those gaps (A2, A3, A4, A6...) in one go?
I'm wanting to do is drag a formula down and it drop to the next cell rather than the same row number I'm on. For example I'm trying to concatenate a list of phrases whilst changing the main word. Here's an example of the excel sheet
Base Terms Phrase Result car red van blue bus red blue
There is meant to be a space after the second red and blue enabling me to make (in order), red car, blue car, car red, car blue
How can I make it so I've done the relevant concatenate formulas for A2 with the B column and simply drag it down and Excel will switch from A2 to A3 and so on when I've dragged out the 4 formulas?
I have a file that contains addresses in column C. I need to find any gaps in addresses.
Ex: 211 Corbin Dr 213 Corbin Drive 214 Corbin Drive 123 Apple Drive 124 Apple Dr 124 Apple Dr 127 Apple Drive
I want to identify that there is a gap between 211 Corbin and 213 Corbin and 124 and 127 Apple Drive.
Currently the address including house number are in column c. However, I split the house number into a separate column and the street address in yet another column. There are also duplicates which I have identified by using conditional formatting to highlight the duplicates.
I have a sheet of over 40000 rows, I attach a sample. Column a is called dam and column b is called damsire. Each dam has only one 1 damsire. Both column a and column b are sorted ascending. Unfortunately there are big gaps in column b. some of these can be filled in as we have the information e.g. b38 and b39 should be Manila..as the row 37 tells you the correct damsire. Similiarly b49 could be filled in as shernazar. I want to create a new column which contains a formula to fill in these blanks. of course some of the blanks cant be filled in as the information is not there e.g. b23 to b28.
how I can format this timeline better (it was a to,e;ome template created by someone smart on this forum) - so that there isn't a huge gap between 1892 and then 1977... and then so the rest of the data isn't scrunched together.
I have a load of postcodes over 8 different tabs, the problem is the format of the postcode is wrong. I basically need to delete the first gap of each cell to make the postcode valid -
Is there a formula to count gaps? If you see the sheet below, I want to count maximum gaps in range A1:J12 and put that count in column L.
******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL12=ABCDEFGHIJKL1X XX XXX 22X X X 33 X X 64X X 85X X X X 26 X X 47 X X 48 X X 59 X X 610 1011 X X X X X 112X 9Sheet1 [HtmlMaker 2.42]
To see the formula in the cells just click on the cells hyperlink or click the Name box. DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am trying to get two shapes to butt up to each other. Unfortunately the shapes either leave a small gap or a slight overlay. I have tried using Ctrl + arrow key to move in small increments, but that didn't work. I have also tried adjusting the width of the rows, but the rows jump backwards or forwards to a number instead of staying with the number I entered. I want to create a seamless shape out of many different shapes.
I have what is probably a simple problem for most, but can't figure out what to do.
In the sample sheet attached, I have times in column E, and an action describing what has happened in column G. What I want to do is calculate the length of time between an opening action and closing one, but don't know how to go about it, as there can be an empty cell(and sometimes more) between each open and close.
The solution can be either in VBA or conditional formatting, if possible.I have product names on column A and weeks as from column B where I have the quantity sold. So, every week I'll have an additional column.
A B C D E ... Product Week1 Week2 Week3 Week4...
What I need:
If the cell is filled, highlight it in green.
If the gap (empty cells) between weeks is =1, highlight it in yellow
If the gap (empty cells) between weeks is >1 but <2, highlight it in orange
If the gap (empty cells) between weeks is >2, highlight it in red
The attached example better illustrates the needs : Example.xlsx
I get given a csv file on a monthly basis which contains consumption data per day for the specified period. This sounds simple but on occasion (more often than not) the data has missing days. This can cause me problem later on in my analysis.
I can happily total the monthly consumption using the date and month text. What i want to do however is to sort the csv file into daily consumption and highlight the missing days i.e. have a range of the days in the month and allocate the daily data to the correct date. I currently do this manually but know that there must be a better, automated approach... searching for matching dates for example?
In my head i'm thinking the following approach but lack the coding skills to do it.
1. Define the start and end dates. Perhaps count the number of days between the two dates and autofill the start date down the appropriate number of days in column A?
2. Paste the csv file into a different sheet and, starting from the top, cut and paste the csv data to the correct date created in step 1. Do this for each row based on the csv data.
I have been browsing here off and on, and have found many excellent answers. I use Excel to process data on time series, as an adjunct to consultancy work on statistical analysis of industrial data . Usually the data has irregular gaps, e.g., daily data might have 2-10 day gaps. If I want to take, say, 7-day averages, SKIPPING OVER gaps longer than 2 days(say), is there an easy way to do this (I don't really know VBA,and it is not worth my time to try and write long code for this, which will eventually be done by some professional programmers)!
