+ Reply to Thread
Results 1 to 6 of 6

Show a unique list of items based on Cell value

  1. #1
    Registered User
    Join Date
    04-17-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Show a unique list of items based on Cell value

    I have a table imported from Access as follows:
    CITY..........SURNAME........VALUE
    London..........Jones.............30
    London..........Aria................20
    Newcastle......Aria................10
    London..........Hendry............50
    Newcastle......Harrison..........50
    Manchester....Gio.................30

    I would like to display a unique list of SURNAME based on CITY.
    For Example:
    If A2 in a spreadsheet says "London"
    Show dynamically in Cell B5, B6 and B7 horizontally, the values "Jones", "Aria" and "Hendry"

    If A2 in a spreadsheet says "Newcastle"
    Show dynamically in Cell B5 and B6 horizontally, the values "Aria" and "Harrison"

    If A2 in a spreadsheet says "Manchester"
    Show dynamically in Cell B7 horizontally, the values "Gio"

    If A2 in a spreadsheet says "TEST"
    Show dynamically in Cell B5 horizontally, nothing.

    How can I achieve this in Excel 2010? Any help is greatly appreciated.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Show a unique list of items based on Cell value

    I put your imported data in Sheet2 and your results in Sheet1
    In Sheet2 col D, I added a helper column (this can be hidden if you like)
    In D2
    =IF(A2=Sheet1!$A$2,D1+1,D1) copied down
    Sheet1 A2 has your city name (I used data validation and a dropdown)
    in Sheet1 B2 copied down
    =IFERROR(INDEX(Sheet2!$B$2:$B$7,MATCH(ROW(A1),Sheet2!$D$2:$D$7,0)),"")
    Does that work for you?
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Show a unique list of items based on Cell value

    Hi -

    Attached is a file that does what you specified. I had to add a couple of helper columns.

    Hope this helps.
    Attached Files Attached Files
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  4. #4
    Registered User
    Join Date
    04-17-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Show a unique list of items based on Cell value

    Quote Originally Posted by ChemistB View Post
    I put your imported data in Sheet2 and your results in Sheet1
    In Sheet2 col D, I added a helper column (this can be hidden if you like)
    In D2
    =IF(A2=Sheet1!$A$2,D1+1,D1) copied down
    Sheet1 A2 has your city name (I used data validation and a dropdown)
    in Sheet1 B2 copied down
    =IFERROR(INDEX(Sheet2!$B$2:$B$7,MATCH(ROW(A1),Sheet2!$D$2:$D$7,0)),"")
    Does that work for you?
    This is very close to what I want. However i've realised ive specified the wrong cells. I actually want them to show across cell B2, to C2, D2, E2 and so on. I've highlighted this area on sheet1(attached).

    loginjmor - Your example is also what I wanted But i actually wanted the results to display horizontally(like B2, C2, D2 and so on).

    Is there a way to display them horizontally across the sheet?
    Attached Files Attached Files
    Last edited by gorgon777; 05-10-2013 at 11:40 AM.

  5. #5
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Show a unique list of items based on Cell value

    Try this workbook

    Sheet2 Column H extracts the Unique City Names.

    Sheet1 B2 is a CSE array, confirm with Ctrl+Shift+Enter before dragging across

    No helpers required.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  6. #6
    Registered User
    Join Date
    04-17-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Show a unique list of items based on Cell value

    Quote Originally Posted by Marcol View Post
    Try this workbook

    Sheet2 Column H extracts the Unique City Names.

    Sheet1 B2 is a CSE array, confirm with Ctrl+Shift+Enter before dragging across

    No helpers required.
    This is perfect, thank you and everyone for your suggestions, its made me realise the INDEX() and OR() methods which are pretty useful!

    This query can be marked as solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1