Search code examples
textjoin

How to Textjoin on resetting row number

Im trying to use the textjoin function in excel. i want to merge the text if column A is same and column B is same, then it should merge column G with an resetting counter on column E.

I hope the picture describes it more clearly

enter image description here

Solution

  • If you need more than two rows to be concantenated you can use a helper column like this:

    • Column A: id1 your first identifier
    • Column B: id2 your second identifier
    • Column C: text the text to be concatenated
    • Column D: helper: recursively concatenate with row below:
      =IF(AND(A2=A3; B2=B3); C2 & " - " & D3; C2) starting in cell D2
    • Column E: result: only show first row of matching block:
      =IF(AND(A2=A1; B2=B1); ""; D2) starting in cell E2

    Result:

    enter image description here

    If you have a non-english version of Excel you will have to translate the function names IF and AND accordingly.