How to Sum Numeric Values When Merging Duplicates

Duplicate data is a pain. You export from your CRM, or get product lists from different suppliers, and bam — same items listed multiple times. You want to clean the mess, but deleting duplicates isn’t enough. What if some rows have stock numbers or sales amounts? You don’t want to lose those. You want to merge and sum them.

That’s where Datablist Duplicates Finder comes in. It can merge duplicates and add up your numbers. Like magic.

Imagine merging several product entries and automatically getting the total stock count – all with a simple instruction!

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

Navigate to the "Clean" menu in the header and select "Duplicates Finder".

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 example, let's assume we use a Product Name or SKU to find duplicate product entries.

Select the properties to identify duplicates

Step 6: Choose Matching Algorithm and Processor

Next, select the comparison algorithm.

For identifiers like SKUs or Emails, 'Exact' or 'Smart' usually works well. The 'Smart' algorithm can handle minor variations like different protocols in URLs or word order.

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

Here's where the magic happens. Instead of using the standard "Auto Merge" options, we'll use the "AI Processing" mode to give specific instructions, including summing our numeric field.

Step 8: Select AI Processing Mode

On the duplicates results page, click on the "AI Editing" button.

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:

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".
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:

Select the record with the latest date in /Date as master record.
And sum all /Stock values from all records into the /Stock property of the master record.

You can also add variation to deal with the other property. For example:

Select the record with the latest date in /Date as master record.
And sum all /Stock values from all records into the /Stock property of the master record.
Merge other properties using the most frequent value.

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".

Final prompt ready for script generation

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 Explaination

Datablist will show you an explaination 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.

Look at the "Preview" listing.

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.

Example preview showing the summed value in the master item

Step 15: Run the Script

If the preview looks correct and the sums are calculated as expected, click "Run Script". 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 duplicate rows should now be gone, replaced by the merged master records.

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

Your data is now cleaned, deduplicated, and accurately reflects the summed numeric values! 🚀

View the final cleaned data with summed values

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: Merging transaction records from different accounts or statements that represent the same underlying expense or income type. Deduplicate based on TransactionDescription and Date, select the record with the most detailed description, and sum the /Amount field for accurate category totals.
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:

Select the record with the latest date in /Date as master record.
Sum all /Stock values from all records into the /Stock property of the master record.
Sum all /SalesCount values from all records into the /SalesCount property of the master record.
Merge other properties using the most frequent value.

Q3: What happens if some records have empty values in the numeric column? The AI summation process typically treats empty or non-numeric values as zero during the calculation. It will sum all valid numbers found across the duplicate records in the specified column.

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.