Creating Menu Sequence Numbers

[FarzanaSaleem]

I have an Excel Sheet, in which I have downloaded menu options of an application.

This application is menu-driven. Every user is assigned a menu. Each menu has serveral menu option numbers.

A menu option number may lead to another menu or may call a program. I have dowloaded all menus of application in an excel sheet with the following columns:

Menu Urn
Menu Option Number
Program Urn
Menu Urn

If a particular menu option number calls another menu, then Menu Urn column is non-blank and Program Urn column is blank.

If a particular menu option number calls a program, then Menu Urn column is blank and Program Urn column is non-blank.

What my user wants is the MENU SEQUENCE NUMBER to reach a particular program. For example, there is a program urn P1, that can be invoked as follows:

Menu1: Call option # 2 that leads to Menu2
Menu2: Call option # 5 that leads to Menu3
Menu3: Call option # 6 that invokes P1

In this case, I will have a row in my file as:
Menu Urn: Menu3
Menu option number: 6
Program Urn: P1
Menu Urn: Blank

What is needed is to have another column MENU SEQUENCE NUMBER and to print 2,5,6 for the row in this case.

All MENU SEQUENCE NUMBERs are needed with reference to a particular menu urn, say Menu1.

 

[TonyJollans]

Hi FarzanaSaleem,

This can be done in code and may be possible without code. Can you answer a couple of questions for me ..

(a) Are there ay duplicates? In your example you get to Menu3 by selecting #2 from Menu1 followed by #5 from Menu2. Might there also be, perhaps, an option #7 from Menu1 leading to Menu3, or some other combination?

(b) Is your data in order, or could it be sorted to be in a particular order?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[FarzanaSaleem]

Hi TonyJollans,

(a) Are there ay duplicates? In your example you get to Menu3 by selecting #2 from Menu1 followed by #5 from Menu2. Might there also be, perhaps, an option #7 from Menu1 leading to Menu3, or some other combination?

Yes, a program urn or a menu urn may be invoked from more than one menu option numbers.

(b) Is your data in order, or could it be sorted to be in a particular order?

Data is sorted by Menu Urn (first column) and it could be sorted to be in a particular order

 

[FarzanaSaleem]

Anyone to help me please.

Thank you

 

[TonyJollans]

Hi FarzanaSaleem,

My apologies. I have looked at this but wasn't happy with what I had - I will come back later today. It isn't possible to write recursive formulae in a worksheet - if there is a fixed maximum depth of, say, 3 or 4, menus then a formula is possible - otherwise it will require code.

I think this in cell E2, and copied down, will give you 2,5,6 in your example but, as I say, it's not general enough and I'm not very happy with it ..

