# RANK Duplicate Numbers Without Missing In Sequence?

Jul 18, 2014
Attempting to do a spreadsheet for my golf club, to record scores, etc...

Managed to get it to pretty much what I want despite some very extreme looking formulae! So for some scores such as 56,56,57,57,54,54, Rank would just give me 3,3,5,5,1,1, so I have put a second column next to 56,56,57,57,54,54 where I input scores to seperate ties (better back nine scores), so in the above I would have for example 30,29 next to the two 56s, 27,29 next to the two 57s, and 27,28 next to the two 54s, so the rank would return 4,3,5,6,1,2. I have gone three stages further, so if, the back nine score was also tied, I have a column for the last 6 hole score, the last 3 hole score and even the last hole score. This is some serious formulae at this point. However, I now want the OPTION to rank the original data as 2,2,3,3,1,1 if I don't put any data in the back nine column.

This is so that I can input doubles scores on the same spreadsheet as singles scores, so if two people shoot the same score in singles I can input the back 9 scores etc to decide who comes out ahead, but in doubles I would just leave the back nine score blank, and then two people would share 1st in the rank, but then I need two 2nd ranks NOT 3rd ranks as it currently is.

Just so you can see how complex it is the current forumlae in the rank is here is an example:

=IF(O7="","",IF(K$2="MEDAL",RANK(O7,O$7:O$255,1)

+SUMPRODUCT(--(O7=O$7:O$255),--(P7>P$7:P$255))

+SUMPRODUCT(--(O7=O$7:O$255),--(P7=P$7:P$255),--(Q7>Q$7:Q$255))

[Code] .....

There are other things in there to sort out a few other issues as well, but I cant see the wood for the trees so to speak, to get it todo what I want!

I have also attached a sample from my spreadsheet to better demonstrate what I want!

View 12 Replies
ADVERTISEMENT
May 27, 2014

I have a very big Excel file (62 MB). I need to be able to filter by one column (FACILITY) and sort the whole file using the (REGISTRATION NO) column as well as finding the missing sequence number in the (REGISTRATION NO) column. The problem is that the (FACILITY) column has more than 200 different facility name. I am thinking of macro as I have different Spreadsheet I need to do the same steps in each one separately.

I have attached a copy (example from the data that I have) .

View 14 Replies
View Related
Aug 15, 2008

I have a list of values (some are duplicates) which I need to rank.

The problem arises where I have more than one of any rank, the next rank skips a number. I need to have a list of sequential numbers and therefore can't have any numbers missing.

Example:

Value Rank Desired Rank

120 1 1

125 2 2

130 3 3

130 3 3

142 5 4

View 9 Replies
View Related
Feb 14, 2009

Un-Skipping the Rank after a duplicate value to get a regular Series!

Col A------Col B------Col C------Col D

Players------Scores------Rank------Answr

SACHIN------186------1------1

DHONI------183------2------2

KAPIL------175------3------3

HAYDEN------175------3------3

SEHWAG------167------5------4

BORDER------158------6------5

RAHUL------152------7------6

LARA------148------8------7

RICKY------148------8------7

KEVIN------142------10------8

BOON------133------11------9

FLINTFF------119------12------10

BOTHAM------112------13------11

I want to prevent the skipping of numbers while using the RANK function which skips the next number when it hits a duplicate value. I want to get the rank as well as not have the next number skipped.

View 4 Replies
View Related
Jun 18, 2009

Hi guys/gurls.. is there a way i can get the following check against range for orders which give result as invoiced(if within the range), duplicated(if 2 or more than 2 are on the orders list) & missing (which on not in the orders when checked against the range).

View 6 Replies
View Related
Oct 12, 2010

I have a list of numbers in sequential order. There are numbers missing. Is there a way to have Excel insert rows for the numbers that are missing in the series.

example:

1

2

3

4

8

9

becomes

1

2

3

4

8

9

View 7 Replies
View Related
Jul 14, 2013

I have two columns of numbers (this week - Column A and last week - Column B). What I need to do is look at the numbers and pull two lists out in Columns C and D. In columns C a list of the numbers that are missing from last week in column D numbers that are missing that were added from the previous week. The two lists consist of about ten thousand rows of data.

******** language="JavaScript" ************************************************************************>

Microsoft Excel - Book3___Running: 14.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp

(A)boutA1=ABCD1This WeekLast WeekMissingAdded

21002103110311002310271032103210274103310331034104051035103410361055610371036

165571038 81040 91055 101655 Sheet1 [HtmlMaker 2.42]

View 2 Replies
View Related
Jul 25, 2014

I'm trying to automate a sequence.

I was gonna explain it but I couldn't make myself clear so I took a screenshot:

sequence.PNG

folder_type is what the user types, (for example, the letter "A"), and folder_code is a sequence that should auto-generate according to what was manually inserted in the folder_type field.

What formula could I use for this process?

View 6 Replies
View Related
Aug 23, 2007

I have a task to identify all the numbers in sequence in a worksheet, how do I do that?

Ex:

Column A

1

2

3

4

5

7

9

10

11

12

13

14

15

16

20

21

22

Desired output 1-5,7,9,10-16,20-22.

View 10 Replies
View Related
Oct 21, 2005

I have cells O1 to O300 numbers in random sequences of no more than 10

I need to average the 5 highest numbers in each sequence eg:

O P

1 2 16 (result)

2 20

3 4

4 6

5 14

6 12

7 8

8 10

9 16

10 18

11 blank cell

12 15 9 (result)

13 3

14 6

15 12

16 9

17 blank cell

18 blank cell

19 4 23 (result)

20 16

21 20

22 40

23 8

24 11

25 28

26 blank cell

down to 300 rows, the sequence of numbers can be from 1 to 10 but never

more than 10

I got a function : =IF(COUNT(O1:O10>=10,AVERAGE(LARGE(O1:O10,{1,2,3,4,5})),"

") but that works only for 10 numbers and not for less

View 11 Replies
View Related
Dec 19, 2008

There are 350 numbers in a column, 7 digits in each cell. I want to see which cells have 3 or more consecutive numbers. Only looking at the last 3 digits. Example. 1960657. 1960658. 1960659. 1960660. Has 4 consecutive numbers.

Conditional formatting ? or other?

View 9 Replies
View Related
Jul 29, 2009

I am trying to "auto fill" a column with numbers in sequential order. (1,2,3,4,5...) I typed in 1,2,3... in the first cells and clicked on the black corner dot to drag the sequence down. It keeps putting in 1,2,3,1,2,3,1,2,3..... I have also went into the advanced options and clicked the "enable fill handle and cell drag-and-drop" and also Alert before overwriting cells" and it still fills in 1,2,3,1,2,3....

View 9 Replies
View Related
Dec 8, 2006

I have a list of random numbers in column A the number range is from 0 to 20,000. What I am trying to do is to count the sequential numbers after each random number....sorry this is not really explained well so I willl use a numeric example.

A

23

24

25

33

34

60

77

80

Above is what I have in column A you can see that my number range starts a 23 so I would like to count the sequential numbers that occur from 23 in this case it would be count(23,24,25) giving the answer 3 I would like this answer to be inserted into column B next to 23 and the numbers that were counted in this formula then be deleted as rows (i.e. where the number 24 and 25 was counted then the rows containing these numbers are deleted), The macro moves then to count the next number.

In the case of just a single number then a value of 1 is entered into the column next to the number indicating that there was only one number in that sequence.

View 9 Replies
View Related
Jun 6, 2014

I was wondering whether it was possible to search for a sequence of numbers.

For example, if this is down a column:

A1: 5

A2: 4

A3: 5

A4: 6

A5: 9

Is it possible to have a find function to search for the sequence of numbers "4 + 5" and consequently highlight cells A2 and A3?

View 5 Replies
View Related
Nov 25, 2012

I have a table that receives the number sequence 1 and sequence number 0.

I need to calculate the highest sequence numbers that repeat in Secunia lines.

A

B

C

D

E

F

G

H

I

J

K

L

Result

1

1

1

0

0

1

0

1

0

1

1

0

3

[code].....

See for example the last line.

1 1 0 0 1 0 1 1 1 1 1 0 = 5

I have a sequence number 1, where it repeats 5 times segudamente.that is greater than other sequences.

View 3 Replies
View Related
Jun 24, 2009

I'd like Excel to run a check to see if a row of numbers runs in sequence.

*As in row 4, the numbers dont have to incriment each time

*As in row 2, there may be gaps in the data

*As in row 6, the data cant increase and then decrease. Each subsequent number must be the same or high than the previous

*There could be varying amounts of numbers per row, hence the results being in column R

As an added bonus it'd be nice to see where an error occurs, but just knowing theres an error (column R) is the primary goal.

View 8 Replies
View Related
Jun 27, 2012

Basically I'm trying to look up a series of numbers against a separate row of numbers and look for a match regardless or number order.

For example

If you look at the above picture I'm trying to do a query of some sort that will look up the numbers in A8:G8 in then search each row in the above table ie look for the numbers in B1:J1, B2:J2,B3:J3 etc I need to be able to search each row and look for the sequence of numbers regardless of order, if there is or inst a match for all numbers it should look at the next row and so on (maybe multiple matches). If there is a match then it should display the Name located in column "A" into cell G8. In this example to Jarrad row contains the numbers located in A8:G8. If there is no match it should display "None".

I'm trying to find any easy way to do this as I have over 500 rows I'm trying to query. The number's in A8:G8 in this example could also be more or less, ie here I have included 6 numbers but this could be 3 or 9 etc.

View 2 Replies
View Related
Dec 24, 2013

I'm use to highlighting two cells, find the plus, and copying a list of numbers down about 10 rows. Is there a macro instruction or VB instruction that will do the same?

Example,

Before:

G1010

G1011

After:

G1010

G1011

G1012

G1013

G1014

G1015

G1016

G1017

G1018

G1019

G1010

G1011

View 4 Replies
View Related
Dec 13, 2008

I would like to place in sequence increasing the numbers of one I creak in a cell.

In the formula I determine it I creak and the corresponding frame number to the placed ones.

The problem is that he is accumulated only the greater and not sequencia it.

1803456781101112

1134567810111280

but,80-80

Function ordenar2(Myrange As Range, num As Integer) As String

Dim Myorder As Double

Dim X2 As String

Dim n As Integer

n = 1

Do While n

View 9 Replies
View Related
Oct 10, 2006

I am setting up a spreadsheet with two columns. The first column will contain a persons name the second column will contain four numbers.

Name Numbers

Joe 1,3,12,24

John 4,12,23,24

Jill 6,14,19,26

I need to be able to search to find the following: the correct sequence of numbers e.g. if 1,3,12,24 were pulled out then that search is easy. If I was looking for someone who had the numbers 1,12,24 then I have a problem. I was thinking about putting the four numbers in to four separate columns but my Excel knowledge is poor.

View 4 Replies
View Related
Mar 7, 2013

I have 2 columns,

column A has a series of 8 digit numbers (some will match some will not) sorting in A-z order from lowest to highest value.

Column B is blank at the moment.

What I want

In column B i need to add numbers starting at 1 and moving down in sequence.

If more than one rows have the same number in column A they get the same numbe rin Column B

It's really a pain or have to hand type in the numbers and I can't figure out a easy way to do this.

Attached example.

Tab 1 = Origanl Data

Tab 2 = results i'm lookin for.

View 2 Replies
View Related
Mar 24, 2009

I'm a basic excel user really and cannot find a function that will allow me to complete the following:

CELL A1 includes the following sequence of numbers: 1,2,1,0,2,3

I would like to know how to put a formula/function into CELL B1 that sums up the sequence of numbers in CELL A1 (i.e 1+2+1+0+2+3)

View 14 Replies
View Related
Nov 12, 2012

I have a document that is a subject index for book that has terms that reference page numbers. The structure is for example: Employment, 587, 592, 553, 605, 233 The term is always following by comma and then space and listing of page numbers that subject reference applies. In many cases the page number references are out of sequence and I need them to be in numerical sequence. i.e. Employment, 233, 553, 587, 592, 605 There are many subject terms in index and need to check and fix sequence of page references for each.

View 6 Replies
View Related
Jun 27, 2013

So I have

BI Raw:..... 500.....480.....570.....540.....480

TI Raw:........88.......80.......51.......22.....122

BI Rank:........3.........4.........1.........2........5

TI Rank:........2..........3........4.........5........1

The Raw rows are calculated then each given a rank. the highest is 1.

In case of a tie in the BI Raw then the lower TI Raw wins.

Also there can be up to 15 numbers in the row

So how would i assign the Rank either using a cell formula or VBA code?

View 2 Replies
View Related
Jun 1, 2008

Without sorting the original field, I am attempting to compare the values in column 1 and then assign a "place" value in column 2 ( cell) next to each value compared starting with 1 (least value) thru n ....until all values have been compared.

Column 1 Column 2

52.1 7

47.4 3

50.0 4

100.0 9

45.3 1

46.3 2

51.1 6

50.5 5

52.2 8

View 4 Replies
View Related
May 26, 2008

this is exactly what i am looking for also, can you please explain how i can use that code in 2007? I have attached a list of the numbers i'm working with, please can you have a look for me?

I've tried entering it ion visual basic but i keep getting:

COMPILE ERROR

INVALID OUTSIDE PROCEDURE

View 9 Replies
View Related
Feb 7, 2012

In column A, I have numbers from 1000 to 8600.

I want to identify (in a separate worksheet is fine) what numbers are missing inside the ranges 2500-2599, 3100-3399 & 3700-3799.

View 2 Replies
View Related
Nov 24, 2013

I have a 5x5 table which is filled by random numbers ranging from 1 to 99. I need to make a function that shows me all the numbers that are missing between 1-99.

View 1 Replies
View Related
Apr 17, 2008

I have a file with approx 3600 rows. In Col B there is a 13 digit EAN-13 code. I would like to find out which numbers are missing. I have to compare each cell from top to bottom in the B-column to the cell below and find out if there are some missing numbers. If the numbers are incremental then goto next cell. But if there's a gap in the numbers then it should insert the missing numbers with an increment of 1.

I think the logic statement should look like this:

i = 1

For x = 1 to last row

If the gap between B(i) and B(i+1) < 1 then

Insert a row and put the missing value in the cell on the new row.

Endif

i = i+1

Next x

The comparison has to be made from the 5 digits in place 8-12 in the code.

Here's an example (i've dimmed the digits that shouldn't be compared):

7330985920714

7330985920721

7330985920738

7330985920776

As you see there's a missing number between the third and fourth row. Here the value should be 733098592074 + the checksum that is a bit tricky to calculate.

Between the fifth and sixth row there are four missing numbers that has to be filled in.

The checksum is OK to leave out at this moment. The important thing is to get the 5 digits.

View 9 Replies
View Related
Jun 18, 2007

Excel 2003. As I entered 720 names/addresses in a spread sheet, I stopped and started repeatedly. When done, the row numbers go from 1 to 728. Puzzled, I carefully reviewed all names and the row numbers assigned. Row numbering skips eight numbers, 432-439. How do I get Excel to reassign the row numbers so they reflect accurately the number of entries?

View 2 Replies
View Related