Results 1 to 6 of 6

Thread: Ugh, this is too tough for me to figure out!!
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2007
    Location
    Chicago, IL
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Ugh, this is too tough for me to figure out!!

    Hi,

    I want to create a drop down where they select a group and all the names fill in below it.

    GROUP 2
    JOHN
    LARRY
    LUCY
    GINA
    FRANK
    ANNA

    Cell A5 is my drop down where they will select the group

    Starting in A7, I'd like a formula that would look at A5 and start to list the names. Essentially, if they select group 2 then in A7 should be John, A8 should be Larry, A9 should be Lucy, etc. Then cell A13 should be blank because all the names are already listed above.

    I will be drawing all this information from a large list which will be sorted by groups.

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,074
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Ugh, this is too tough for me to figure out!!

    I will be drawing all this information from a large list which will be sorted by groups.
    Since you have a large list then maybe VBA is more suitable for you.
    Are you willing to use VBA?

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    22
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ugh, this is too tough for me to figure out!!

    Hello!

    Happy to give it a try. This will really come down to the way your data is structured and grouped. I pulled together a few examples of what I mean.

    Case 1


    GROUP 2 GROUP 3 GROUP 4
    JOHN DAVID ...
    LARRY ANDREW
    LUCY MIKE
    GINA MICHELLE
    FRANK SARAH
    ANNA

    In case 1, I would write an index-match-match with a counta partially locked on $A$7:A7.

    Case 2

    JOHN Group 2
    LARRY Group 2
    LUCY Group 2
    GINA Group 2
    FRANK Group 2
    ANNA Group 2
    DAVID Group 3
    ANDREW Group 3
    MIKE Group 3
    MICHELLE Group 3
    SARAH Group 3
    ... Group 4

    There's a few solutions for this. If your lists are in order, you can use countif on your A5 cell in an if statement to stop the formula after it reaches a certain number of names.

    Case 3

    Group 2
    JOHN
    LARRY
    LUCY
    GINA
    FRANK
    ANNA
    Group 3
    DAVID
    ANDREW
    MIKE
    MICHELLE
    SARAH
    Group 4
    …

    This one is the least stable IMO. You will need to have consistent group names which can be tough to maintain. Assuming you are comfortable signing up for that, index match to get the start of GROUP X then offset with a counta $A$7:A7. You'll need an if statement to check when it moves to GROUP X+1.

    Some other things to think about - Will you have people in duplicate lists? For example, would JOHN ever be in GROUPs 2, 4 and 7? How often will you be updating these lists? Will they always be sorted by grouping (all of group 1, then all of group 2, etc.)? Do you know the maximum size of groups?

    Lots there. If you can tell me how the data is structured I can give you a more tailored solution.


  4. #4
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    229
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Ugh, this is too tough for me to figure out!!

    If you can put all your data into Excel Tables then this approach uses one function.

    Enter your groups and data as below although they could be on another sheet. One critical factor is the headings can't contain spaces or special characters (underscore is OK).

    Select all the cells with data and Insert, Table. Now select Formulas, Create from Selection and use the Top Row for names. Selecting Name Manager you should see a Table1 name (created when you did Insert Table) and the three Groupn names.

    In A5 select Data, Data Validation, List and select all the headings from the table.
    Now if you select the A5 LoV it should offer Group1, Group2, Group3.

    In A7 select Data, Data Validation, List and for Source enter =INDIRECT($A$5)
    Excel takes the A5 entry as the table name and the LoV in A7 will offer all the entries for that named range.

    A B C D E F G
    1 Group1 Group2 Group3
    2 Sue JOHN Vanessa
    3 Sara LARRY Pete
    4 Bert LUCY Fred
    5 Group1 Alf GINA
    6 FRANK
    7 Alf ANNA
    8
    Sheet2

    Workbook Defined Names
    Name Refers To
    Group1 =Sheet2!$E$2:$E$7
    Group2 =Sheet2!$F$2:$F$7
    Group3 =Sheet2!$G$2:$G$7

  5. #5
    Board Regular
    Join Date
    May 2007
    Location
    Chicago, IL
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ugh, this is too tough for me to figure out!!

    I'm not familiar with VBA so I don't think that's the route I can go.

    Ok so the data is pulled where the group numbers are all listed in column A and the names are all in column C.

    There is not a set limit to how many people are in each group...there could be anywhere from 5 to 30 in each group so I'm not sure the COUNTIF would work.

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    22
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ugh, this is too tough for me to figure out!!

    Option 1 -

    =IFERROR(INDEX('YOUR DATA SHEET NAME'!$C$1:$C$40,MATCH(0,INDEX(COUNTIF($A$6:A6,'YOUR DATA SHEET NAME'!$C$1:$C$40)+('YOUR DATA SHEET NAME'!$A$1:$A$40<>$A$5)*1,0),0)),"")

    The over engineered solution based on the addresses you provided in post #1 . You'll have to adjust your ranges to match your data file. Copy down below the highest group count.

    Pros - This will find all names in the group even if they haven't been sorted. Cons - Can be slow depending on how big your data set is.

    Option 2

    =IF(COUNTIF('YOUR DATA SHEET NAME'!$A:$A,$A$5)<=COUNTA($A$6:A6),"",INDEX('YOUR DATA SHEET NAME'!$C:$C,MATCH($A$5,'YOUR DATA SHEET NAME'!$A:$A,0)+COUNTA($A$6:A6)))

    Pros - should be quick regardless of file size. Cons - Need to sort data set in Group Order for this to work.

Some videos you may like

User Tag List

Tags for this Thread

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
  •