Results 1 to 4 of 4

Thread: Extract only the Titles that not empty
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2017
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract only the Titles that not empty

    Hello friends,
    In the example I have attached, I have a list of countries where some states have data in the same column and some do not.
    In the table below I would like to see only a list of countries that have data.


    Hello friends,
    In the example I have attached, I have a list of countries where some states have data in the same column and some do not.
    I would like to know how to create list of the countries that onlt have data have data.


    I'll be glad to receive your help
    Thank you

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    1
    Alabama
    California
    Florida
    Hawaii
    Illinois
    Indiana
    Nevada
    New Jersey
    New York
    Oregon
    Texas
    Washington
    2
    3
    1
    2
    3
    1
    1
    5
    1
    3
    1
    1
    2
    2
    1
    5
    3
    4
    5
    4
    3
    3
    4
    5
    3
    5
    5
    5
    1
    5
    6
    3
    3
    1
    5
    1
    2
    2
    7
    5
    3
    4
    5
    3
    2
    8
    3
    2
    2
    1
    1
    9
    4
    5
    5
    3
    2
    1
    10
    3
    3
    2
    5
    4
    3
    1
    11
    3
    3
    4
    4
    2
    4
    12
    4
    3
    3
    2
    4
    5
    5
    1
    13
    14
    15
    Alabama
    California
    Florida
    Illinois
    Indiana
    Nevada
    New York
    Texas
    Washington
    16
    17
    18
    19
    20
    Sheet: Data
    Last edited by Omer_K; Sep 16th, 2019 at 03:14 PM.

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,643
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Extract only the Titles that not empty

    Put this in C15 and drag right:

    =IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($B$1:$M$1)/(MMULT(TRANSPOSE(ROW($B$2:$B$12)),--($B$2:$M$2<>""))>0),COLUMNS($C15:C15))),"")

    And it appears that Washington should not be listed?
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular
    Join Date
    Apr 2017
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract only the Titles that not empty

    Eric you are a genius!!


    You're definitely right - Washington shouldn't show up,


    Thanks so much it works perfect

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,643
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Extract only the Titles that not empty

    Happy to help!

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
  •