Announcement

Collapse
No announcement yet.

Return/Concatenate All Results Corresponding To Lookup Value

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.

    Comment


    • #3
      Re: Return All Results Corresponding To Lookup Value

      Hi blinx,

      Welcome to board!

      UDF.

      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
      Kris

      ExcelFox

      Comment


      • #4
        Re: Return/Concatenate All Results Corresponding To Lookup Value

        Thx, That's help

        Comment


        • #5
          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"




          Code:
          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

          Comment


          • #6
            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.

            Comment


            • #7
              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
              If the solution helped please donate to RSPCA

              Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

              Comment

              Working...
              X