+ Reply to Thread
Results 1 to 2 of 2

Formula to find all matches in column A and return cells from column C

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    North Bay, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    60

    Formula to find all matches in column A and return cells from column C

    Hey guys,

    I need a formula to find ALL matching cells in column A and return their respective column C cells into column D combined

    For example:

    A1: 123 B1: 321 C1: 999
    A2: 123 B2: 356 C2: 977
    A3: 123 B3: 458 C3: 955

    In this case, cells A1, A2 and A3 are all equal so return "999 // 977 // 955" in cell D1

    Thanks,

    Pat.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Formula to find all matches in column A and return cells from column C

    Pat,

    Unfortunately, Excel's native CONCATENATE() function doesn't work with arrays. This means that there is no way Excel can perform what you're asking for without the use of VBA or an add-in. I have created a UDF for this task which I named ConcatAll. The UDF code and instructions for how to use it are located here:
    http://www.excelforum.com/tips-and-t...geravatar.html

    Attached is an example workbook based on the criteria you described.
    Headers are in row 1 so actual data starts in row 2
    Your provided sample data is in A2:C4 and there is additional sample data that goes down to row 10

    In cell F1 is this formula that gets the number of unique values listed in column A:
    Please Login or Register  to view this content.

    In cell E4 and copied down to cell E8 is this formula that returns the unique list of values from column A:
    Please Login or Register  to view this content.

    In cell F4 and copied down is this array formula that uses the UDF ConcatAll in order to get the results you're looking for:
    Please Login or Register  to view this content.

    Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter. That's how the formula gets surrounded by the curly braces {}, do not try to add those manually.
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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