如何高亮唯一重复值并按重复项汇总对应列数据？

Hey there! Dealing with duplicate values in column D and needing to sum up matching values in column G doesn't have to be a slow, manual grind—especially with large datasets. Below are two practical, time-saving methods to get this done quickly:

Method 1: Excel Built-in Tools (Quick & Straightforward) #

This works great if you need a fast solution without complex setup:

Step 1: Highlight Duplicate Values in Column D #

• Select the entire data range in column D
• Go to the Home tab → Conditional Formatting → Highlight Cells Rules → Duplicate Values
• In the pop-up box, pick your preferred highlight style (e.g., "Light Red Fill with Dark Red Text") and click OK. All duplicate entries in D will stand out instantly.
  • Pro tip: To highlight only first occurrences or subsequent duplicates, use the dropdown in the dialog to select "First Occurrence" or "Duplicate" instead of the default.

Step 2: Auto-Sum Column G for Each Duplicate Group #

Let’s say you want the sum results in column I (with a header like "Total G Value"):

• In cell I2, enter this formula:

  =SUMIF($D:$D, $D2, $G:$G)
  

• Drag the fill handle down to apply the formula to all rows. Every row will now show the total sum of G values for its corresponding D value.

Optional: Get Unique Values with Summaries #

If you don’t want repeated sum results, clean up the output:

• Copy columns D and I to a new range
• Select the new D column → Data tab → Remove Duplicates
• Confirm you only want to remove duplicates from column D, and you’ll end up with a clean list of unique D values paired with their total G sums.

Method 2: Power Query (Best for Large Datasets & Auto-Refreshes) #

For massive datasets where you might need to update data later, Power Query is your go-to—it automates the entire process and lets you refresh results in one click:

1. Import Data into Power Query

   • Select your full data range → Data tab → From Table/Range (check "My table has headers" if your data has column names)

2. Group & Sum the Data

   • In the Power Query Editor, select column D → go to the Transform tab → Group By
   • Configure the grouping dialog like this:
     • Group by: Choose your D column name (e.g., "ID" or "Category")
     • New column name: Enter something like "Total G"
     • Operation: Select Sum
     • Column: Pick your G column name
   • Click OK—you’ll immediately get a table with unique D values and their summed G totals.

3. Load Back to Excel & Highlight Duplicates (Optional)

   • Click Close & Load to bring the summarized table back to Excel
   • Use the conditional formatting method from Method 1 to highlight duplicates in the D column if needed.

4. Refresh Updated Data

   • If your source data changes later, just right-click the summarized table → Refresh—no manual re-calculation required!

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