How to get get cell valure based on parameters
Results 1 to 4 of 4

Thread: How to get get cell valure based on parameters
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2012
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to get get cell valure based on parameters

    Sorry if the title didn't say it all.

    I have a probelm which I can't seem to solve.

    I have a list called "DATA" and in column A I have countries (Sweden, USA, France and so on) and in column B I have Citys for these countries (For USA It could be New York, Miami and so on) and in column C I have street names in this cities. Each country, city and streetname can appear several times in the list. If column A is USA the city in column B will alway be a US city and same goes for column C (will be a steet in the city in column B in the country in column A).

    I then need to create another list base on the "DATA" list (in another sheet). This list needs to look in "DATA" list in column A and write in all unique entries (all countires in the list)
    Like this:

    Country
    USA
    Sweden
    France

    Column B need to list all the unique entries in column B for the value in B1 (USA in this case which is liked to A2 of cause)
    Like this:

    USA
    New York
    Miami
    Las Vegas

    Column C need to list all the unique entries in column C for the value in C1 (New York in this case which is liked to B2 of cause)
    New York
    Ann street
    Fulton street
    Perry street

    Is this possible without VB programming? This would help me massivly!!

    /Per

  2. #2
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get get cell valure based on parameters

    Hi,

    is this what you mean?....

    Sample data....

    DATA

     ABC
    1CountryCityStreet
    2EnglandLondonLon Street
    3USANew YorkNew Street
    4FranceParisPar Street
    5CanadaTorontoTor Street
    6EnglandManchesterMan Street
    7USAMiamiMia Street
    8FranceNiceNic Street
    9CanadaOttawaOtt Street
    10EnglandOxfordOxf Street
    11USALas VegasLas Street
    12FranceCalaisCal Street
    13CanadaVancouverVan Street
    14EnglandLondonLon Street1
    15USANew YorkNew Street1
    16FranceParisPar Street1
    17CanadaTorontoTor Street1
    18EnglandManchesterMan Street1
    19USAMiamiMia Street1
    20FranceNiceNic Street1
    21CanadaOttawaOtt Street1
    22EnglandOxfordOxf Street1
    23USALas VegasLas Street1
    24FranceCalaisCal Street1
    25CanadaVancouverVan Street1


    Excel tables to the web >> Excel Jeanie HTML 4

    Example results.....

    Results

     ABCD
    1CountriesEnglandLondon 
    2EnglandLondonLon Street 
    3USAManchesterLon Street1 
    4FranceOxford  
    5Canada   
    6    

    Spreadsheet Formulas
    CellFormula
    B1=A2
    C1=B2
    A2{=IFERROR(INDEX(DATA!$A$2:$A$13,MATCH(0,COUNTIF($A$1:A1,DATA!$A$2:$A$13),0)),"")}
    B2{=IFERROR(INDEX(DATA!B$2:B$25,MATCH(0,COUNTIF(B$1:B1,DATA!B$2:B$25)+(DATA!A$2:A$25<>B$1),0)),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4


    You will need to change the sheet name and cell ranges to suit your layout.
    The formula In A2 needs entering with ctrl shift enter NOT just enter, you can then copy it down.
    The formula In B2 needs entering with ctrl shift enter NOT just enter, you can then copy it across and down.

    This solution was taken from here and slightly altered to suit......
    Unique list to be created from a column where an adjacent column has text cell values | Get Digital Help - Microsoft Excel resource

    I hope that helps.

    Good luck.

    Ak
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  3. #3
    New Member
    Join Date
    Feb 2012
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get get cell valure based on parameters

    Thanks AK it seems to be working

    /Per

  4. #4
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get get cell valure based on parameters

    Hi,

    I'm pleased it worked for you, thanks for the feedback.

    Ak
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

Some videos you may like

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
  •