+ Reply to Thread
Results 1 to 6 of 6

Returning multiple matches and and displaying in a single cell with using UDF

  1. #1
    Registered User
    Join Date
    02-10-2015
    Location
    Bozeman, Montana
    MS-Off Ver
    2013
    Posts
    9

    Returning multiple matches and and displaying in a single cell with using UDF

    Hello everyone, This is my first post so I hope I provide the appropriate information to help come up with a working solution.

    First I will start with my requirements:
    1. It has to work with SharePoint and the Excel viewer/editor.
    2. It can't use UDF because of #1
    3. It has to use multiple workbooks because one is replaced daily by an automated system. (I only need to read from this workbook)

    Workbook 1 (This is where data entry and formulas reside)

    ProductID (A1) TrackingIDS (B1)
    AH0001 12345, 67890
    AH0002 123, 456, 789

    WorkBook 2

    TrackingIDs (A1) ProductID (B1)
    12345 AH0001
    67890 AH0001
    123 AH0002
    456 AH0002
    789 AH0002


    Worksheet 1 column B needs to be populated with the values from Worksheet 2 where Worksheet 1 Column A equals Worksheet 2 Column B.

    Any number of rows will need to be returned by the formula. It could be 0 or it could be 20.

    I have made this work with UDF but I need to do it with a formula if possible due to the SharePoint and Excel Web component requirements.

    Thanks for all your help in advance.
    Last edited by achimbos; 02-11-2015 at 01:24 PM. Reason: Additional info

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

    Re: Returning multiple matches and and displaying in a single cell with using UDF

    There are going to be limits. I set a maximum of 5 Tracking ID's per product in this ARRAYED Formula in B2

    =TRIM(IFERROR(INDEX(Sheet1!$A$2:$A$6, SMALL(IF(Sheet1!$B$2:$B$6=A2, ROW($A$2:$A$6)-ROW($A$2)+1),1)),"")&" "&IFERROR(INDEX(Sheet1!$A$2:$A$6, SMALL(IF(Sheet1!$B$2:$B$6=A2, ROW($A$2:$A$6)-ROW($A$2)+1),2)),"")&" "&IFERROR(INDEX(Sheet1!$A$2:$A$6, SMALL(IF(Sheet1!$B$2:$B$6=A2, ROW($A$2:$A$6)-ROW($A$2)+1),3)),"")&" "& IFERROR(INDEX(Sheet1!$A$2:$A$6, SMALL(IF(Sheet1!$B$2:$B$6=A2, ROW($A$2:$A$6)-ROW($A$2)+1),4)),"")&" "&IFERROR(INDEX(Sheet1!$A$2:$A$6, SMALL(IF(Sheet1!$B$2:$B$6=A2, ROW($A$2:$A$6)-ROW($A$2)+1),5)),""))

    I have a simple space separator because if you use commas, you end up with extra commas at the end of the string.
    ...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. Press F2 on that cell and try again.
    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
    Registered User
    Join Date
    02-10-2015
    Location
    Bozeman, Montana
    MS-Off Ver
    2013
    Posts
    9

    Re: Returning multiple matches and and displaying in a single cell with using UDF

    Thank you for your quick response and working example file. I will see if I can make this work since my internal customer is looking for more than 5. By the looks of this I can get it to go a little higher with some manipulation.

  4. #4
    Registered User
    Join Date
    02-10-2015
    Location
    Bozeman, Montana
    MS-Off Ver
    2013
    Posts
    9

    Re: Returning multiple matches and and displaying in a single cell with using UDF

    After further testing I have run into a problem and could use further assistance. I was clear about my datasets and this doesn't appear to work as I get past the first 6 rows. Both worksheets could have an infinite number of rows. I have tried replacing the ranges with a larger range and it's returning a blank field for the results.

    Thanks.

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

    Re: Returning multiple matches and and displaying in a single cell with using UDF

    Here is an example of it for 6 duplicates and rows 2 to 600. This formula takes a lot of memory and the larger the range, the longer it will take to calculate (depending on your computer). It may not have been returning a blank field, just not done calculating. You may have to go get a cup of coffee or tea. If your range is too large, it becomes impractical so keep it as small as possible. The range of "infinite" that you suggested is definitely too large.

    =TRIM(IFERROR(INDEX(Sheet1!$A$2:$A$600, SMALL(IF(Sheet1!$B$2:$B$600=A2, ROW($A$2:$A$600)-ROW($A$2)+1),1)),"")&" "&IFERROR(INDEX(Sheet1!$A$2:$A$600, SMALL(IF(Sheet1!$B$2:$B$600=A2, ROW($A$2:$A$600)-ROW($A$2)+1),2)),"")&" "&IFERROR(INDEX(Sheet1!$A$2:$A$600, SMALL(IF(Sheet1!$B$2:$B$600=A2, ROW($A$2:$A$600)-ROW($A$2)+1),3)),"")&" "& IFERROR(INDEX(Sheet1!$A$2:$A$600, SMALL(IF(Sheet1!$B$2:$B$600=A2, ROW($A$2:$A$600)-ROW($A$2)+1),4)),"")&" "&IFERROR(INDEX(Sheet1!$A$2:$A$600, SMALL(IF(Sheet1!$B$2:$B$600=A2, ROW($A$2:$A$600)-ROW($A$2)+1),5)),"") & “ “ & IFERROR(INDEX(Sheet1!$A$2:$A$600, SMALL(IF(Sheet1!$B$2:$B$600=A2, ROW($A$2:$A$600)-ROW($A$2)+1),1)),""))

  6. #6
    Registered User
    Join Date
    02-10-2015
    Location
    Bozeman, Montana
    MS-Off Ver
    2013
    Posts
    9

    Re: Returning multiple matches and and displaying in a single cell with using UDF

    Thanks for all your help. I was able to get the range expanded late last night and it's all working fine.

+ 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: 2
    Last Post: 04-30-2014, 05:59 AM
  2. Concetenate multiple matches into a single cell
    By penfolda in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2013, 06:57 AM
  3. Returning a value to a single cell based on multiple values elsewhere.
    By archieross in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2012, 07:35 PM
  4. Displaying Multiple Cell Information in Single Cell
    By SamuelT in forum Excel General
    Replies: 5
    Last Post: 01-23-2006, 11:35 AM
  5. [SOLVED] Displaying the results of multiple formulas in a single cell.
    By gallegos1580 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-12-2005, 01:06 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