[blue][tt]=IF(A2=1,"",IF(OFFSET(A$2,MATCH(A2,D$2$200,0)-1,0)=1,"",OFFSET(A$2,MATCH(A2,D$2$200,0)-1,0) & ",") & OFFSET(A$2,MATCH(A2,D$2$200,0)-1,1)&","&B2)[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[FarzanaSaleem]

Thanks TonyJollans

if there is a fixed maximum depth of, say, 3 or 4, menus then a formula is possible

There is not a fixed maximum depth, but a maximum number may be assumed as 20 for example

otherwise it will require code

I have no problems if I need to write some VBA code

will give you 2,5,6 in your example but, as I say, it's not general enough

I copied this formula in column E of my file, then I analysed those entries where procedure urn is non-blank. Some entries were shown as #N/A and some entries showed me correct results as:

Menu2, 5, 6
Menu1, 8, 9

I noticed two important points:

1. Formula showed only those sequence with EXACTLY THREE STEPS like the two examples above

2. Formula showed one path, if a procedure urn is available through two menu sequences. For example, if a procedure urn is accessible through Menu2, 8, 9 and Menu6, 5, 9, then formula showed one of these two. This is a very important point because as I explained in the first post:

All MENU SEQUENCE NUMBERs are needed with reference to a particular menu urn, say Menu1

So if I need Menu Sequence Numbers with reference to Menu2, then I need to show Menu2, 8, 9 and if I need Menu Sequence Numbers with reference to Menu6, then I need to show Menu6, 5, 9. The reference menu urn will be known to me in advance.

 

[TonyJollans]

Hi FarzanaSaleem,

A depth of 20 is far too many to deal with in a formula, so code it is. I think this will do it for you.

Put this code in a standard module:

[blue]
Function MenuSelections(MenuId, Optional BaseMenuId = 1)

MenuSelections = GetMenuSelection(MenuId, BaseMenuId)
MenuSelections = MenuSelections & IIf(MenuSelections = "", "", ",") & MenuId.Offset(0, 1)

End Function
Function GetMenuSelection(MenuId, BaseMenuId)

    Dim FindRange As Range
    Dim ParentMenuId
    Dim ParentMenuChoice As String
    Dim MatchOffset As Long
    
    GetMenuSelection = ""
    
    If MenuId <> BaseMenuId Then
        
        Set FindRange = ActiveSheet.Range("D2", ActiveSheet.Range("A65536").End(xlUp).Offset(0, 3))
        If Application.Version >= 10 Then
            ' Find does not work in UDFs prior to Excel 2002
            Set FindRange = FindRange.Find(MenuId, , xlValues, xlWhole)
        Else
            On Error Resume Next
            MatchOffset = Application.WorksheetFunction.Match(MenuId, FindRange, 0) - 1
            On Error GoTo 0
            Set FindRange = IIf(MatchOffset < 0, Nothing, FindRange.Offset(MatchOffset, 0).Resize(1, 1))
        End If
        
        If Not FindRange Is Nothing Then
        
            ParentMenuId = FindRange.Offset(0, -3)
            GetMenuSelection = FindRange.Offset(0, -2)
            
            If Not ParentMenuId = BaseMenuId Then
                ParentMenuChoice = GetMenuSelection(ParentMenuId, BaseMenuId)
                GetMenuSelection = ParentMenuChoice & IIf(ParentMenuChoice = "", "", "," & GetMenuSelection)
            End If
        
        End If
    
    Set FindRange = Nothing
    
    End If
    
End Function[/blue]

And then in E2 put ..

[blue][tt] =MenuSelections(A2)[/tt][/blue]

.. and copy down.

Note that there is some version-dependent code in the main function. You can leave it as is or remove the irrelevant bit.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[FarzanaSaleem]

Thanks TonyJollans

I copied the code in a standard module and put in E2 =MenuSelections(A2,"Menu8") and copied down.

The code gave most (not all) sequence numbers accurately when I used 'Super Menu' as a Base Menu ID.

For example, if my highest menu urn is Menu1 and I used =MenuSelections(A2,"Menu1"), then most of the results that I got were accruate.

But when I used =MenuSelections(A2,"Menu8"), then I got:
1. Many #VALUEs
2. All menu sequence numbers with 1 Step
3. Some correct sequence numbers
4. In some cases, menu sequence number of only the last menu in the sequence

But, nevertheless, in case of 'Super User Menu', it helped me to reduce my work substantially.

Thank you

Regards

 

[TonyJollans]

Hi FarzanaSaleem,

Sorry, couple of problems. Try these fixes.

1. Replace the first function ("MenuSelections") with this ..

[blue]Function MenuSelections(MenuId, Optional BaseMenuId = 1)

If MenuId = BaseMenuId Then
    MenuSelections = MenuId.Offset(0, 1)
Else
    MenuSelections = GetMenuSelection(MenuId, BaseMenuId)
    If MenuSelections <> "" Then
        MenuSelections = MenuSelections & "," & MenuId.Offset(0, 1)
    End If
End If

End Function[/blue]

2. Change the line below (in the middle of the code, just after On Error Goto 0). Change from < to <= shown in [red]red[/red]

[blue]Set FindRange = IIf(MatchOffset [b][red]<=[/red][/b] 0, Nothing, FindRange.Offset(MatchOffset, 0).Resize(1, 1))[/blue]

Hopefully that will sort your problems and if you give a base menu which can't be used you should get an empty result

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.