Search - Not with Autofilter
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Search - Not with Autofilter

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     

    I have an Excel sheet with a large list of names. Some of the names are repeated as the list shows members of different user groups in my organisation, and of course a person can be a member of more than 1 user group.

    I want to build a small search facility at the top of the sheet, in which when a name is selected from a drop down list, it shows how many lists they are in and a dynamically created drop down list with the names of the groups.

    Example:-

    Intranet Users Internet Users Admin Users
    Joe Bloggs Joe bloggs Joe Bloggs
    Mary Smith John Doe John Doe
    John Doe Jim Beam
    Jim Beam

    Etc.

    I have the 'how many' part working but can't get the new drop down list to work.
    e.g.
    Running the query on 'Mary Smith' would result in 1 and on 'Jim Beam' would result in 2 etc.

    But I also want a drop down list that would contain 'Intranet Users' only for Mary Smith but 'Intranet Users, Internet Users, Admin Users' for John Doe.

    Can you help me, please??

    Thanking you in advance


  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,860
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-03-15 06:17, rcranley wrote:

    I have an Excel sheet with a large list of names. Some of the names are repeated as the list shows members of different user groups in my organisation, and of course a person can be a member of more than 1 user group.

    I want to build a small search facility at the top of the sheet, in which when a name is selected from a drop down list, it shows how many lists they are in and a dynamically created drop down list with the names of the groups.

    Example:-

    Intranet Users Internet Users Admin Users
    Joe Bloggs Joe bloggs Joe Bloggs
    Mary Smith John Doe John Doe
    John Doe Jim Beam
    Jim Beam

    Etc.

    I have the 'how many' part working but can't get the new drop down list to work.
    e.g.
    Running the query on 'Mary Smith' would result in 1 and on 'Jim Beam' would result in 2 etc.

    But I also want a drop down list that would contain 'Intranet Users' only for Mary Smith but 'Intranet Users, Internet Users, Admin Users' for John Doe.

    Can you help me, please??

    Thanking you in advance
    Make a 1-column list of registered users in a worksheet named Admin from A1 on:

    {"Registered Users";"Joe Bloggs";"Mary Smith";"John Doe";"Jim Beam";"aky"}

    Activate A2.
    Activate Insert|Name|Define.
    Enter USERS as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Admin!$A$2,0,0,COUNTA(Admin!$A:$A)-1,1)

    Click OK.

    I'll asume that the relevant data is in a worksheet named Data.
    Lets say that A1:C5 in Data houses the sample data you provided:

    {"Intranet Users","Internet Users","Admin Users";
    "Joe Bloggs","Joe bloggs","Joe Bloggs";
    "Mary Smith","John Doe","John Doe";
    "John Doe","Jim Beam","";
    "Jim Beam","",""}

    Note 1. "" stand for an empty cell.

    In E1 enter: User Groups [ just a label ]

    In E2 enter: =IF(ISNUMBER(MATCH(Query!$A$3,$A$2:$A$5,0)),$A$1,"")

    In E3 enter: =IF(ISNUMBER(MATCH(Query!$A$3,$B$2:$B$5,0)),$B$1,"")

    In E4 enter: =IF(ISNUMBER(MATCH(Query!$A$3,$C$2:$C$5,0)),$C$1,"")

    Note 2. The formulas from E2 on are checking one by one whether a given user is a member in a given group. Thus, a separate formula per group given the layout of your data.

    In column F from F2 on enter the following sets of labels:

    {"Groups";"LocList";"SortList";"NoDupsList"}

    In G1 enter:

    =COUNTA($E$1:$E$10)-1

    Note 3. Adjust the range $E$1:$E$10 to suit the number of user groups that you have.

    In G2 enter:

    ="$E$2:"&"$E"&$G$1+1

    In G3 enter:

    ="$H$2:"&"$H$"&COUNTA(H:H)+1

    In G4 enter:

    ="$I$2:"&"$I$"&COUNTA(I:I)+1

    In G5 enter:

    ="$J$2:"&"$J$"&COUNTA(I:I)+1

    In H2 enter and copy down till 10th row:

    =IF(LEN(E2),SUMPRODUCT((E2>INDIRECT($G$2))+0)+1,"")

    Note 4. The number 10 is the assumed number of user groups, which you must adjust to suit.

    In I2 enter:

    =IF(LEN(H2)=0,"None",IF(ISNUMBER(MATCH(ROW()-ROW($H$2)+1,INDIRECT($G$3),0)),INDEX(INDIRECT($G$2),MATCH(ROW()-ROW($H$2)+1,INDIRECT($G$3),0)),0))

    In I3 enter and copy down till the 10th row:

    =IF(ISNUMBER(MATCH(ROW()-ROW($H$2)+1,INDIRECT($G$3),0)),INDEX(INDIRECT($G$2),MATCH(ROW()-ROW($H$2)+1,INDIRECT($G$3),0)),0)

    In J2 array-enter and copy down till the 10th row:

    =IF(ROW()-ROW(INDIRECT($G$5))+1>ROWS(INDIRECT($G$4))-COUNTIF(INDIRECT($G$4),0),"",INDIRECT(ADDRESS(SMALL((IF(INDIRECT($G$4)<>0,ROW(INDIRECT($G$4)),ROW()+ROWS(INDIRECT($G$4)))),ROW()-ROW(INDIRECT($G$5))+1),COLUMN(INDIRECT($G$4)))))

    Note 5. You need to hit control+shift+enter at the same time, not just enter, to array-enter a formula.

    Activate J2.
    Activate Insert|Name|Define.
    Enter GROUPS as name in the Names in the Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Data!$J$2,0,0,COUNTIF(Data!$J$2:$J$10,"*?*"),1)

    Click OK.

    Now we are in the main worksheet to which I will refer as Query.

    In Query:

    In A2:C2 enter the following labels:

    {"User","# Groups","Groups"}

    Activate A3.
    Activate the option Data|Validation.
    Choose 'List' for Allow.
    Enter as 'Source':

    =USERS

    Click OK.

    In B3 enter:

    =COUNTIF(Data!A2:C5,A3)

    Activate C3.
    Activate the option Data|Validation.
    Choose 'List' for Allow.
    Enter as 'Source':

    =GROUPS

    Click OK.

    Aladin

    PS. If interested in a WB that uses the above system of formulas, just drop me a line.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com