Ranking With VBA - No Gaps Between Ranks

Feb 7, 2014

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

Code:

=SUM(1/COUNTIF(A\$2:A\$35000;A\$2:A\$35000)*(A\$2:A\$35000>A2))+1

I was thinking of using a for next loop to rank sorted columns but I dont know how to set it up properly.

Ranking Values And Ranking Duplicates The Same Rank

Mar 30, 2014

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.

Example:
20
18
18
15
12
11
11
9

The ranking I am looking for is:
1
2
2
3
4
5
5
6

Ranking Without Zero And Ranking On Multiple Factors?

Aug 12, 2014

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.

Unique Ranks Function

May 10, 2007

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:

100 1
99 2
98 3
98 3
98 3
97 4
96 5
95 6

It would produce it as this:

100 1
99 2
98 3
98 4
98 5
97 6
96 7
95 8

VLOOKUP With Military Ranks

Oct 22, 2008

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.

Complicated Data Lookups / Ranks

May 3, 2013

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.

Example.xlsx‎

Macro Which Ranks Table Of Data

Oct 7, 2008

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 (%).

Rankif Returning Sequential Ranks

Oct 18, 2011

Here is an example of my data and desired results:

A B C
[Material] [Receipt Date] [Order Received (Desired Results)]
1 8/1/11 1
1 8/1/11 1
1 9/1/11 2
2 8/2/11 1
2 8/5/11 2
2 8/5/11 2
2 9/1/11 3

I would like to identify the order in which each "material" was received but I would like the ranks to be sequential.

I am aware of the:
=SUMPRODUCT(--(\$A\$2:\$A\$8=\$A2),--(E2>E\$2:E\$8))+1
to return the rank but I would like to make them sequential...

One last request... I would LIKE to not use array functions if possible, however I will if I have to..

Formula - (-) Shows Up And Ranks This As The Number One

Mar 16, 2007

On cell K7, i have this formula:

=IF(ISERROR(I7-J7),"",I7-J7)

On cell L7 i have this formula:

=IF(ISERROR(RANK(K7,\$K\$7:\$K\$29,0)),"",(RANK(K7,\$K\$7:\$K\$29,0)))

The issue is that in cell K7 a dash (-) shows up and ranks this as the number one, even when i have no data in I7 and J7.

MTD Conv
Conv Goal
B/W Goal
RK
-
1

29.3
31
(1.70)
12

21.8
25.0
(3.20)
15

Formula That Ranks Dynamic Unsorted Data

Aug 18, 2014

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

Ranks Columns Of Figures In Decending Order

May 15, 2009

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.

Formula That Reads Grade And Then Ranks These By 1st-5th Place According To Score

Feb 10, 2014

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.

Multi Conditional IF Function: Show Where The Tender Ranks Against A Set Of Criteria

Nov 12, 2009

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.

Gaps In A List

May 13, 2006

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.

Sequential Numbering With Gaps

Nov 10, 2007

I have a column in which I enter a date, and an adjacent column which automatically enters a sequential number, using ...

Delete GAPS In Columns In One Go

Feb 29, 2012

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?

Drag Formulas With Gaps?

Mar 13, 2014

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?

Find Gaps In Sequence

Dec 31, 2008

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.

Fill In Of Text Gaps

Mar 21, 2007

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.

Stop Gaps In Timeline

Jul 17, 2007

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.

Delete Gaps In Cells (postcodes)

Jul 20, 2009

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 -

DL 7 9
DL 8 1
DL 8 2
DL 8 4
DL 8 5
DL 9 3
DL 9 4
DL 10 4
DL 10 5
DL 10 6
DL 10 7
DL 11 7

You see I need to have it like DL7 9, or DL10 7, but i'm not sure how, I've attached the file so you can have a look.

Removing Gaps From Data Extraction

Nov 13, 2009

I'm doing some simple data extraction, e.g.
A B
1 bob 3
2 mandy 4
3 charlie 6
4 dave 1
5 steve 5

So I had in c1 to c5 = =if(b1 > 3, a1,"") autofilled, which works fine, but I end up with,

gap
mandy
charlie
gap
steve

how would I get,

mandy
charlie
steve

also is it possible to have an if statement in 1 cell change the value of another cell?

e.g.
in a1
if(b1>5, c1="yes",c1="no"), can't seem to get it to work

How To Count Maximum Gaps In Range

Feb 1, 2012

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.

Align Shapes No Overlap Or Gaps

Nov 20, 2006

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.

Gaps In Cells When Calculating Formulas

Jan 2, 2008

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.

Rolling Time Formula - Possible To Fill In Gaps

Jul 30, 2014

I have attached a work sheet where I have part of a formula working.

Although what I am trying to achieve is in the example in column B.

It is possible to fill in the gaps as in column B with the task between the time frames.?

Count Gaps Between Values And Highlight Cells?

Jul 17, 2014

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‎

Fill In Gaps - Missing Days In Range

Mar 29, 2012

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.

Handling Gaps In Time Series Data

Dec 21, 2006

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)!

Leave Gaps For Blanks & Zeros In Chart

Nov 21, 2006

Instead of treating cells with a blank or a text value as zero in a line graph, how can I create a gap in the line?