Share via

Lookup/Concatenate Multiple Values into one cell

Anonymous
2019-09-23T16:31:34+00:00

Hi

I was wondering if there was a way to concatenate or Lookup values from multiple rows into one cell where I have rows for the same employee.

Basically If Jon has 3 rows with the same ID in each of the rows but each row has a different absence reason

Name  ID Reason

JON     1   Sick

JON     1   Holiday

JON     1   AWOL

I want to have just one row, with one cell containing all 3 reasons. Is this possible?

Thanks you

JT

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Vijay A. Verma 104.8K Reputation points Volunteer Moderator
    2019-09-23T17:00:19+00:00

    Hi GreenRaisin

    Greetings! I am Vijay, an Independent Advisor. If you are on Office 365, then you can use TEXTJOIN function which will have to be entered as Array formula

    =TEXTJOIN(", ",TRUE,IF(A2:A10="Jon",C2:C10,""))

    Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

    If you are not on Office 365 (as you have specified Office 2016), then VBA function is the only way out.

    Let me know.

    40+ people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-09-23T17:11:52+00:00

    Thank you so much this works!!

    1 person found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2019-09-23T16:55:51+00:00

    You can create a user-defined VBA function for this purpose. See http://www.eileenslounge.com/viewtopic.php?f=27&t=12298#p91187

    0 comments
  3. Anonymous
    2019-09-23T16:50:54+00:00

    Elsewhere in a seperate column that contains only 1 ID for each employee which concats JoNs three values into the one row. Im sure there is a way to use an array but hitting f9 to bring back the values.

    0 comments
  4. HansV 462.6K Reputation points MVP Volunteer Moderator
    2019-09-23T16:42:28+00:00

    Do you want to replace the three rows for JON with one row, or do you want a formula elsewhere that concatenates JON's values?

    0 comments