+ Reply to Thread
Results 1 to 11 of 11

CONCATENAT IF. Search Criteria Range and Concatenate Corresponding Range if Found

  1. #1
    Registered User
    Join Date
    06-24-2013
    Location
    Vancouver
    MS-Off Ver
    Excel 2010
    Posts
    25

    CONCATENAT IF. Search Criteria Range and Concatenate Corresponding Range if Found

    This is essentially a CONCATENATE IF question.

    In one column I have a Range of Criteria. In a second column I have the range that needs to be searched. If the criteria is found then corresponding cells located in a third column should be concatenated in a fourth column.

    Another way to word it is:
    Search through all data in column A for items located in Column C. If data in Column C is found in Column A then concatenate ALL cells in column D that are on in same corresponding row as the data found. Make Sense?!

    Note that ranges will very.

    Please see the attached Test Sheet for a quick example.

    What is the formula for this? (I'm looking for a formula to achieve the result rather than VBA)

    Thanks is advance for the help.
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,402

    Re: CONCATENAT IF. Search Criteria Range and Concatenate Corresponding Range if Found

    In cases like this you will need VBA. The User Defined Function below will do this. It is not mine. It states that it was written by tigeravatar.

    If you aren’t familiar with how to install one of these copy the VBA code below. Then in Excel press Alt + F11 function key. Click Insert > click Module. A VBA editing window will appear. Paste this code into that. Exit the VBA editor.

    Then array-enter this formula in F2 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    And here is the UDF code. You will need to save your file as an xlsm macro-enabled file.

    Please Login or Register  to view this content.
    Last edited by FlameRetired; 11-04-2015 at 12:18 AM.
    Dave

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,701

    Re: CONCATENAT IF. Search Criteria Range and Concatenate Corresponding Range if Found

    Or

    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    Please Login or Register  to view this content.

    In F2 Cell

    =cLookup(E2,$A$2:$A$16,$C$2:$C$16)


    Drag it down
    Last edited by :) Sixthsense :); 11-04-2015 at 12:53 AM.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,898

    Re: CONCATENAT IF. Search Criteria Range and Concatenate Corresponding Range if Found

    or a nice simple UDF (shamelessly copied by me from Jacc), which will also put in the commas, which currently look very out of place in your results column. The formula must be array entered (CTRL-SHIFT-ENTER).

    Please Login or Register  to view this content.
    =concatY(IF($A$2:$A$16=$E2,$C$2:$C$16,""))
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 11-04-2015 at 04:43 AM.
    Glenn



  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,701

    Re: CONCATENAT IF. Search Criteria Range and Concatenate Corresponding Range if Found

    What is the need of Array entry when it is UDF?

    Let the UDF to do that array task inside the code

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,898

    Re: CONCATENAT IF. Search Criteria Range and Concatenate Corresponding Range if Found

    Sixthsense. I can plagiarise the VBA of others, but (to my shame) cannot write my own. So... How do I do that?

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,701

    Re: CONCATENAT IF. Search Criteria Range and Concatenate Corresponding Range if Found

    Quote Originally Posted by Glenn Kennedy View Post
    Sixthsense. I can plagiarise the VBA of others, but (to my shame) cannot write my own. So... How do I do that?
    I am aware since you mentioned in post #4.

    Accessing the excel cells each time in code is not the better approach when it comes to speed. Loading the range/cells values in array (Variable) and looping through the array will work faster.

    Please refer Post #3 code for further info.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,898

    Re: CONCATENAT IF. Search Criteria Range and Concatenate Corresponding Range if Found

    Hehe

    i do not think that you realse the depth of my ignorance here. Can you translate this into English??

    Accessing the excel cells each time in code is not the better approach when it comes to speed. Loading the range/cells values in array (Variable) and looping through the array will work faster.

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,701

    Re: CONCATENAT IF. Search Criteria Range and Concatenate Corresponding Range if Found

    Quote Originally Posted by Glenn Kennedy View Post
    Can you translate this into English??
    Accessing the excel cells each time in code is not the better approach when it comes to speed. Loading the range/cells values in array (Variable) and looping through the array will work faster.
    In your suggested code

    Please Login or Register  to view this content.
    If you see your suggested code the c refers to each cell in $C$2:$C$16 range. See how many times the each cells are verifying the value of c cell.

    In my code

    Please Login or Register  to view this content.
    In my suggested code I straight away assigned the selected range of cells value in array variable and accessing the stored (in array) values is much faster than accessing the excel cell each time.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,898

    Re: CONCATENAT IF. Search Criteria Range and Concatenate Corresponding Range if Found

    OK. I'll go away and play with this and see if/when I get stuck!!

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,701

    Re: CONCATENAT IF. Search Criteria Range and Concatenate Corresponding Range if Found

    Please go through the snb website given below which will describes the methods/usage/advantages of using array.

    http://www.snb-vba.eu/VBA_Arrays_en.html

+ 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: 6
    Last Post: 12-30-2014, 02:12 PM
  2. Replies: 4
    Last Post: 08-04-2014, 04:48 AM
  3. Concatenate a range based on Criteria
    By nuruedriss in forum Excel General
    Replies: 1
    Last Post: 11-06-2013, 10:32 AM
  4. [SOLVED] Search for value in range and clear value when found
    By 3foo3 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-30-2013, 08:27 PM
  5. Replies: 2
    Last Post: 10-23-2012, 11:09 AM
  6. [SOLVED] Populate Listbox with a range from one worksheet (criteria) and remove if item found
    By Foreverlearning in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 05-08-2012, 07:31 AM
  7. [SOLVED] Search a Range for a phrase and Format cell if found
    By Leaper@FakeEail.cooo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-26-2005, 06:05 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