Page 1 of 5 123 ... LastLast
Results 1 to 10 of 49

Thread: LookUp Value and Concatenate All Found Results

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13

    LookUp Value and Concatenate All Found Results

    The idea for this UDF (user defined function) came from this article which I saw in another forum...

    Excel udf: Lookup and return multiple values concatenated into one cell | Get Digital Help - Microsoft Excel resource

    This is actually an old article that someone just commented on which, in turn, brought it to the top of that forum's "Recent Comments" list and, hence, to my attention. When I looked at the article, I decided the UDF presented by that programmer could be expanded to make it more flexible, hence this current article by me (you should follow the above link in order to see the type of data being processed and the expected resulting output from it). Here is the code I came up with...

    Code:
    Function LookUpConcat(ByVal SearchString As String, _
                          SearchRange As Range, _
                          ReturnRange As Range, _
                          Optional Delimiter As String = " ", _
                          Optional MatchWhole As Boolean = True, _
                          Optional UniqueOnly As Boolean = False, _
                          Optional MatchCase As Boolean = False)
                       
      Dim X As Long, CellVal As String, ReturnVal As String, Result As String
      
      If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
         (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
        LookUpConcat = CVErr(xlErrRef)
      Else
        If Not MatchCase Then SearchString = UCase(SearchString)
        For X = 1 To SearchRange.Count
          If MatchCase Then
            CellVal = SearchRange(X).Value
          Else
            CellVal = UCase(SearchRange(X).Value)
          End If
          ReturnVal = ReturnRange(X).Value
          If MatchWhole And CellVal = SearchString Then
            If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                    ReturnVal & Delimiter) > 0 Then GoTo Continue
            Result = Result & Delimiter & ReturnVal
          ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
            If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                    ReturnVal & Delimiter) > 0 Then GoTo Continue
            Result = Result & Delimiter & ReturnVal
          End If
    Continue:
        Next
        
        LookUpConcat = Mid(Result, Len(Delimiter) + 1)
      End If
      
    End Function
    The first three arguments are required. The first argument is the text you want to search for. The second argument is the range to be searched... this range can either be a column of cell references or a row of cell reference (it cannot be a two-dimensional array of cells though). The third argument is the range from which the matching results will be taken from... this range can be a column of cell references or a row of cell reference (it cannot be a two-dimensional array of cells though). Note that while normally the orientation of the search and result ranges will be the same (easier to coordinate between the two I would think), this is not a requirement... one can be a reference to a row of cells and the other can be a reference to a column of cells.

    The remaining arguments are all optional. The fourth argmument, a String, is the text (one or more characters) that you want to be the delimiter between the concatenated text that is found during the lookup (the default value is a space character). The fifth argument, a Boolean, determines whether the lookup should be trying to match the entire cell value or only part of the cell value (the default value is True meaning the lookup will have to match the entire cell value). The sixth argument, a Boolean, determines if the concatenated list that is returned should allow duplicated values to be listed or whether a found value should only be listed one time, that being the first occurrence in the range (the default value is False meaning found values will be listed as many times as they are found within the search range). The seventh argument, a Boolean, determines whether the lookup process will be case sensitive or not (the default value is False meaning the searched for text does not have to be of the same case as the text being searched).

    That is pretty much it. Hopefully my explanation has been clear enough but, if not, please feel free to ask me go into more detail on any parts that seem "murky" to you.

    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use LookUpConcat just like it was a built-in Excel function.




    https://www.youtube.com/@alanelston2330/featured

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=317218#p317218
    https://eileenslounge.com/viewtopic.php?p=316955#p316955
    https://eileenslounge.com/viewtopic.php?p=316955#p316955
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=317006#p317006
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316254#p316254
    https://eileenslounge.com/viewtopic.php?p=316280#p316280
    https://eileenslounge.com/viewtopic.php?p=315915#p315915
    https://eileenslounge.com/viewtopic.php?p=315512#p315512
    https://eileenslounge.com/viewtopic.php?p=315744#p315744
    https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
    https://eileenslounge.com/viewtopic.php?p=315680#p315680
    https://eileenslounge.com/viewtopic.php?p=315743#p315743
    https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
    https://eileenslounge.com/viewtopic.php?p=314950#p314950
    https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
    https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
    https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 05-20-2024 at 03:51 PM.

  2. #2
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Hi Rick

    I'd prefer
    Code:
    LookUpConcat = Mid(Result, 2)
    to
    Code:
    LookUpConcat = Mid(Result, Len(Delimiter) + 1)

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA
    https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg
    https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839t UQl_92mvg
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 09-13-2023 at 10:48 AM.

  3. #3
    Junior Member
    Join Date
    Jul 2012
    Posts
    4
    Rep Power
    0
    Thank you so much for this! :D This has enabled me to save an extraordinary amount of time on one of our admin tasks and will probably save whoever succeeds me in post several days work a month. The explanation of the arguments is especially helpful.

    I'm sure advanced users would do this anyway, but I just wanted to add that I had to lock the ranges when applying the lookup all down the column, or it would not look for the previously searched on values, which in my case I need it to do. This means my formula comes out =LookUpConcat(B3,$B$3:$B$6941,$AU$3:$AU$6941) rather than =LookUpConcat(B3,B3:B6941,AU3:AU6941) which is where I was originally going wrong.

    Applying the calculation to the whole column takes about 6 minutes if I just sit back and let it work.

    Thank you so much.


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA




    https://www.youtube.com/@alanelston2330/featured


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eekDyfS0CD
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eevG7txd2c
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg. 9dPo-OdLmZ09dc21kigjmr
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg. 9cXui6zzkz09cZttH_-2Gf
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxhXnQ-mWYhrHWuM354AaABAg.9bepnegjnRu9iMmBDtf4m1
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxFIZ858qf7w_uA9bd4AaABAg.9dKpEpUk3YT9d VEGnka6yj
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugz8oC8iGd6-SPhpaQZ4AaABAg.9bhRt-kPXri9brzh_99JF9
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugz8oC8iGd6-SPhpaQZ4AaABAg.9bhRt-kPXri9bsrQIgXb3L
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxwJDkFskrMW8EpcXt4AaABAg.9bmKMz5-Z1g9bmx0REIz41
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxhXnQ-mWYhrHWuM354AaABAg.9bepnegjnRu9bmyko2YUvQ
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxwJDkFskrMW8EpcXt4AaABAg.9bmKMz5-Z1g9bmzpPqfLRD
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZwbV_Y_7UFzHwNBh4AaABAg.9dKb0Vc7MOB9d VK8si3ont
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugx6Ec_r4kb9EYOVgIt4AaABAg.9dOW613fb8V9d VIJECZIdC
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwBho9tBLQ4nPVdYqd4AaABAg.9fWvoBWY3Da9g 9cLjhPiaz
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZy1NAMBx5Uv4U2cJ4AaABAg.9f0XX-_JaGp9g9bYLMZiIy
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyL-xp8IiiahmQ12kJ4AaABAg.9f7xHCpAEx29g9asFhVFfT
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxRxyFNNp3WHTzuiJJ4AaABAg.9fFR6ECmXk69g 9afNBcS4Z
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwsdMh0FGDfvA249_B4AaABAg.9fLR6FHCIVI9g 9aLlUyzog
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwBho9tBLQ4nPVdYqd4AaABAg.9fWvoBWY3Da9g 9_4422NzK
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugwyy8JXr56HJ8m_od94AaABAg.9gSFgqqJQNV9g TXco41b5l
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g TYl6RldpA
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g TfhAWU9ju
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g TfuYQGmUa
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g Tg3AmMPUc
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g TgEqh5wdo
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxmUK0S_aZVZWz8-gt4AaABAg.9gLc3DfWfHl9gTZ3y6fL1H
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZloYeY2wQr7-xTOh4AaABAg.9gB2bbbs9mB9gTZUkNYI8e
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzlM96nGEhW9J1Gpgd4AaABAg.9fmOFVcXZh49g T_8CYeQgz
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 09-22-2023 at 05:29 PM.

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    I am glad you were able to put the UDF in my article to such practical use... thank you so much for letting me know. As for using the absolute reference... you will find that necessary to do whenever referencing a fixed range from within a formula that you copy (drag down) into other cells.

    I was just wondering, though, do you really need "live" formulas for what you are doing or would a macro be more practical for you needs? I ask because you are loading up your worksheet with a lot of formulas for what looks to me like a task that only needs to be done infrequently for any give set of data. If a macro would be more useful, let me know and I'll convert the UDF into one for you.
    Last edited by Rick Rothstein; 07-03-2012 at 11:18 PM.

  5. #5
    Junior Member
    Join Date
    Jul 2012
    Posts
    4
    Rep Power
    0
    A macro would probably be more useful - I'm so out of practice with Excel that I didn't consider it :o, but the udf does seem to be overloading the workbook somewhat.



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 12-14-2023 at 02:57 AM.

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by theladysaphir View Post
    A macro would probably be more useful.
    Let me get some setup information from you first then; please answer the following questions...

    1. Will the macro always look for its words to match in Column B, or could the column vary from file to file?

    2. Will the macro always look in AU for the items to concatenate, or could the column vary from file to file?

    3. You show the lookup and concatenate lists going from Row 3 to Row 6941. I am guessing the starting row will always be Row 3, but that last row seems strange. Will it always be 6941 or should the macro simply look for the last row with data in the lookup column?

    4. What column should the output go to? Will it always be the same column for all the files you will use the macro against?

  7. #7
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Hi Rick,

    I was wondering whether this code could perform the same task (or am I overloooking something ?)

    Code:
    Function search_con_snb(sp As Range, c01 As String)
        sn = sp.Value
        For j = 1 To UBound(sn)
            c02 = c02 & "|" & Join(Application.Index(sn, j), "|")
        Next
        search_con_snb = Join(Filter(Split(c02, "|"), c01), "|")
    End Function
    sp is the range to look in
    c01 is the string to be looked for.
    | is the concatenation delimiter

    e.g to look for "rick" in range A1:B2000 and concatenate by a | all cell values in which 'rick' has been found and put the result into cell F1:

    =search_con_snb(A1:B2000,"rick")

    PS. Basically I consider every multiple search as a filter (autofilter, advancedfilter, VBA-filter) operation.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
    https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195
    https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314081#p314081
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314078#p314078
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314062#p314062
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314054#p314054
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313971#p313971
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313909#p313909
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 03-02-2024 at 02:58 PM.

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    I was wondering whether this code could perform the same task (or am I ovrloooking something ?)

    Code:
    Function search_con_snb(sp As Range, c01 As String)
        sn = sp.Value
        For j = 1 To UBound(sn)
            c02 = c02 & "|" & Join(Application.Index(sn, j), "|")
        Next
        search_con_snb = Join(Filter(Split(c02, "|"), c01), "|")
    End Function
    sp is the range to look in
    c01 is the string to be looked for.
    | is the concatenation delimiter
    Our functions are quite different from each other. My function looks in a row or column and, if it finds a match, can return concatentations of values from a different range (sort of like how SUMIF works, but with text and concatenation rather than numbers and summation). Within that functionality, the UDF allows the user to control whether the search is case sensitive or not and whether the search should match the entire contents of the cells it searches or just find the text somewhere within the cell. It also allows you to filter the return list so that all items are returned from the ReturnRange or so that repeated items are listed only once. Also, the function allows the delimiter to be set by the user rather than hard-coding it into the body of the function.
    Last edited by Rick Rothstein; 07-06-2012 at 07:15 AM.

  9. #9
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by theladysaphir View Post
    1+2. I will set up the instructions for other users so that the data will always be in column B/AU.
    3. The macro should simply look for the last row with data in the lookup column.
    4. I will set up the instructions so the output will always be column D.
    Okay, I started to set things up and I need clarification on one point please. In your earlier message, you said this...
    [I'm sure advanced users would do this anyway, but I just wanted to add that I had to lock the ranges when applying the lookup all down the column, or it would not look for the previously searched on values, which in my case I need it to do. This means my formula comes out =LookUpConcat(B3,$B$3:$B$6941,$AU$3:$AU$6941) rather than =LookUpConcat(B3,B3:B6941,AU3:AU6941) which is where I was originally going wrong.
    I am gathering from your last message that you put the the above formula in D3 and then copied it down to Row 6941 (the last row of data), is that correct? If so, that means column D will have multiple copies of the same concatenated text; that is, everytime B3 repeats itself in Column B (and there should be repeats or why else would you be using my LookUpConcat function), the concatenated list will be displayed "next to it" in Column D. Did you really want all those repeats? Or would putting the concatenated list next to (in Column D) the first occurrence of the lookup word be enough (leaving Column D's row blank for when the word is repeated later on)? Seems like the latter would be less cluttered and would allow you to find each word's concatentation easier.
    Last edited by Rick Rothstein; 08-01-2013 at 01:20 AM.

  10. #10
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by theladysaphir View Post
    You are correct in your interpretation of what I am doing, and in any other case I would prefer your suggested output, but in this particular case I need the output in every cell.
    Okay, here's the solution. Rather than create a new procedure, I simply wrote a macro that repeatedly calls my LookUpConcat function (while it can be used as a UDF, it is also a perfectly good VB function as well). So, put the LookUpConcat function in a module and then put this macro in the same module...
    Code:
    Sub LookUpAndConcatenate()
      Dim Cell As Range, LastRow As Long, ResultRow As Long
      Dim SearchRange As Range, ReturnRange As Range
      Const SearchCol As String = "B"
      Const ReturnCol As String = "AU"
      Const ResultCol As String = "D"
      Const StartRow As Long = 3
      LastRow = Cells(Rows.Count, SearchCol).End(xlUp).Row
      Set SearchRange = Cells(StartRow, SearchCol).Resize(LastRow - StartRow + 1)
      Set ReturnRange = Cells(StartRow, ReturnCol).Resize(LastRow - StartRow + 1)
      For Each Cell In SearchRange
        Cells(Cell.Row, ResultCol).Value = LookUpConcat(Cell.Value, SearchRange, ReturnRange)
      Next
    End Sub
    Then, when you want to fill the cells, just run the above LookUpAndConcatenate macro. Note that I used Const statements (they declare constants) to set the various parameters from your worksheet so that you can change them later on in case things change on your worksheet for some reason.

Similar Threads

  1. Replies: 5
    Last Post: 06-04-2013, 01:04 PM
  2. Replies: 6
    Last Post: 12-12-2012, 08:03 PM
  3. Lookup lookup lookup just can't make it work
    By work2live in forum Excel Help
    Replies: 1
    Last Post: 12-08-2012, 11:48 PM
  4. Excel found unreadable content
    By zyousafi in forum Excel Help
    Replies: 2
    Last Post: 08-08-2012, 10:41 AM
  5. Concatenate Multiple Lookup Values in Single Cell
    By Admin in forum Download Center
    Replies: 4
    Last Post: 04-06-2012, 09:07 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •