Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic range question | Excel Discussion (Misc queries) | |||
Dynamic chart question | Charts and Charting in Excel | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic Chart Question | Charts and Charting in Excel | |||
Question regarding dynamic range setting | Excel Worksheet Functions |