+ Reply to Thread
Results 1 to 4 of 4

Return and concatenate values array formula

  1. 06-29-2017, 06:14 AM #1
    BWellman
    BWellman is offline
    Registered User
    Join Date
    12-23-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    85

    Return and concatenate values array formula

    Hey,

    I have a data table which has an ID, segment, Name, company, and score. There are three segments, and I am looking to return all of the name, companies, and scores for a given segment.

    The challenge is that the name and companies need to be concatenated into one cell, while the score can stay separate (i.e., ("Name" &"-"& "company")

    I know the first part is an array / index match formula that uses a row count to get rid of blanks, I'm just not sure how to combine that with a concatenate formula.

    I have included an example document for reference. I am looking to return the values into column I and J depending on the segment chosen in I2.

    Regards,
    B
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-29-2017, 07:00 AM #2
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Return and concatenate values array formula

    Hi B - paste this ARRAY FORMULA* in I6 and copy down:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    ...and this in J6:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    I assumed you wanted each person to appear only once on the list. If DUPLICATES are permissible, post # 3 is a better fit.

    *Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
    When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Attached Files Attached Files
    Last edited by leelnich; 06-29-2017 at 07:26 AM.
    Reply With QuoteRegister To Reply
  3. 06-29-2017, 07:10 AM #3
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Return and concatenate values array formula

    An alternative:

    In I6:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    In J6:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Both are array formulas, entered with Ctrl-Shift-Enter.
    Copy down.

    Hope that helps.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.
    Reply With QuoteRegister To Reply
  4. 06-29-2017, 02:40 PM #4
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Return and concatenate values array formula

    Thanks for the rep and for marking the thread as Solved.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Array formula to return list of values that match multiple criteria
    By TFiske in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-30-2016, 01:57 PM
  2. Array Formula To Return Unique Values From a Column Using a Value
    By lucas813 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 02-13-2015, 12:07 PM
  3. Array formula to return multiple values based on various (OR) criteria
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-20-2013, 05:12 AM
  4. [SOLVED] ARRAY Formula to return multiple values
    By Ricardo Mass in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-01-2013, 01:55 PM
  5. add to array formula to only return unique values
    By jason892 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-11-2013, 06:39 AM
  6. Array formula to return Max values or zero
    By Motox in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-17-2012, 09:18 PM
  7. Formula / array to return multiple values
    By paulmag in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2008, 11:52 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1