Forum Discussion
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 re...
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:
- Filter by time range Use Excel or Power Query to exclude entries outside the window.
- 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.
- 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.
- 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