+ Reply to Thread
Results 1 to 26 of 26

Lookup and show multiple items from list - [SOLVED]

  1. #1
    Registered User
    Join Date
    05-18-2018
    Location
    US / UK / NL / DE
    MS-Off Ver
    Up to date Office 365 in various languages and Office 2010
    Posts
    83

    Lookup and show multiple items from list - [SOLVED]

    Good morning all,

    I have been working on the problem and have issues getting the right solution.

    I have a list with 110000 entries and for argument sake I will use fruit

    The list:
    Italianapples
    Italianapples
    Chineseapples
    Canadianberris
    Spanischcoconuts
    Dutchbanannas
    USapples
    Frenchgrapes
    Italianapples
    Mexicanberries
    Belgianapples
    and 110000 more

    In a different tab I will write the value to search for, including wild cards like ? and *:
    *apples

    Now I would like the following list to show up in a different tab:
    Italianapples
    Italianapples
    Chineseapples
    USapples
    Italianapples
    Belgianapples

    Looking up the first match or checking number of matches works ok. But showing all matches in a list is a problem for me.

    All help will be appreciated.

    Thanks

    Duncan
    Last edited by Duncan-; 05-20-2018 at 09:50 AM.

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,546

    Re: Lookup and show multiple items from list

    Welcome to the formum Duncan,
    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,100

    Re: Lookup and show multiple items from list

    One way using formulas would be :

    =INDEX($A$1:$A$11, SMALL(IF(FREQUENCY(IF(ISERROR(SEARCH(TRANSPOSE($D$1), $A$1:$A$11)), "", ROW($A$1:$A$11)-MIN(ROW($A$1:$A$11))+1), ROW($A$1:$A$11)-MIN(ROW($A$1:$A$11))+1)=ROWS($D$1), ROW($A$1:$A$11), ""), ROW(A1)))

    array entered. Can be wrapped in a IFERROR to manage errors.

  4. #4
    Registered User
    Join Date
    05-18-2018
    Location
    US / UK / NL / DE
    MS-Off Ver
    Up to date Office 365 in various languages and Office 2010
    Posts
    83

    Re: Lookup and show multiple items from list

    Hi NFL

    Ok , will do

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,231

    Re: Lookup and show multiple items from list

    Try

    In Sheet2 A2 with data in Sheet1 A2:A100 and search value in G1 of Sheet2

    =IFERROR(INDEX(Sheet1!$A$2:$A$100,SMALL(IF(ISNUMBER(SEARCH(Sheet2!$G$1,Sheet1!$A$2:$A$100)),ROW(Sheet1!$A$2:$A$100)-ROW($A$2)+1,""),ROWS($1:1))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Then copy down column
    Last edited by JohnTopley; 05-18-2018 at 06:45 AM.

  6. #6
    Registered User
    Join Date
    05-18-2018
    Location
    US / UK / NL / DE
    MS-Off Ver
    Up to date Office 365 in various languages and Office 2010
    Posts
    83

    Re: Lookup and show multiple items from list

    Used help from John and Paul

    Attached a sample xls below. Now where did I go wrong ?
    Attached Files Attached Files
    Last edited by Duncan-; 05-18-2018 at 07:08 AM.

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,546

    Re: Lookup and show multiple items from list

    G4=IFERROR(INDEX($C:$C,SMALL(INDEX((LEN($C$4:$C$10000)=LEN(SUBSTITUTE(UPPER($C$4:$C$10000),UPPER($E$4),"")))*10^10+ROW($C$4:$C$10000),0),ROWS($G$4:$G4))),"")
    Try this and copy towards down

    Note: change *apples as apples

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,231

    Re: Lookup and show multiple items from list

    Search parameter does NOT require wild card i.e Apples will do.

    Second change Formulas==>Calculation Options to "Automatic"
    Last edited by JohnTopley; 05-18-2018 at 07:22 AM.

  9. #9
    Registered User
    Join Date
    05-18-2018
    Location
    US / UK / NL / DE
    MS-Off Ver
    Up to date Office 365 in various languages and Office 2010
    Posts
    83

    Re: Lookup and show multiple items from list

    Update .... now where did I go wrong .... ? Rev 002 attached.



    PS: With over 110000 rows in my 20 column table tha Automatic setting makes everything way too slow. But for this small fruit test it will be a better setting of course
    Attached Images Attached Images
    Attached Files Attached Files

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,546

    Re: Lookup and show multiple items from list

    if you post your original file by removing sensitive data if any, we can to it with helper column and pivot table
    in this case you have to refresh pivot table when you change your criteria (search item)

  11. #11
    Registered User
    Join Date
    05-18-2018
    Location
    US / UK / NL / DE
    MS-Off Ver
    Up to date Office 365 in various languages and Office 2010
    Posts
    83

    Re: Lookup and show multiple items from list

    Thanks NFL, not sure why it would be better to post a table with close to 2 000 000 entries and filled with formulas. I think that would make it more difficult and besides that it will be a large file and will bog down many computers , just saving it might take a couple of minute.

    Seems to me the fruit example is a good representation.

  12. #12
    Registered User
    Join Date
    05-18-2018
    Location
    US / UK / NL / DE
    MS-Off Ver
    Up to date Office 365 in various languages and Office 2010
    Posts
    83

    Re: Lookup and show multiple items from list

    Only now I realized the formula need sto be copied to the other rows. This means one needs to know the number of hits before hand, or brute force copy it a 110000 times. I was hoping there is a formula that does not require this.

    Thanks for all the help guys. I think I am getting close to the understanding your tips and suggestions.

    But I am still going wrong in the first formula from Paul.
    Attached Images Attached Images
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,231

    Re: Lookup and show multiple items from list

    If you have 2 formulas that work ...???

    Regarding the number of rows being variable: you can create dynamic name ranges which automatically adjust when rows are added / deleted

    There is any example in that attached:

    Named Range:; Mylist

    Refers to: =TheResults!$B$4:INDEX(TheResults!$B$4:$B$200000,COUNTIF(TheResults!$B$4:$B$200000,"?*"),1)


    Look in Formulas==.Name manager

    Formulas is:

    =IFERROR(INDEX(MyList,SMALL(IF(ISNUMBER(SEARCH($C$4,MyList)),ROW(MyList)-ROW($G$4)+1,""),ROWS($1:1))),"")
    Attached Files Attached Files

  14. #14
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,546

    Re: Lookup and show multiple items from list

    remove the rows no problem, I think it would be better with the original file format (with dummy data), so that will create helper column and create pivot table
    later you can add data (more rows)

  15. #15
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Lookup and show multiple items from list

    maybe try this one (done with PowerQuery/Get&Transform)

  16. #16
    Registered User
    Join Date
    05-18-2018
    Location
    US / UK / NL / DE
    MS-Off Ver
    Up to date Office 365 in various languages and Office 2010
    Posts
    83

    Re: Lookup and show multiple items from list

    Quote Originally Posted by nflsales View Post
    re.........I think it would be better with the original file format (with dummy data), so that will create helper column and create pivot table later you can add data (more rows)
    Helper column would increase the table with original data by 110000 cells wouldn't it ?
    Last edited by Duncan-; 05-18-2018 at 10:25 AM.

  17. #17
    Registered User
    Join Date
    05-18-2018
    Location
    US / UK / NL / DE
    MS-Off Ver
    Up to date Office 365 in various languages and Office 2010
    Posts
    83

    Re: Lookup and show multiple items from list

    Quote Originally Posted by JohnTopley View Post
    If you have 2 formulas that work ...???
    The formula 2 and 3 behave different with wild cards like ??? and * and I was wondering how Formula 1 would react to wild cards.
    Last edited by Duncan-; 05-18-2018 at 10:26 AM.

  18. #18
    Registered User
    Join Date
    05-18-2018
    Location
    US / UK / NL / DE
    MS-Off Ver
    Up to date Office 365 in various languages and Office 2010
    Posts
    83

    Re: Lookup and show multiple items from list

    Quote Originally Posted by JohnTopley View Post
    I

    Regarding the number of rows being variable: you can create dynamic name ranges which automatically adjust when rows are added / delete
    Thanks. Never used the range feature before

    But I still need to copy the formula "=IFERROR(INDEX(MyList,SMALL(IF(ISNUMBER(SEARCH($C$4,MyList)),ROW(MyList)-ROW($G$4)+1,""),ROWS($1:1))),"")" a 110000 times in order to see the max possible number of results. Correct ? edit some time later --> I think I did someting wrong in the formula name manager. Will do some more trouble shooting tonight.

    In order to use a flexible range would it be possible to refer to a table column on another sheet. I tried it but could not get it working.
    Last edited by Duncan-; 05-18-2018 at 10:19 AM.

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,231

    Re: Lookup and show multiple items from list

    The attached now has the named range on Sheet1.

    And you need drag the formula down for the likely maximum number of any criteria so if you never have more then 200 of any given item, then the formula "only" needs to in 200 rows.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    05-18-2018
    Location
    US / UK / NL / DE
    MS-Off Ver
    Up to date Office 365 in various languages and Office 2010
    Posts
    83

    Re: Lookup and show multiple items from list

    Thanks John and all other people posting to solve my problem !

    My formula is now as follows:

    Please Login or Register  to view this content.
    It works perfect and in variable C10 I can use wildcards like ? and *.

    Although my range with data actually starts at E5, not at E4. But when using E5 the results are incorrect.

    I would like to understand why this is because I have the feeling the problem lies elsewhere and not in the INDEX function.
    Last edited by Duncan-; 05-19-2018 at 03:10 AM.

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,231

    Re: Lookup and show multiple items from list

    TRy

    =IFERROR(INDEX(DATASHEET!$E$5:$E$107662,SMALL(IF(ISNUMBER(SEARCH($C$10,DATASHEET!$E$5:$E$107662)),ROW(DATASHEET!$E$5:$E$107662)-ROW($E$5)+1,""),ROWS($E$5:$E5))),"")

  22. #22
    Registered User
    Join Date
    05-18-2018
    Location
    US / UK / NL / DE
    MS-Off Ver
    Up to date Office 365 in various languages and Office 2010
    Posts
    83

    Re: Lookup and show multiple items from list

    Dang, now why did I miss that. Thanks. Now it makes sense. Thanks a lot.

    One more question. When using INDEX it seems impossible (at least I can not get it working) to refer to a table column in another sheet.

    Intead of
    INDEX(DATASHEET!$E$5:$E$107662 ..........................

    Something like:
    INDEX(NAMEOFTABLE[@NAMEOFCOLUMN] ..........................

  23. #23
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,231

    Re: Lookup and show multiple items from list

    I confess not being familiar with nor using tables: I am (probably through ignorance!) not a big fan as I find referencing data in formulae using tables long-winded and not very comprehensible. For example dragging table-based formula compared to range-based formula is (or appears to be) a pain.

    But to answer your question: you can reference a table in any sheet.

    It would help (as normal) to post a workbook.

    Attach a sample workbook (not image).so that we do not have to manually key in your data to do a testing.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  24. #24
    Registered User
    Join Date
    05-18-2018
    Location
    US / UK / NL / DE
    MS-Off Ver
    Up to date Office 365 in various languages and Office 2010
    Posts
    83

    Re: Lookup and show multiple items from list

    Quote Originally Posted by JohnTopley View Post
    .......For example dragging table-based formula compared to range-based formula is (or appears to be) a pain.
    Sometimes I do not have to drag (some how the formula is copied to all rows automatically) and when I do have to drag it works as dragging always works. Of course with 100000+ rows one can not drag (takes too much time) but has to copy & paste the formula.

    But to answer your question: you can reference a table in any sheet.
    That made me go back and double check everything. Took me about 45 minutes and got it working.

    Think the weird 2x(ctrl+enter) did the trick.

    Thank for all the help John
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Duncan-; 05-19-2018 at 04:00 PM.

  25. #25
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,231

    Re: Lookup and show multiple items from list

    Tables automatically expand when you add rows (one of the "claims to fame" of tables but no better in my view than using dynamic ranges).

    Note: you not need "wildcards" with SEARCH: in your posted example you will get the same result with "App" as the search criteria

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Last edited by JohnTopley; 05-20-2018 at 03:55 AM.

  26. #26
    Registered User
    Join Date
    05-18-2018
    Location
    US / UK / NL / DE
    MS-Off Ver
    Up to date Office 365 in various languages and Office 2010
    Posts
    83

    Re: Lookup and show multiple items from list

    Quote Originally Posted by JohnTopley View Post
    .............
    Note: you not need "wildcards" with SEARCH: in your posted example you will get the same result with "App" as the search criteria..........
    For my xls wildcards like ? and * are of utmost importance.

    In the example using "app" will result in 7 hits, using "???????app" will result in 6 hits. The last one ignores USapples, as it should. Perfect example the wildcards work as I need them.
    Last edited by Duncan-; 05-20-2018 at 10:09 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Adding items to list view - blank cells show as 0
    By Andrew Andromeda in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-03-2016, 07:02 AM
  2. show a list of items based on combo box selection
    By zak.horrocks in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-01-2014, 06:14 AM
  3. Replies: 1
    Last Post: 09-04-2014, 12:14 PM
  4. [SOLVED] How to show a list of items based on the date
    By ExarchMystix in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-23-2014, 03:13 AM
  5. Select Multiple Items in a List and then Print Those Items
    By dcdoc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-26-2013, 01:16 PM
  6. [SOLVED] Show a unique list of items based on Cell value
    By gorgon777 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-10-2013, 12:18 PM
  7. Replies: 1
    Last Post: 04-20-2013, 02:45 PM

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