Capturing dynamic ranges

[Paul Martin]

Hi all

I've worked with dynamic ranges quite a bit and am generally fine with
them but now I have a slightly sticky scenario and my formulas are
getting a little long. I'm thinking there'll be a simpler solution
than mine. Here's the problem.

I have a dynamic range in a single column, let's call it AllData, and
is structured like this:

ALLDATA_START
NORTH
Apples
Pears
Oranges
TOTAL NORTH
SOUTH
Apples
Pears
Oranges
TOTAL SOUTH
EAST
Apples
Pears
Oranges
TOTAL EAST
WEST
Apples
Pears
Oranges
TOTAL WEST
ALLDATA_END

Given that AllData is defined, I'd like to create named ranges for
North, South, East and West. Again, these ranges need to be dynamic,
and my thinking is that each is an offset of AllData, based on the
position of the first and last lines of each of N, S, E & W.

Any suggestions appreciated. TIA.

Paul Martin
Melbourne, Australia

 

[T. Valko]

You can define each range like this...

For North:

=INDEX(AllData,MATCH("north",AllData,0)):INDEX(AllData,MATCH("total
north",AllData,0))

So, North will refer to:

NORTH
Apples
Pears
Oranges
TOTAL NORTH

 

[Paul Martin]

Great, thanks Biff

Paul

You can define each range like this...

For North:

=INDEX(AllData,MATCH("north",AllData,0)):INDEX(AllData,MATCH("total
north",AllData,0))

So, North will refer to:

NORTH
Apples
Pears
Oranges
TOTAL NORTH

 

[Paul Martin]

As a slight refinement, the individual totals are not distinguishable,
but I can find the end of one range by a row offset (-1) from the
start of the next range. How would I deal with the end of the last
range?

Paul

 

[T. Valko]

the individual totals are not distinguishable

What do those mean?

--
Biff
Microsoft Excel MVP


As a slight refinement, the individual totals are not distinguishable,
but I can find the end of one range by a row offset (-1) from the
start of the next range. How would I deal with the end of the last
range?

Paul

 

[Roger Govier]

Hi Paul

just amend Biff's formula to
=INDEX(AllData,MATCH("north",AllData,0)+1):INDEX(AllData,MATCH("total
north",AllData,0)-1)

North will then just refer to the Apples, Pears and Orange cells that are
bounded by North and North Total

You don't actually need to refer to the North Total cell, as =SUM(North)
will give the same value

--
Regards
Roger Govier

As a slight refinement, the individual totals are not distinguishable,
but I can find the end of one range by a row offset (-1) from the
start of the next range. How would I deal with the end of the last
range?

Paul




__________ Information from ESET Smart Security, version of virus
signature database 4798 (20100122) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4798 (20100122) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Paul Martin]

Biff, the actual data doesn't have total lines that are differentiated
as per my example, ie, it's just "TOTAL".

Paul

 

[Paul Martin]

Thanks, Roger. You've pre-empted my next question, which is a little
more complex. The source data is cut and pasted from a PDF and
includes headers and footers which can interrupt the data.
Consequently, one cannot be certain that the start and end of the data
will be a set offset from the start and end of "AllData". So, I'd
need something that identifies (in my example) the position of Apples
(as the start of the data) and Oranges (as the end of the data). Any
suggestions?

Paul

 

[Paul Martin]

Another complexity is that there is an unknown string appending each
row. So it's not just 'Apples', but 'Apples ABC' in one place,
'Apples XYG' in another, etc. And because of the headers and footers,
the data cannot be guaranteed to be sorted, ruling out VLOOKUP. Any
suggestions are appreciated. So, to clarify, the data might look like
this:

ALLDATA_START
NORTH
Apples 197
Pears 83
<FOOTER>
<HEADER>
Oranges 176
TOTAL fclg
SOUTH
Apples 9
Pears 988
Oranges 15
TOTAL xnoa
<FOOTER>
ALLDATA_END

 

[T. Valko]

Well, at this point I'm lost!

--
Biff
Microsoft Excel MVP


Another complexity is that there is an unknown string appending each
row. So it's not just 'Apples', but 'Apples ABC' in one place,
'Apples XYG' in another, etc. And because of the headers and footers,
the data cannot be guaranteed to be sorted, ruling out VLOOKUP. Any
suggestions are appreciated. So, to clarify, the data might look like
this:

ALLDATA_START
NORTH
Apples 197
Pears 83
<FOOTER>
<HEADER>
Oranges 176
TOTAL fclg
SOUTH
Apples 9
Pears 988
Oranges 15
TOTAL xnoa
<FOOTER>
ALLDATA_END

 

[Paul Martin]

I'll start a new post, Biff. Maybe I'm confusing the issue.

Thanks

Paul