Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I can't find one good way to solve this problem. I will try to sum the issue as best as I can:

I use Hyperion Essbase Excel ad-in to navigate inside OLAP cubes. What I am trying to accomplish here is to map certain labels to values of a dimension. Inside this dimension are multiple levels of data grouping so one child node can have multiple levels of parents. I can know beforehand what will be the members but they are subject to change every month and finding missing members can be tedious so what we do is use the parent members and zoom in its members. However, inside one parent can be multiple different labels. I have achieved part of this by using VLOOKUP() and if the current row is not found in my table then I look one row down and drag that formula, this has the following undesirable effect:

enter image description here

I have numbered what goes together. As you can see in my example, the element "24566" is also found in the parent "FD120465". The result is that all the elements found before it in the same group are mapped to "Expense B" but the target result is that all child elements should be mapped to "Expense D" but "24566". Here is the formula I have used in this example:

=IFERROR(VLOOKUP(C11,$C$3:$D$8,2,0), D12)

There are several ways to deal with this that I have come up with but none that I like. I could make two different formulas, one that looks at only child elements and another that looks only at parent elements. If child elements are put first, it does not matter that the wrong label will be put in the group ("24566" would be labelled "Expense D" in the orange part). This also has the disadvantage to incur more work (defining the regions where to use formula 1 and formula 2 and either do the work manually or overkill it with VBA...)

Another way is to simply take out every child element and put it in the table with its proper mapping. The problem is that parents are subject to have more or less items in them each month and mapping can change (though that should not happen as often) so managing every child element will get tedious. The sample I have provided is tiny to what I have (15-20 children in a parent).

This is somewhat of a last shot I am giving at this task before we settle for the solution that has the least downsides. Is there an obvious (or even not so obvious) way to solve this problem cleanly ?

Thanks !

share|improve this question
Er, does the above explain which is parent and which is child, and how these relate to elements/labels? 'all child elements should be mapped to "Expense D" but "24566"' - what makes 24566 different? I can see you have tried, but if it makes no sense at all to me it might not make enough sense to others! – pnuts Jan 1 '13 at 4:34
I'm sorry I tried my best to make it as clear as possible but it is quite a complex problem. Elements starting with "FD" are parents and elements with only 5 characters are children. If a parent has a mapping associated with it then all its children should have the same mapping except if the child was specifically assigned another label. – Alexandre P. Levasseur Jan 1 '13 at 5:07

This works IF you want what I suspect you want and are starting from where I suspect you are. (It is not necessarily the best way to achieve the result it does from the data but may at least help in clarifying what is required.)

  1. Add a helper column so that all children have a parent (assuming they do):

    B11=IF(LEFT(C11,2)="FD",C11,B12) (copied down as required)

  2. =IFERROR(VLOOKUP(C11,$C$3:$D$8,2,FALSE),VLOOKUP(B11,$C$3:$D$8,2,FALSE)) (in D11, copied down as far as required).

SU526563 example

I have assumed that the bottom two entries in the Table contain typos.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .