# How To Get The Longest Sequence Of 3s

Apr 2, 2012

How can I get the Longest sequence of 3's. E.g.

CA
1
2
3
5
3
3
5
4

## Count Longest Run Of Numbers Above Mean

Jun 12, 2008

I'm attempting to summarise several hundred control charts.

One thing I'd like to do is be able to put in a formula to count the maximum number of successive entries that are all the same side of the mean.

Another related thing is to be able to count the longest run where successive values are the same.

## Displaying Longest String In Column

Mar 13, 2014

How I can show the longest string in a column, I've tried to find a formula that does this but it only shows the number of characters, not the actual string contents.

## Find The Longest Run Of Zeros In A Column

Nov 10, 2008

I have a list of dates in column A (sequential from low to high).
I have corresponding rainfall data in column S.

I am trying to find the longest run of 0's in the rainfall data and return the start and end dates. It would be great if i could tell it (in cell C8) to find the longest run below this number.
ie: i say i want to find the longest run of numbers below 5.

## First And Last Date In The Longest Continued Range

Jul 16, 2008

I am looking for a formula that returns the date of the first and last value in the longest continued range.

In the example:

CD30CLA3101/02/200833201/03/20086
3301/04/200853401/05/20083501/06/200863601/07/200823701/08/2008

The two formula should return the two dates in blue.

## Extend Range To Longest Column

Aug 7, 2007

I had asked about automatically naming regions and this is an extension of that post because it's closed. The code we ended up with to name the region is:
Dim sNm As String, sRT As String

If Intersect(Target, Rows(1)) Is Nothing Then Exit Sub '------------------->
If Target.Count > 1 Then Exit Sub '---------------------------------------->

sNm = Replace(Trim(Target), " ", "_")
sRT = "=offset(" _
& ", 1, 0, counta(" _
& Cells(2, Target.Column).Resize(Rows.Count - Target.Row).Address & ") )"

My problem now is that I need to have the regions be the length of the longest column. I've tried using a few different ways using the worksheet range but I can't seem to get it to work.

## Count Longest Run Of Contiguous Zeros

Jun 18, 2008

I am trying to figure out how to create a formula or VBA to count how many contiguous 0's there are in a specific row...and then drag this formula down many rows..for example - if a row of data contains 1,3,0,0,0,4,5,0,0,0,0,0,0,0,5,3,1,0 I want the result to be 7, because 7 is the longest streak of contiguous 0's.

## Longest Series Of Consecutive 2 Chars In String?

Aug 23, 2014

I'm looking for a formula which returns the longest consecutive series of occurrences of 2 chars in a text string.

In the case I'm trying to count the longest consecutive number of nine-spares (9/) in a bowling game.

For example:

|7/|X|9/|9/|9-|9/|9/|9/|9/|X8/|longest series of 9/ would be 4.
|9/|9-|9/|9/|7/|9-|(8)/|X|7/|9-|longest series of 9/ would be 2.
|9/|(8)1|X|X|72|63|7/|8/|8/|9/9|longest series of 9/ would be 1.

## Find The Longest String Including A Variable

May 22, 2007

I have a database application which appends an "A" to the sequence number each time an entry is amended and then posts it as a new row in the database. This means there can be multiple entries with the same number but with different amount of "A"'s afterwards.

e.g.
Cell A6 contains seq no 1
Cell A7 contains seq no 2 - Seq no 1 is subsequently amended so...
Cell A8 contains seq no 1A - 1A is subsequently amended so...
Cell A9 contains seq no 1AA - and so on and so forth..........

What i am trying to achieve is a formula which will look at the record number which is required (i.e. 1) and will search for record 1 with the most number of "A"'s appended. This way the user will only be amending the most up to date record.

