Can excel find a sequence of numbers from a known total?

E

efandango

Can excel find (and highlight) a continuous sequence of numbers using a known
total as reference from the following list of numbers?

the answer is 68.

8
6
3
2
6
10
9
4
12
8
6
1
8
10
8
14
10
9
12
12
14
6
4
3
4
4
4
0
6
10
4
9
6
3
11
12
10
7
12
8
8
 
R

Ron Coderre

Not sure where you're going with this, but....
using your posted list and looking for consecutive values that sum to 68....

A1 8
A2 6
A3 3
A4 2
A5 6
A6 10
A7 9
A8 4
A9 12
A10 8
A11 6
A12 1
A13 8
A14 10
A15 8
A16 14
A17 10
A18 9
A19 12
A20 12
A21 14
A22 6
A23 4
A24 3
A25 4
A26 4
A27 4
A28 0
A29 6
A30 10
A31 4
A32 9
A33 6
A34 3
A35 11
A36 12
A37 10
A38 7
A39 12
A40 8
A41 8

These ranges total to 68:
A1:A10
A6:A14
A18:A26
A21:A32
A23:A35

Assuming it could be done, how would you want Excel to handle all of that
with highlighting?

***********
Regards,
Ron

XL2002, WinXP
 
C

Chip Pearson

If I understand your question properly, you want find a contiguous range of
cells that adds up to a particular number, 68 in your example. The following
code will search column A looking for a series of contiguous cells that add
up to the value in cell B1. It will scan until either the answer is found or
a blank cell is encountered in column A, in which there is no series that
adds up to Answer.

Sub FindSeries()

Dim StartRng As Range
Dim EndRng As Range
Dim Answer As Long
Dim TestTotal As Long

Answer = Range("B1") '<<< CHANGE

Set StartRng = Range("A1")
Set EndRng = StartRng
Do Until False
TestTotal = Application.Sum(Range(StartRng, EndRng))
If TestTotal = Answer Then
Range(StartRng, EndRng).Select
Exit Do
ElseIf TestTotal > Answer Then
Set StartRng = StartRng(2, 1)
Set EndRng = StartRng
Else
Set EndRng = EndRng(2, 1)
If EndRng.Value = vbNullString Then
MsgBox "No series found"
Exit Do
End If
End If
Loop
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
H

Herbert Seidenberg

There are 6 ranges....Ron.
Chip highlights only one.
This array formula will show all of them:
=IF((SUM(INDEX(Bin,colm):INDEX(Bin,rowm))=68)*(rowm>colm),colm&","&rowm,"")
if put in a 41x41 array.
Bin is the given set of numbers,
rowm is a vertical set of numbers from 1 to 41,
colm is a horizontal set of numbers from 1 to 41.
 
E

efandango

Hello Ron,

I want to use this to error check a date range control in MS Access. The
control is calculating no of total days an event occurs, I have quite a few
controls to check, and given that I have a large number of date ranges to
check through, finding the 'from' - 'to' contigeous spans is a nightmare, so
if i can just chuck the column of figures in (that relate to an adjacent
column of dates), and get Excel to highlight the sequence(s), it should make
the error cheking easier and foolproof.

make sense?
 
E

efandango

Hello Chip,

That sir, is fantastic!. it did for me in the blink of an eye, what I have
previously spent an eye-straining excessive amount of time on...

thank you

much appreciated.


regards

Eric
 
E

efandango

Hello Herbert,

Thanks for your response, Chip has solved the problem for me, in so much as
I am able to hit the button an it highlights the first (and likely
signigicant) range I am looking for.

But, I am intrigued by your solution, is it similar to those kind of word
puzzle grids where you have to find the hidden words in any orientation?


kind regards

Eric
 
C

Chip Pearson

Eric,

It was an interesting little diversion from the usual questions in the
newsgroup. It was fun to write. I put the solution up on my web site, on a
page describing how to fill missing entries in a series of numbers. The two
problems are only tangentially related, but I couldn't think of any other
relevant page on this on which to include it.
http://www.cpearson.com/excel/InsertAndFill.htm#SumSeries .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
S

Sandy Mann

Hi Chip,

May I ask what type of *offset* you are using in your code - or point me to
somewhere that I can look it up for myself.

What I mean is that your:

Set StartRng = StartRng(2, 1)

looks like it equivelant to:

Set StartRng = StartRng.Offset(rowoffset:=1, columnoffset:=0)

or Set StartRng = StartRng.Offset(1, 0)

What really surprises me is that

Set StartRng = StartRng(0, 0)

seems to be the StartRng minus one row and minus one column which is
definitely non-intuitive, (at least not the way that my mind works)

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
C

Chip Pearson

Sandy,

This type of offsetting doesn't use a 0-based index as does the Offset
property. Instead, it actually calls the default method of the Range object,
named _Default, which uses a 1-based index. Code like

Debug.Print Range("A1")(2,2).Address

is really shorthand code for the more explicit

Debug.Print Range("A1").[_Default](2, 2).Address

_Default is a hidden member of the Range object, so you won't see it in the
Object Browse unless you enable "Show Hidden Members". Once you do this,
you'll see that it is indeed defined as the default method of a Range.

Since [_Default] is 1-based, and Offset is 0-based, the following statement
are functionally equivalent:

Debug.Print Range("A1").[_Default](2, 2).Address
Debug.Print Range("A1")(2, 2).Address
Debug.Print Range("A1").Offset(1, 1).Address

