Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Return/Concatenate All Results Corresponding To Lookup Value

  1. #1
    Join Date
    30th April 2008
    Posts
    2

    Return/Concatenate All Results Corresponding To Lookup Value

    My first post here,

    Have some trouble in excel

    For example I have this table
    A 1
    B 1
    A 5
    C 2
    A 2

    how to display the A result like this
    A 1;5;2 whereas the vlookup only return 1 value

    Thank you

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784
    If you wish to continue using this free service.

    Please be considerate of others who use the forum for searching. Your current Thread Title (which I will change) is non-reflective of your immediate problem.

    In future, please take 1 minute of your time to read the text on the New Thread page.

    Please take just 3 mins of your time to read: How To Get Your Question Answered...FAST! AND Anatomy Of A Good Thread Title

    REMEMBER: Your thread title should NEVER be what you THINK is your answer. 9 times out 10 it will wrong and prevent someone from finding a solution to a simliar issue.

  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Return All Results Corresponding To Lookup Value

    Hi blinx,

    Welcome to board!

    UDF.

    VB:
    Function KCONCAT(Var As Variant, _ 
        Optional Delim As String = ",", _ 
        Optional Exc As Boolean = True) As String 
         'Exc - Removing False from output
        Dim a, v 
        If TypeOf Var Is Range Or IsArray(Var) Then 
            a = Var 
            For Each v In a 
                KCONCAT = KCONCAT & Delim & v 
            Next 
            If Len(KCONCAT) > 1 Then 
                If Exc Then 
                    KCONCAT = Trim$(Replace(Replace(Mid$(Trim$(KCONCAT), 2), _ 
                    Delim & "False", ""), "False" & Delim, "")) 
                Else 
                    KCONCAT = Mid$(Trim$(KCONCAT), 2) 
                End If 
            End If 
        Else 
            KCONCAT = Var 
        End If 
    End Function 
    
    
    Use,

    =KCONCAT(IF(A1:A5=C1,B1:B5),";")

    where C1 houses A

    Array entered. To Enter the array formula hold down Ctrl and Shift while pushing Enter.

    HTH

  4. #4
    Join Date
    30th April 2008
    Posts
    2

    Re: Return/Concatenate All Results Corresponding To Lookup Value

    Thx, That's help

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    23rd April 2007
    Posts
    3,886
    The formula =ConcatIf(A:A,"A",B:B,";") should work with your situation.

    The UDF ConcatIf mirrors the syntax of SumIf

    =ConcatIf(compareRange,criteria,[stringsRange],[delimiter])

    =ConcatIf(compareRange,"<>") will return a string of all the cells in compareRange.

    Example:
    ColA ColB
    w 1
    x 2
    y 1
    z 1
    x 3
    y 1
    x 2
    t 2

    ConcatIf ($A:$A,"x",$B:$B,",")= "2,3,2"
    ConcatIf ($B:$B,1,$A:$A)="wyzy"




    VB:
    Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, _ 
        Optional ByVal stringsRange As Range, Optional Delimiter As String) As String 
        Dim i As Long, j As Long, criteriaMet As Boolean 
         
        Set compareRange = Application.Intersect(compareRange, _ 
        compareRange.Parent.UsedRange) 
         
        If compareRange Is Nothing Then Exit Function 
        If stringsRange Is Nothing Then Set stringsRange = compareRange 
        Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _ 
        stringsRange.Column - compareRange.Column) 
         
        For i = 1 To compareRange.Rows.Count 
            For j = 1 To compareRange.Columns.Count 
                If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then 
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j)) 
                End If 
            Next j 
        Next i 
        ConcatIf = mid(ConcatIf, Len(Delimiter) + 1) 
    End Function 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th April 2012
    Posts
    7

    Re: Return All Results Corresponding To Lookup Value

    Hi,

    This UDF is a massive help. However I was wondering how would it be possible to start each returned value on a new line within the cell that it returns?

    Curently my data returns like the below:

    Dixon,Carlsen H,Bull,Richard,Benning,Chris,Ali,Daryl


    But would like it to return like so:

    Dixon,Carlsen H
    Bull,Richard,
    Benning,Chris,
    Ali,Daryl

    I know this maybe possible perhaps with the CHAR formula nested in, but not sure how.

    Thanks in advance.

    A.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    9th April 2007
    Location
    Alstonville, Straya
    Posts
    5,703

    Re: Return/Concatenate All Results Corresponding To Lookup Value

    Hi thaatrain,
    This is a very old thread . can you please start your own new thread and reference it back to this thread via the link:
    http://www.ozgrid.com/forum/showthread.php?t=90243

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Lookup & Concatenate Results
    By dakke in forum Excel General
    Replies: 2
    Last Post: February 28th, 2008, 07:53
  2. Return Lookup Results Of All Occurences
    By Windy58 in forum Excel General
    Replies: 9
    Last Post: January 31st, 2008, 06:04
  3. Lookup & Return All Results For Found Value
    By squirtle in forum Excel General
    Replies: 8
    Last Post: November 29th, 2007, 04:43
  4. Return Lookup Results Between Two Tables
    By Pedrohern in forum Excel General
    Replies: 7
    Last Post: August 30th, 2007, 18:59
  5. Return All Matching Results of a Lookup
    By wingam00 in forum Excel General
    Replies: 9
    Last Post: September 6th, 2006, 00:19

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
  •  
porno