Odd Dynamic Range Question
 

HI, I am trying to create lookups using dynamic ranges but am having
trouble doing either because of the format of my data. I have posted a
copy of my data format below. I have 2 reference columns (A and B) and
the data that I need to lookup is in column C. The trick is, the
values in column A are not copied all the way down, they are just a
header. I need to look up the Large, Mid/Small, and Cash values for
each portfolio. Also, the number of rows for under each portfolio can
change (see the 3rd portfolio in my dataset) Does anyone have any
ideas on how to approach this?

Help is much appreciated!
Brian


MC_CO_AGE 8.33
LARGE 17.87
MID/SMALL 76.37
[Cash] 4.66
[Unassigned] 1.10
MC_GR_Raw 8.33
LARGE 20.20
MID/SMALL 74.35
[Cash] 4.51
[Unassigned] 0.93
S&P 500 8.33
LARGE 86.68
MID/SMALL 13.32

Odd Dynamic Range Question
 

If your data is alway like this:

MC_CO_AGE 8.33
LARGE 17.87
MID/SMALL 76.37
[Cash] 4.66
[Unassigned] 1.10

portfolio followed by 4 rows in that same exact (no variation) order, then you
could do something like:

=match(x99,sheet2!a:a,0)
Where x99 holds the portfolio name to match and sheet2 column A holds the list
of names.

This formula will give you the row of the exact match.

so if you want the row under the match, you'd just add one to that result:
=match(x99,sheet2!a:a,0)+1
That would put you to the row for Large for the matching portfolio.

Then if you wanted to bring back column C, you could use:
for Large:
=index(sheet2!c:c,match(x99,sheet2!a:a,0)+1)

for mid/small, it's +2
=index(sheet2!c:c,match(x99,sheet2!a:a,0)+2)

and so forth.

This won't work if the rows in each portfolio group aren't exactly the same.

================
If they're not, then I'd fill the empty cells with the portfolio names for that
group.

Debra Dalgleish shares a few techniques here (manual and macro):

http://contextures.com/xlDataEntry02.html
and a video
http://www.contextures.com/xlVideos01.html#FillBlanks

Then I'd use this technique to retrieve the value for matching both column A and
B.

Odd Dynamic Range Question
 

As long as each portfolio has a category for Large, Mid/Small, and Cash then
this shouldn't be a problem.

However, with that being said, I can't tell which column is which from the
posted sample data. Can you repost the sample data and use something like a
comma to delimit the columns?

--
Biff
Microsoft Excel MVP


"Bongard" wrote in message
...
HI, I am trying to create lookups using dynamic ranges but am having
trouble doing either because of the format of my data. I have posted a
copy of my data format below. I have 2 reference columns (A and B) and
the data that I need to lookup is in column C. The trick is, the
values in column A are not copied all the way down, they are just a
header. I need to look up the Large, Mid/Small, and Cash values for
each portfolio. Also, the number of rows for under each portfolio can
change (see the 3rd portfolio in my dataset) Does anyone have any
ideas on how to approach this?

Help is much appreciated!
Brian


MC_CO_AGE 8.33
LARGE 17.87
MID/SMALL 76.37
[Cash] 4.66
[Unassigned] 1.10
MC_GR_Raw 8.33
LARGE 20.20
MID/SMALL 74.35
[Cash] 4.51
[Unassigned] 0.93
S&P 500 8.33
LARGE 86.68
MID/SMALL 13.32

Odd Dynamic Range Question
 

Thank you guys both for responding. The catch here is that the rows
will not automatically be the same, otherwise I think this would be a
little bit easier. The program that I'm extracting from automatically
suppresses rows if the group contains no data (automatially suppresses
the mid/small or Large rows). Also, I can pretty easily write a macro
to do this but I'm trying to avoid doing that so it can be troubleshot
by the users. I'll post a comma delimited version of my table to make
it more clear for you Biff.

Thanks again,
Brian

MC_GR_Raw,,8.33
,LARGE,20.20
,MID/SMALL,74.35
,[Cash],4.51
,[Unassigned],0.93
REIT_AGE,,8.33
,LARGE,25.10
,MID/SMALL,67.94
,[Cash],2.99
,[Unassigned],3.97
S&P 1000,,8.33
,MID/SMALL,99.98
,[Unassigned],0.02
S&P 500,,8.33
,LARGE,86.68
,MID/SMALL,13.32
S&P Midcap,,8.33
,MID/SMALL,100.00
S&P Smallcap,,8.33
,MID/SMALL,99.93
,[Unassigned],0.07
SC_CO_AGE,,8.33
,LARGE,0.72
,MID/SMALL,94.34
,[Cash],4.34
,[Unassigned],0.59

Odd Dynamic Range Question
 

Ok, that really complicates things since not every portfolio has the same
categories.

In this case, and, if you want a formula solution I would do as Dave
Peterson suggested, fill the empty cells in column A with the portfolio
name. Then it becomes fairly simple.

--
Biff
Microsoft Excel MVP


"Bongard" wrote in message
...
Thank you guys both for responding. The catch here is that the rows
will not automatically be the same, otherwise I think this would be a
little bit easier. The program that I'm extracting from automatically
suppresses rows if the group contains no data (automatially suppresses
the mid/small or Large rows). Also, I can pretty easily write a macro
to do this but I'm trying to avoid doing that so it can be troubleshot
by the users. I'll post a comma delimited version of my table to make
it more clear for you Biff.

Thanks again,
Brian

MC_GR_Raw,,8.33
,LARGE,20.20
,MID/SMALL,74.35
,[Cash],4.51
,[Unassigned],0.93
REIT_AGE,,8.33
,LARGE,25.10
,MID/SMALL,67.94
,[Cash],2.99
,[Unassigned],3.97
S&P 1000,,8.33
,MID/SMALL,99.98
,[Unassigned],0.02
S&P 500,,8.33
,LARGE,86.68
,MID/SMALL,13.32
S&P Midcap,,8.33
,MID/SMALL,100.00
S&P Smallcap,,8.33
,MID/SMALL,99.93
,[Unassigned],0.07
SC_CO_AGE,,8.33
,LARGE,0.72
,MID/SMALL,94.34
,[Cash],4.34
,[Unassigned],0.59

Odd Dynamic Range Question
 

I went the direction of writing a quick macro to import the data and
copy down the cells in column A. I then created some lookups with the
format If(columnA="Ptfl",If(columnB="Size",columnC))). This seems to
be working just fine.

thanks for your help,
Brian

Odd Dynamic Range Question
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bongard" wrote in message
...
I went the direction of writing a quick macro to import the data and
copy down the cells in column A. I then created some lookups with the
format If(columnA="Ptfl",If(columnB="Size",columnC))). This seems to
be working just fine.

thanks for your help,
Brian