Whether to use the default method or to use Offset is a matter of personal
preference and style. My only recommendation is that you choose one and
stick with it. Don't mix references using _Default with references using
Offset. They won't conflict with one another -- they play together just
fine -- but mixed reference styles may be confusing and might make
debugging/maintaining code more difficult.

Alan Beban gave me religion on _Default about 10 years ago, and it is just
my personal preference for coding style. It is probably a few nanoseconds
faster than Offset, but in practical terms there is no real difference. Pick
a style you like and stick with it.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
H

Herbert Seidenberg

Never thought of that application,
but it does work in one dimension
if you use a code for the letters.
 
S

Sandy Mann

Thank you Chip. I think that I will probably stick with Offset - it seems
too schizophrenic to me to write 2 and think 1 <g> But at least knowing
about it will allow me to read other people's code.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk


Chip Pearson said:
Sandy,

This type of offsetting doesn't use a 0-based index as does the Offset
property. Instead, it actually calls the default method of the Range
object, named _Default, which uses a 1-based index. Code like

Debug.Print Range("A1")(2,2).Address

is really shorthand code for the more explicit

Debug.Print Range("A1").[_Default](2, 2).Address

_Default is a hidden member of the Range object, so you won't see it in
the Object Browse unless you enable "Show Hidden Members". Once you do
this, you'll see that it is indeed defined as the default method of a
Range.

Since [_Default] is 1-based, and Offset is 0-based, the following
statement are functionally equivalent:

Debug.Print Range("A1").[_Default](2, 2).Address
Debug.Print Range("A1")(2, 2).Address
Debug.Print Range("A1").Offset(1, 1).Address

Whether to use the default method or to use Offset is a matter of personal
preference and style. My only recommendation is that you choose one and
stick with it. Don't mix references using _Default with references using
Offset. They won't conflict with one another -- they play together just
fine -- but mixed reference styles may be confusing and might make
debugging/maintaining code more difficult.

Alan Beban gave me religion on _Default about 10 years ago, and it is just
my personal preference for coding style. It is probably a few nanoseconds
faster than Offset, but in practical terms there is no real difference.
Pick a style you like and stick with it.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


Sandy Mann said:
Hi Chip,

May I ask what type of *offset* you are using in your code - or point me
to somewhere that I can look it up for myself.

What I mean is that your:

Set StartRng = StartRng(2, 1)

looks like it equivelant to:

Set StartRng = StartRng.Offset(rowoffset:=1, columnoffset:=0)

or Set StartRng = StartRng.Offset(1, 0)

What really surprises me is that

Set StartRng = StartRng(0, 0)

seems to be the StartRng minus one row and minus one column which is
definitely non-intuitive, (at least not the way that my mind works)

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
Click to expand...
Click to expand...
 
S

Sandy Mann

Chip Pearson said:
_Default is a hidden member of the Range object, so you won't see it in
the Object Browse unless you enable "Show Hidden Members".
Click to expand...

Wow! VBA just gets bigger and bigger <g>
--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk


Chip Pearson said:
Sandy,

This type of offsetting doesn't use a 0-based index as does the Offset
property. Instead, it actually calls the default method of the Range
object, named _Default, which uses a 1-based index. Code like

Debug.Print Range("A1")(2,2).Address

is really shorthand code for the more explicit

Debug.Print Range("A1").[_Default](2, 2).Address

_Default is a hidden member of the Range object, so you won't see it in
the Object Browse unless you enable "Show Hidden Members". Once you do
this, you'll see that it is indeed defined as the default method of a
Range.

Since [_Default] is 1-based, and Offset is 0-based, the following
statement are functionally equivalent:

Debug.Print Range("A1").[_Default](2, 2).Address
Debug.Print Range("A1")(2, 2).Address
Debug.Print Range("A1").Offset(1, 1).Address

Whether to use the default method or to use Offset is a matter of personal
preference and style. My only recommendation is that you choose one and
stick with it. Don't mix references using _Default with references using
Offset. They won't conflict with one another -- they play together just
fine -- but mixed reference styles may be confusing and might make
debugging/maintaining code more difficult.

Alan Beban gave me religion on _Default about 10 years ago, and it is just
my personal preference for coding style. It is probably a few nanoseconds
faster than Offset, but in practical terms there is no real difference.
Pick a style you like and stick with it.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


Sandy Mann said:
Hi Chip,

May I ask what type of *offset* you are using in your code - or point me
to somewhere that I can look it up for myself.

What I mean is that your:

Set StartRng = StartRng(2, 1)

looks like it equivelant to:

Set StartRng = StartRng.Offset(rowoffset:=1, columnoffset:=0)

or Set StartRng = StartRng.Offset(1, 0)

What really surprises me is that

Set StartRng = StartRng(0, 0)

seems to be the StartRng minus one row and minus one column which is
definitely non-intuitive, (at least not the way that my mind works)

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
Click to expand...
Click to expand...
 
E

efandango

Chip,

I'm not sure why I should, after all it was your cleverness; but I feel
somewhat honoured that my problem should inspire a publishable formula!. Can
you unofficially title it: 'Eric's red-eye forumla'...

In my limited experience of VBA, it seems beautifully concise, if only I
could fully understand what its doing.

On another note, I think the two solutions on your website compliment each
other; certainly in the same ballpark.

once again, thanks. (to you all)
 
E

efandango

Well If boredom ever becomes your friend...

a 3 axis formula would be fun.

regards

Eric
 
Top