Google Sheets无插件实现重复姓名列合并且不影响行数据

Got it, let's work through this. You've already got a solid FILTER formula pulling in the exact employee data you need, and now you want to visually merge duplicate names in Column A while keeping every task row intact. Here's how to do it without any add-ons:

Method 1: Use LET for Clean, Readable Formula (Recommended for Newer Google Sheets) #

Replace your existing FILTER formula in cell C26 with this combined formula. It will handle both the filtering and the duplicate name display logic:

=ARRAYFORMULA(
  LET(
    filtered_data, FILTER(A3:G22, ARRAYFORMULA(ISNUMBER(MATCH(A3:A22,{"Mary";"Jason";"Ana";"Jen";"Ben";"Helen";"Dan";"Richard";"Breg"},0)))),
    names, INDEX(filtered_data,,1),
    IF(COUNTIF(OFFSET(names,0,0,ROW(names)-ROW(INDEX(names,1,1))+1,1),names)=1, names, "")
  )
)

How this works: #

• LET lets us define variables to keep the formula tidy:
  • filtered_data is your original filtered dataset (the same result as your current FILTER formula)
  • names extracts just the first column (employee names) from the filtered data
• The COUNTIF + OFFSET combo checks how many times each name has appeared up to the current row. If it's the first occurrence, it shows the name; if it's a repeat, it shows an empty string. This creates the "merged" visual effect while keeping all task rows intact.

Method 2: Compatible with Older Google Sheets (No LET Function) #

If you're using an older version of Google Sheets that doesn't support LET, use this alternative formula instead:

=ARRAYFORMULA(
  IF(
    COUNTIF(
      INDIRECT("R"&ROW(INDEX(FILTER(A3:G22,ARRAYFORMULA(ISNUMBER(MATCH(A3:A22,{"Mary";"Jason";"Ana";"Jen";"Ben";"Helen";"Dan";"Richard";"Breg"},0))),1,1))&"C"&COLUMN(INDEX(FILTER(A3:G22,ARRAYFORMULA(ISNUMBER(MATCH(A3:A22,{"Mary";"Jason";"Ana";"Jen";"Ben";"Helen";"Dan";"Richard";"Breg"},0))),1,1))&":R"&ROW(INDEX(FILTER(A3:G22,ARRAYFORMULA(ISNUMBER(MATCH(A3:A22,{"Mary";"Jason";"Ana";"Jen";"Ben";"Helen";"Dan";"Richard";"Breg"},0))),,1))&"C"&COLUMN(INDEX(FILTER(A3:G22,ARRAYFORMULA(ISNUMBER(MATCH(A3:A22,{"Mary";"Jason";"Ana";"Jen";"Ben";"Helen";"Dan";"Richard";"Breg"},0))),1,1)),
      INDEX(FILTER(A3:G22,ARRAYFORMULA(ISNUMBER(MATCH(A3:A22,{"Mary";"Jason";"Ana";"Jen";"Ben";"Helen";"Dan";"Richard";"Breg"},0))),,1)
    )=1,
    INDEX(FILTER(A3:G22,ARRAYFORMULA(ISNUMBER(MATCH(A3:A22,{"Mary";"Jason";"Ana";"Jen";"Ben";"Helen";"Dan";"Richard";"Breg"},0))),,1),
    ""
  )
)

Key Notes: #

• This method doesn't alter or delete any rows—all task data for each employee remains fully accessible. It just hides duplicate names visually to create a cleaner merged look.
• If you want to make the empty cells blend even better, you can apply conditional formatting to set the font color of empty name cells to match your sheet's background.

内容的提问来源于stack exchange，提问作者user12722902