How to Sum Numeric Values When Merging Duplicates

To merge duplicates and sum numeric values, group rows by a stable identifier such as SKU, choose the record to keep, and add the numeric values from every member of that group. Datablist's Duplicates Finder lets you describe these rules, preview the generated script, and then apply it.

I start by checking what each row represents. Sum separate contributions, such as stock in different warehouses. If two rows are repeated exports of the same stock count, adding them would double-count the inventory. Keep the latest snapshot instead.

For a separate column-wide check, use the CSV numeric calculations walkthrough to inspect totals and ranges before or after merging. Those calculations summarize values; they do not decide which rows are duplicates.

For example, three rows for SKU A-100 with stock of 12, 8, and 5 from three different warehouses should produce one row with stock 25. Include warehouse or transaction identifiers when checking whether a contribution has already been counted.

This guide will walk you through the exact steps to sum numeric values while merging duplicates:

• Setting Up Your Data
• How to find duplicates
• How to Sum Values while merging duplicates

Setting Up Your Data

Before you can merge and sum values, you need your data in Datablist. Let's start by creating a collection and importing your file.

Step 1: Create a Collection

In Datablist, collections are where you organize your data. Think of them like smart spreadsheets. Click the '+' button in the sidebar or the "Start with a CSV/Excel file" shortcut on the home screen to create a new one.

Step 2: Import Your File

You can import data from various sources, but for this example, let's use a CSV or Excel file. Click "Import CSV/Excel" and upload your file.

Datablist supports large files, so don't worry if you have hundreds of thousands of rows.

Datablist automatically detects column types (like Text, Number, Date, Email).

⚠️ Important: Make sure the column you want to sum (e.g., /Stock) is correctly identified as a Number type. If not, you can manually change it during the import process.

Step 3: Check Your Imported Data

Once the import finishes, take a quick look at your collection to ensure everything looks correct. You should see your rows and columns, ready for cleaning.

Finding Duplicates

Now that your data is loaded, let's find those duplicates.

Step 4: Start the Duplicates Finder

Open Clean > Merge duplicates.

Step 5: Select Your Deduplication Identifier(s)

You need to tell Datablist how to identify duplicate items.

Choose the property that uniquely identify an item in your list. This could be an Email address, a Product SKU, a Company Name, or any combination of fields.

For this stock example, use SKU to group the same product. A product name alone may match different sizes or variants, so use it only after checking those distinctions.

Step 6: Choose Matching Algorithm and Processor

Next, select the comparison algorithm.

For a product SKU, start with Exact so different identifiers remain separate. For email or URL fields, Smart can normalize variations through the relevant processor. Review those settings before accepting a match. The Smart matching explanation shows what each normalization stage changes.

You can also choose a 'Processor' to normalize data before comparison (e.g., cleaning email addresses or URLs).

Click Run duplicates check.

Step 7: Preview Duplicate Groups

Datablist will analyze your data and present groups of potential duplicates. Review these groups to confirm they are indeed duplicates before proceeding to the merging step.

Using AI Processing to Sum Values

Select Custom with AI to generate rules for each duplicate group. The generator sees a small sample of your items, so specify the rules for missing numbers, invalid dates, and ties rather than assuming it will discover every case.

Step 8: Select AI Processing Mode

On the duplicates results page, select Custom with AI. Older screenshots below show the previous AI Editing label.

This mode lets you define custom merging logic using natural language prompts.

Step 9: Write Your AI Prompt

This is the core of the process. You'll write a prompt explaining how Datablist should merge the duplicate groups.

Your prompt needs two key parts:

1. Master Record Selection: Tell the AI how to choose the main record (the "master") that will be kept after merging. You can use criteria like "the record with the latest date in /Property", "the most complete record", or "the record created first".
2. Value Summation: Instruct the AI to sum the values from a specific numeric field across all records in the group (including the master) and place the total into that same field on the master record.

Use /PropertyName or {{PropertyName}} to refer to columns in your collection.

Here's the example prompt for summing a /Stock property while choosing the master based on the latest /Date property:

This prompt deliberately keeps the chosen record's other values unchanged. If other rows contain useful notes or identifiers, specify how to preserve those fields before deleting them. For a workflow that keeps every row, see normalizing duplicate values without merging.

The Stock rule distinguishes a blank from invalid text. It also counts the record kept once, avoiding a common mistake where its value is either omitted or added twice.

Step 10: Finalize and Generate Script

Review your prompt. Ensure you've correctly referenced your column names (like /Date and /Stock). Once you're happy, click Generate and preview changes.

