Extracting Data From A List
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Extracting Data From A List

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

    Default

     
    Okay, I'm pretty good with Excel now, but there is on problem I'm having. How do I search a list (or array) for certain values, then extract those values to another list? For instance, suppose I have the following in cells A1:B7=

    No A
    No B
    Yes C
    No D
    No E
    Yes F
    No G

    And I want to end up with:
    In cell C1: C
    In cell C2: F

    All I want to do is extract the "Yes" values and put them in a row or column one after the other, without blank cells. I also need to do this with a formula as opposed to filtering or writing a macro. Any help would be appreciated.

    Mike

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    I know you say as "apposed to filetring", but the advanced filter would do this nicely as would a very simple Pivot Table.



  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Select cells D1:D7 and enter the array formula...

    {=SMALL(IF(ISNUMBER(MATCH(A1:A7,{"Yes"},0)),ROW(A1:A7)),ROW(A1:A7))}

    Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

    Next, enter the formula, =IF(ISNUMBER(D1),INDEX(B:B,D1),""), into cell C1 and Fill Down to C7.

    Hide column D if desired.


    [ This Message was edited by: Mark W. on 2002-03-11 09:43 ]

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