Forum Discussion

sybtan05's avatar
sybtan05
Copper Contributor
Oct 14, 2025

Data Reconciliation Assistance Needed – Time Range & Sum Matching

I have two sets of data that need to be reconciled. Specifically, I’m trying to identify which combinations of numbers from these datasets can sum up to a specific target value.

Additionally, the reconciliation should only consider entries that fall within a defined time range—from approximately 8:00 AM on one day to 10:00 PM the following day.

I’ve tried using Solver and Goal Seek, but the results don’t seem accurate. I also attempted using Microsoft Copilot, but the outcome still appears incorrect.

Could you assist with identifying or generating the correct combinations based on the criteria above?

copilot in excel
excel

2 Replies

  • Olufemi7's avatar
    Olufemi7
    Brass Contributor
    Dec 23, 2025

    Hello sybtan05​, 

    To reconcile two datasets and identify combinations of numbers that sum to a target within a specific time window (8:00 AM Day 1 → 10:00 PM Day 2), you’ll need to:

    1. Filter by time range Use Excel or Power Query to exclude entries outside the window.
    2. Model the subset‑sum problem
      • In Excel, set up binary decision variables for each entry (1 = included, 0 = excluded).
      • Use Solver to constrain the sum of selected values to equal your target.
      • Ensure Solver is set to “binary” for those variables.
    3. Alternative approach If you need all possible combinations, a script (e.g., Python with itertools.combinations) is more reliable than Solver/Goal Seek, which only find one solution.
    4. Using Microsoft Copilot Copilot for Excel/Finance can assist with reconciliation by helping define rules, filtering by timestamps, and generating reconciliation reports. However, for exhaustive subset‑sum searches, Solver or code is required alongside Copilot’s guidance.


      Reference: 

      https://support.microsoft.com/en-us/office/define-and-solve-a-problem-by-using-solver-5d1a388f-079d-43ac-a7eb-f63e45925040?utm_source=copilot.com


  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Nov 16, 2025

    Python may help:

     

    import itertools
import pandas as pd

# Load datasets
df1 = pd.read_excel("dataset1.xlsx")
df2 = pd.read_excel("dataset2.xlsx")

# Filter by time range
start = pd.Timestamp("2025-11-16 08:00")
end   = pd.Timestamp("2025-11-17 22:00")

df1 = df1[(df1['timestamp'] >= start) & (df1['timestamp'] <= end)]
df2 = df2[(df2['timestamp'] >= start) & (df2['timestamp'] <= end)]

# Combine values
values = list(df1['amount']) + list(df2['amount'])

target = 1000  # example target sum

# Find combinations
for r in range(2, len(values)+1):
    for combo in itertools.combinations(values, r):
        if sum(combo) == target:
            print(combo)

     

Resources