Trying to combine range E4:I into J4:J with the Array Formula of:

=ArrayFormula( regexreplace( regexreplace( transpose( trim( query( transpose( regexreplace( trim( E4:I ) , " " , "~" ) ) ,, 9^99 ) ) ) , " " , ", " ) , "~" , " " ) )

This formula works well but I want to exclude duplicates in each row.

Here is a link to the sheet.

CodePudding user response：

You can try this modified formula from a similar post:

=INDEX(
  REGEXREPLACE(SORT(
    SPLIT(
      TRANSPOSE(QUERY(
        QUERY(
          UNIQUE(
            SPLIT(
              FLATTEN(
                ROW(E2:I11) & "♦♥" & E2:I11
              ), "♥"
            ) & ", "
          ),
          "SELECT MAX(Col2)
           WHERE Col2 IS NOT NULL
           GROUP BY Col2
           PIVOT Col1",
        ),,99
      )),"♦"
    ), 1, TRUE),
    "^(?:,\s*) |(?:,\s*) $|(,\s)\s*(?:,\s*)*",
    "$1"
  ),,2)

Output:

Note:

• Output is automatically sorted alphabetically
• You can change E2:I11 to E2:I but it will be slower than limiting your range. If you have a column that can identify until what row is populated, add an IF statement and limit your formula to only process those rows.

Reference:

• https://stackoverflow.com/a/67837006/17842569