4

I have a table on a worksheet called "DealSetup" that looks like this simplified version:

   A           B
1  John Doe    1
2  Jane Doe    1
3  Mark Doe    2
4  Doug Doe    1
5  Mary Doe    2

I have a table on a worksheet called "List" that I would like to pull the names into from the "DealSetup" worksheet. However, I want only list the names based on the value in column B.

For example, on the "List" worksheet, I'd like to use some sort of INDEX or array function of some kind to look to the "DealSetup" sheet and pull everyone who's column B value is 1 and list them. I do not want any blanks, though. It should look like this:

 EVERYONE WITH B COLUMN VALUE OF 1:  
   A  
1  John Doe
2  Jane Doe
3  Doug Doe

Or like this:

 EVERYONE WITH B COLUMN VALUE OF 2:  
   A  
1  Mark Doe
2  Mary Doe

I've been trying a few things with no luck. Not sure where to go from here, so any input would be appreciated!!

  • check out the advanced filter functionality - it can be done with it (though not fully automatically) – Peter Albert Dec 23 '13 at 20:23
  • I have tried out the Advanced filter, but the problem with that is it copies the values, and I want to just have them listed dynamically. Thanks! – drumichael611 Dec 23 '13 at 20:28
  • can you use support columns? – Peter Albert Dec 23 '13 at 20:35
  • 1
    When you say table, do you mean a table object or a table in cells? – Raystafarian Dec 23 '13 at 23:37
  • "Dynamically" - In what kind? When adding new values to the DealSetup sheet? Or when you change the criteria on the List sheet? Also, is VBA a valid solution? A macro could be triggered by one of the previous named events and re-apply the advanced filter in place – nixda Dec 24 '13 at 2:43
4

Assuming your data in DealSetup!A$2:B$6 you can use this formula in List!A2

=IFERROR(INDEX(DealSetup!A$2:A$6,SMALL(IF(DealSetup!B$2:B$6=C$1,ROW(DealSetup!A$2:A$6)-ROW(DealSetup!A$2)+1),ROWS(A$2:A2))),"")

confirm with CTRL+SHIFT+ENTER and copy down as far as required and further. When you run out of names you get blanks.

Assumes criteria (1,2 etc.) in C1

see attached workbook - try changing C1 to 2

If names repeat (within the specified criteria) then you'll get the names repeated - formula can be tweaked to show each name once only, i.e. this version

=IFERROR(INDEX(DealSetup!A$2:A$6,SMALL(IF(DealSetup!B$2:B$6=C$1,IF(COUNTIF(A$1:A1,DealSetup!A$2:A$6)=0,ROW(DealSetup!A$2:A$6)-ROW(DealSetup!A$2)+1)),1)),"")

-1

If you want a solution with formulas, then you're going to have blanks at the bottom. There's no way to have the cells containing the formula grow and shrink as your list changes. Your table with formulas should be the same height as your raw data to ensure that you don't miss anything if, say, everybody has a 1 in column B. If you know that'll never be the case, you can reduce the number of rows in the List worksheet but it's a risk. Here's a formula that'll return them all in order with blanks at the bottom. It's an array formula so enter it with Ctrl+Shift+Enter: (Barry Houdini already submitted a similar solution)

{=IFERROR(INDEX(DealSetup!$A:$A,SMALL(IF(DealSetup!$B:$B=1,ROW(DealSetup!$B:$B)),ROW())),"")}

If you can't accept blanks at the bottom, it'll have to be either a PivotTable or VBA solution. This question is pretty old so I don't expect to see OP back here any time soon, unfortunately.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.