+ Reply to Thread
Results 1 to 7 of 7

Return nth text value from list of other text

  1. #1
    Registered User
    Join Date
    08-27-2019
    Location
    UK
    MS-Off Ver
    Windows 10
    Posts
    3

    Return nth text value from list of other text

    Hi all,

    I have a problem that I cannot get my head around, I was wondering if anyone had any insight;

    I have a list on 'sheet1' column A - listing say fruits. This list is dynamic and changes on user selection- it can be longer or shorter.

    A
    Banana
    Cherry
    Dragon fruit

    On 'sheet2' column A, I have many unsorted duplicates of the same text (fruits), each with their own individual data in adjacent rows - lets say weight in column B:


    A B
    Fruit Weight

    Apple 52
    Apple 48
    Banana 120
    Grape 7
    Grape 7
    Cherry 10
    Apple 52
    Banana 127
    Cherry 11
    Dragon fruit 700


    I want to pull all instances of fruits in 'sheet2' that appear in sheet1 column A, and paste them in a sorted fashion in a column without any spaces.

    So in this case

    Banana 120
    Banana 127
    Cherry 10
    Cherry 11
    Dragon fruit 700

    In a nutshell, the purpose of this is to return all data in a single column for those fruits which a user has selected.

    There are over 15000 rows in the data set and for some reasons it is not feasible to re-sort the data in Sheet2.

    How might one get excel to pull all text duplicates from a list based on another list of text values?

    I tried offsetting an index match, but the offset needs to be dynamic and take into account many duplicates, and duplicates the sheet has already returned, and I cannot figure out how to do so.

    Any help is much appreciated!
    Last edited by OIOIOIOIOIO; 08-28-2019 at 08:15 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Return nth text value from list of other text

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

    2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    3. Make sure that your desired solution is also shown (mock up the results manually).

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Registered User
    Join Date
    08-27-2019
    Location
    UK
    MS-Off Ver
    Windows 10
    Posts
    3

    Re: Return nth text value from list of other text

    Hi Glenn,

    I've just mocked one up, thank you.data_example.xlsx

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Return nth text value from list of other text

    Have you looked at advanced filters?

    https://www.ablebits.com/office-addi...vanced-filter/
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Return nth text value from list of other text

    In B2, copied down:
    =COUNTIF(Sheet2!A:A,Sheet1!A2)

    In C3, copied down:
    =IFERROR(INDEX($A$2:$A$4,MATCH(0,INDEX(--(COUNTIF($C$2:C2,$A$2:$A$4)=$B$2:$B$4),0),0)),"")

    In D3, copied across and down:
    =IFERROR(INDEX(Sheet2!B:B,AGGREGATE(15,6,ROW(Sheet2!$A$3:$A$21)/(Sheet2!$A$3:$A$21=Sheet1!$C3),COUNTIF($C$3:C3,C3))),"")
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-27-2019
    Location
    UK
    MS-Off Ver
    Windows 10
    Posts
    3

    Re: Return nth text value from list of other text

    Quote Originally Posted by Glenn Kennedy View Post
    In B2, copied down:
    =COUNTIF(Sheet2!A:A,Sheet1!A2)

    In C3, copied down:
    =IFERROR(INDEX($A$2:$A$4,MATCH(0,INDEX(--(COUNTIF($C$2:C2,$A$2:$A$4)=$B$2:$B$4),0),0)),"")

    In D3, copied across and down:
    =IFERROR(INDEX(Sheet2!B:B,AGGREGATE(15,6,ROW(Sheet2!$A$3:$A$21)/(Sheet2!$A$3:$A$21=Sheet1!$C3),COUNTIF($C$3:C3,C3))),"")
    Glenn this is astonishing, there's no way i'd have figured this out in a million years. Thank you so much.

    Quote Originally Posted by AliGW View Post
    Have you looked at advanced filters?
    This also looks like it could solve some elements of the problem. I am not familiar with adavnce features but will check them out.

    Thank you both.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Return nth text value from list of other text

    Of course, I wouldn't have bothered trying to help if I hadn't had a sample sheet to work with. I'm kinda lazy!!

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. Replies: 5
    Last Post: 04-06-2019, 02:18 AM
  2. Replies: 4
    Last Post: 08-28-2018, 12:40 PM
  3. [SOLVED] Find list of text in row and then return offset of matched text?
    By nobodyukno in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-05-2017, 12:00 PM
  4. Replies: 10
    Last Post: 09-18-2014, 09:36 AM
  5. [SOLVED] Check to see if a cell contains text from a list then return the corresponding text
    By crousseau989 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-21-2013, 12:06 PM
  6. Replies: 3
    Last Post: 06-17-2013, 01:09 PM
  7. [SOLVED] Look-up specific text and return a list of the satisfied text
    By careng in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-06-2013, 01:13 PM

Tags for this Thread

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