+ Reply to Thread
Results 1 to 14 of 14

Excel - Returning multiple values in a single cell that match criteria

  1. #1
    Registered User
    Join Date
    04-14-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    5

    Excel - Returning multiple values in a single cell that match criteria

    I am trying to find a function that will help me do the following (without using VBA). So suppose i have the following data:

    http://i.imgur.com/Ql2Z7yR.jpg

    I need it to be the following format:

    http://i.imgur.com/OkRsbKE.jpg

    Basically, I need to get all the associated sku's (column F) from the parent_sku (column E) into column G comma delimited.

    Any help would be GREATLY appreciated!

  2. #2
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,023

    Re: Excel - Returning multiple values in a single cell that match criteria

    This is a excel forum
    Post a xls file and not a jpg.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: Excel - Returning multiple values in a single cell that match criteria

    It looks like you just want to CONCATENATE - check it out!
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Excel - Returning multiple values in a single cell that match criteria

    Here it is

    =CONCATENATE(F2,F3,F4,F5,F6)
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Registered User
    Join Date
    04-14-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Excel - Returning multiple values in a single cell that match criteria

    @xladept & @AlKey

    Thanks for the quick response. Unfortunately, a simple concatenate wouldn't work as the # of cells above each parent sku vary. I've tried a VLOOKUP but that only returns the first value from the parent_sku. See attached, the green column is how it's supposed the look, the yellow cells are where there needs to be those values.
    Attached Files Attached Files

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Excel - Returning multiple values in a single cell that match criteria

    Then there is another option with VB UDF where you can select a range and not pick individual cells. Are you open for VBA solution?

  7. #7
    Registered User
    Join Date
    04-14-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Excel - Returning multiple values in a single cell that match criteria

    While I'm very comfortable with excel, I'm very limited with regard to VBA. I feel as though there is function out there utilizing the INDEX/MATCH and VLOOKUP - or even something like a SMALL and then +1 until it goes through all the values associated with the parent sku and adds them to that cell...

  8. #8
    Registered User
    Join Date
    04-14-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Excel - Returning multiple values in a single cell that match criteria

    @AlKey were you referring to this site?
    http://www.get-digital-help.com/2010...into-one-cell/

    Works perfectly!!!

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Excel - Returning multiple values in a single cell that match criteria

    Yes, but there is no function will put all values in one cell.

    The vba solution is very easy just follow these simple instructions

    1.Press Alt+F11 on your keaboard. This will open VB Editor.
    2. At the top, click on Insert and click on Moduel
    3. Copy code below and paste it into Module
    4.Close Module and close VB Editor.

    Make sure to save your workbook as Micro-Enabled

    That's all.

    Code

    use formula =UnionCell(A1:A10)

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    04-14-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Excel - Returning multiple values in a single cell that match criteria

    Take a look at the attached file, it puts everything in one cell separated by a " " (which can be easily edited in the VBA script).

    D7 is where the function is.
    Attached Files Attached Files

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Excel - Returning multiple values in a single cell that match criteria

    Glad that your issue has been resolved

    Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: Excel - Returning multiple values in a single cell that match criteria

    Hi amazinz2006,

    ALKey's function works where the formula doesn't (namely in D14)???

  13. #13
    Registered User
    Join Date
    11-19-2012
    Location
    Wilmington Delaware
    MS-Off Ver
    Excel 2003 2007 2010
    Posts
    23

    Re: Excel - Returning multiple values in a single cell that match criteria

    The Merge Assistant, http://www.add-ins.com/merge_assistant.htm, has a feature that will do what you want

  14. #14
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Excel - Returning multiple values in a single cell that match criteria

    Thank you for your feedback!

    Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

+ 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. [SOLVED] How to return multiple values to a single cell based on multiple criteria
    By lwallace in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2014, 06:32 AM
  2. Returning a value to a single cell based on multiple values elsewhere.
    By archieross in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2012, 07:35 PM
  3. [SOLVED] How to return multiple values that match a single search criteria?
    By JSallen in forum Excel General
    Replies: 4
    Last Post: 11-28-2012, 11:49 AM
  4. Replies: 3
    Last Post: 09-06-2012, 09:07 AM
  5. returning entire row based on single or multiple criteria
    By cdevericks in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2008, 09:43 AM

Tags for this Thread

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