The formula i have tried (but doesn't work) is:

=INDEX(A6:A65536,MATCH(B2&MAX(LEN(A6:A65536)),A6:A65536,1),0)

(B2 is the record the user wants to amend)

## VBA Code To Detect Longest Cell Containing Data And Copying Whole Range

Feb 9, 2014

I am trying to do the following with VBA.

What i need is to find out the last cell with data and then selecting the whole range and copying it.

As you can see, the column with the "longest" data range is B9,C9,D9,E9.

I need a VBA code to detect which Column has the longest Data and from there copy the entire range.

Hence, in this case, the range to be copied is From A2:J9.

Column A
Column B
Column C
Column D
Column E
Column F
Column G
Column H
Column I
Column J

1
2
ttt
rrr
m
vvv
gg
ff
fff
fff
fff

[Code] ..........

Hence,in this case, the range to be copied is From A2 to J14.

Column A
Column B
Column C
Column D
Column E
Column F
Column G
Column H
Column I
Column J

1
2
hjhjh
ghj
gh
ghj
ghj
ghj

[Code] ..........

## Min And Max In A Sequence

May 12, 2006

I have in column " A" 500 rows with numbers in random sequences each
sequence has random number of rows , each sequence is seperated with a blank
cell (the result of a formula) some sequences have zerrows included.
I need to find the lowest number in each sequence and put that number in
column

" B" I also need to find the highest number and put that number in column "
C"

eg:- A B C
4 2 10
6
2
8
10
blank cell
7 7 50
9
11
13
50
18
21
30
15
blank cell
3 1 17
5
0
1
0
0
17
6

some sequences have two or more of the same numbers , in case of two ore
more of the same numbers only one is needed

## Letters In Sequence

Jun 24, 2009

I would like to create a column with letters from alphabet in a sequence. If I write A and in cell below I put B then highlight the two cells and drag down I get a repetition of A and B. How do I get the following alphabet letters ie. C,D, E etc.?

## Get The Next Number In A Sequence

Aug 4, 2009

I have code that adds the content of a userform directly onto the next available row starting at column B, using:

## Next Number In Sequence

Nov 10, 2009

I have Excel 2003. I am trying to find the next number in a sequence of numbers. The number range is 1-59, and the sequence is 89 numbers that go like:

1
5
8
3
7
10
6
2
5
3
8
11
41,...(to the 89th number)

## Sum Sequence On Certain Number

Apr 16, 2012

Need to find and then sum the sequence on a certain number. Using the number 1 in the example.

Example:
0
2
1
2
1
1
1
4
3
2

## 15 Digit Sequence

Mar 31, 2009

My question is, a formula was provided to me that add's a comma and a space after a 15 digit sequence of numbers, that formula is:

=IF(MID(C378,LEN(C378)-1,1)="W",C378&" ,",C378)

And what that does is it would change data in a cell that looks like this
01-02-034-05-W2 TO 02-03-045-06-W2

To this:

01-02-034-05-W2 TO 02-03-045-06-W2 ,

The formula works great, thanks to the help of the posters!

However I just ran into an issue, what if I have a set of data in a cell that looks like this:

01-02-034-05-W2 TO 02-03-045-07-W2
02-02-034-05-W2 TO 02-03-045-07-W2
03-02-034-05-W2 TO 02-03-045-07-W2

And I need a space and a comma at the end of each sequence, so the above would change to this after the formula is applied:

01-02-034-05-W2 TO 02-03-045-07-W2 ,
02-02-034-05-W2 TO 02-03-045-07-W2 ,
03-02-034-05-W2 TO 02-03-045-07-W2 ,

Now the data in the cell is wrapped so it lists one set of numbers, then the next set, then the next set as seen above.

## Fibonacci Sequence

Jan 5, 2008

I'm trying to write some code to calculate the value of a Fibonacci Sequence, at a random point in the sequence.

For example if I have 5, 15, 30, 50, 75, 105... and my random value is 3 it would return 30.

Being new to VB the only way I've of thought of to do this is a massive if then statement.

## Automatic Numeric Sequence

Jan 15, 2013

I have a dynamic worksheet containing a variable number of rows that I would like to have automatically numbered when I run my VBA script. I used Dave Hawley's code as follows, with a modification to begin at cell A2, but it doesn't seem to work, as it only numbers cell A2 and not the rest.

VB:
With Range("A2")
.Value = 1
.AutoFill .Range("A2:A" & RowTotal), xlLinearTrend
.Range("A" & RowTotal + 1).Clear
End With

RowTotal is just a variable that holds the results of a simple last row finder. How to make this work?

## How To Automate Sequence Of Numbers

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?

## Identify Numbers In Sequence

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.

## Auto Fill Sequence

Sep 11, 2007

I am fighting with this for a longer time now and can't find a solution:

is it possible in Excel to generate a number sequence (to create a list for auto fill) representing numbers like this:

000001
000006
000002
000007
000003
000008
000004
000009
000005
000010
000011
000016
000012
000017
000013
000018
000014
000019
000015
000020

and so on till 001000

## Create An Alphanumeric Sequence

Sep 14, 2009

I want to create a 4 character alphanumeric sequence. The sequence is to be like this: a1a1-z10z10. If it's a number, increment 1 until you reach 10, then move to the next column, if it's a letter, increment a until z, then move to the next column. Excel's autofill feature doesn't seem to work well for me due to the letters involved, so if someone has a macro handy that would be great.

## Formula To Identify A Sequence?

Nov 29, 2012

formula that will produce the number sequence as shown in the third column of the table. The sequence starts at 1 and if the colour changes then the number is 2 and if the colour changes again then the number is 3 and so on. The part I'm having difficulty with is that if the name in the first column changes then the sequence must reset to the number 1 and become 2,3 etc. as above when the colour changes.

Peter
Blue
1

Peter
Blue
1

[Code]....

## Conditional Formatting In Sequence (every Ten 0 Zero)

Feb 22, 2013

I need to create a Conditional Formatting in a sequence (every ten 0 [zero])

## Entering Cells In Sequence

Mar 12, 2014

I have a column of values, and I want a column next to it that shows the value in every second cell in the first column, but right underneath. See below for an example of what I am trying to achieve. The actual column is 17000 cells so I cant do it manually.

12
24
36
48
510
612
7
8
9
10

## New Worksheet With Sequence Number

Jun 6, 2014

I have a problem to name correctly my new worksheet in order to obtain a sequence.

So my first worksheet' name is 3114

My macro is : [Code] ..........

And I don't know what I have to put after Name = ... to obtain the sequence, what means that the next worksheet is 3115, the next one 3116 etc.

I tried with Worksheet.Count but it puts the number of sheet starting from 1 ...

## Change Find Sequence

Mar 28, 2009

I want to change the sequence of find for the below code. I want to find the last entered row, instead of first entered. Now this code find first entered row.

## Average Numbers In Sequence

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

## Instances That Occur In A Sequence

Mar 14, 2008

I have a problem, I have a formula which counts the number of instances that occur and assigns the value as 1 for every instance, however I want the formual to also recognise that if a number of instances occur in succession a value of 1 should also be assigned.

E.g. if a person is absent for 1 day the formula assigns a value of 1
if a person is off for 3 days in succession the formula assigns a value of 1

## Copying Formula Down In Sequence

Dec 11, 2012

I have two sheets in excel, one contains a list of names and the second is a basic lookup sheet for mail merge purposes.

Basically I want to write a formula in column A (of sheet 2) [current formula is =LabelNames!A2] and in column B the formula is =LabelNames!A3.

I want to drag these formulas down so each one jumps by 2... Ie:

=LabelNames!A2
=LabelNames!A4
=LabelNames!A6