Reviewing and Running the AI Script

Datablist's AI doesn't just execute your command blindly. It first generates a JavaScript script based on your prompt and shows you a preview.

Step 11: Wait for Processing

The AI will take a moment to interpret your prompt and write the corresponding script.

Step 12: Review the Script Explanation

Datablist will show you an explanation of what the generated script does. Check that the AI system understands your intent.

Step 13: Check the Script Result Preview

More importantly, Datablist provides a preview of how the script will affect your data before you run it.

Inspect the preview and check at least one group by adding its values yourself. Also check a blank value, a zero, an invalid number, and a tied date. Groups skipped by your rules should remain unchanged.

It will show the proposed state of the master record after merging and summing. Verify that the numeric field (e.g., /Stock) shows the correct sum based on the duplicate group members.

Step 14: Example Preview with Master Item

Here's a closer look at the preview for a specific group. You can see the the master record is the one with the latest date, as expected with our prompt.

Step 15: Run the Script

If the preview looks correct and the sums are calculated as expected, click Run AI rules. Datablist will now apply the merging and summation logic to all identified duplicate groups in your collection.

Step 16: Verify the Results

Once the script finishes running, your duplicate records will be merged according to your instructions.

Go back to your main collection view. The accepted groups should now have one record with the summed value. Groups skipped by your rules should remain unchanged.

Check the column you summed (e.g., /Stock). It should now contain the aggregated totals for the previously duplicated items.

Compare the total stock before and after processing the accepted groups. The total should stay the same when you consolidate distinct contributions. Check skipped groups separately before exporting.

You've successfully used AI Processing to not only merge duplicates but also perform calculations like summation during the process, saving significant manual effort and ensuring data accuracy.

Use Cases

This AI-powered summation feature is incredibly versatile. Here are a few scenarios where it shines:

• Inventory Management: You import product lists from multiple suppliers or warehouses, resulting in duplicate entries for the same product. Use AI Processing to merge these entries, select the record with the latest update date as the master, and sum the /QuantityOnHand values from all duplicates to get an accurate total stock count.
• Sales Data Consolidation: Sales data comes in from different regions or platforms, creating duplicate records for the same customer or product sale within a period. Merge these duplicates based on CustomerID and ProductID, choose the record with the earliest SaleDate, and sum the /UnitsSold and /Revenue fields to get consolidated sales figures.
• Financial Data Aggregation: Add distinct transactions into category totals only after checking their transaction IDs. Two statements can contain the same transaction; remove repeated copies before summing, and keep different currencies separate.
• Project Management: Combining task entries for the same project milestone from different team members' updates. Merge based on ProjectID and MilestoneName, keep the record with the latest StatusUpdateDate, and sum the /HoursLogged field to track total effort.
• E-commerce Order Merging: Combining multiple line items for the same product within a single customer order that might have been entered separately. Deduplicate based on OrderID and ProductID, keep one line item, and sum the /Quantity and /ItemTotal fields.

FAQ

Q1: What if my numeric column isn't recognized as a 'Number' type? During the import process, ensure the column is set to the Number type. If it's already imported as Text, you can use the "Convert Text to..." tool under the "Clean" menu to convert it to Number before running the Duplicates Finder. The AI summing function requires the field to be a numeric type.

Q2: Can I sum multiple numeric columns in one prompt? Yes! You can extend the prompt to include summation instructions for multiple fields. For example:

Use the prompt above and add a rule to sum /SalesCount with the same treatment of blanks and invalid values. Specify which fields remain unchanged on the record kept. Review both totals in the preview.

Q3: What happens if some records have empty values in the numeric column? The generated script follows the rules you specify. The prompt above treats blanks as zero and skips groups containing non-empty, non-numeric stock values. Review the preview to confirm this behavior before running it; do not silently convert an invalid amount to zero.

Q4: Can the AI perform other calculations besides summing? Yes, AI Processing is quite flexible. While summing is a common use case, you could potentially prompt it to perform other calculations like averaging, finding the maximum/minimum value, or even performing calculations based on multiple fields, though prompt complexity will increase.

Q5: Is using AI Processing more expensive than standard merging? The "AI Processing" is included and unlimited in all paid plans.

Q6: What if the AI doesn't understand my prompt correctly? Always check the preview carefully before running the script. If the preview isn't what you expect, refine your prompt. Be more specific, ensure column names are correct, and clearly state the master record selection criteria and the summation instruction. Break down complex logic into simpler steps if needed. You can regenerate the script multiple times until the preview is accurate.