Google Sheet订单数据聚合需求：合并重复姓名展示关联商品

Got it, let's tackle this Google Sheets order consolidation problem—perfect for cleaning up data to make receipts. Here are a couple of straightforward, reliable methods to get exactly the grouped, deduplicated view you want:

Method 1: TEXTJOIN + UNIQUE + FILTER Combo (Works on All Sheets Versions) #

This method is great if you need compatibility with older Google Sheets versions, or want more control over each step:

• First, extract unique customer names: In a blank column (e.g., Column C), enter =UNIQUE(A2:A) in cell C2. This will automatically list every distinct name from your original data.
• Next, merge deduplicated products for each name: In cell D2, enter =TEXTJOIN(" - ", TRUE, UNIQUE(FILTER($B$2:$B, $A$2:$A=C2))), then drag the fill handle down to apply it to all names.
  • Breakdown: FILTER grabs all products linked to the current name, UNIQUE removes duplicate entries, and TEXTJOIN stitches the remaining products together with " - ".

Method 2: QUERY Function (One-Step, Clean Solution) #

If you're using a newer Google Sheets version, the STRING_AGG function makes this a one-liner:
In a blank cell (e.g., C1), paste this formula:

=QUERY(A2:B, "SELECT A, STRING_AGG(DISTINCT B, ' - ') WHERE A IS NOT NULL GROUP BY A LABEL A 'NAME', STRING_AGG(DISTINCT B, ' - ') 'PRODUCT'", 1)

• Breakdown:
  • SELECT A, STRING_AGG(DISTINCT B, ' - '): Picks the name column and merges unique products with " - " separators
  • WHERE A IS NOT NULL: Skips any empty rows in the name column
  • GROUP BY A: Groups all entries by customer name
  • LABEL ...: Sets clear headers for your consolidated table

Raw Data vs. Expected Result #

WHAT WE HAVE:
NAME - PRODUCT
DANIEL - ORANGE
ALICE - PINEAPPLE
ALICE - APPLE
ROBERT - BANANA
ROBERT - BANANA
ROBERT - KIWI

WHAT WE WOULD LIKE TO ACCOMPLISH:
NAME - PRODUCT
DANIEL - ORANGE
ALICE - PINEAPPLE - APPLE
ROBERT - BANANA - KIWI

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