Select File from Excel based on Condition

Question

This scenario involves selecting Excel files in a folder based on their cumulative sum, ensuring the sum reaches a given Amount without exceeding it. Alteryx should dynamically pick files until the difference between the sum of selected files and the target Amount reaches zero.

Scenario Breakdown

* Input: A folder with multiple Excel files, each containing an Amount value.
* Target Condition: Select files sequentially until the sum matches the given Amount exactly.
* Stopping Condition: Stop selecting files once the cumulative sum equals the Amount.

Example 1 Given Amount: 100

File Name Amount File150File220File330File423

Step-by-Step Selection Process

* Select File1 → Cumulative Sum = 50 → Difference = 100 - 50 = 50
* Select File2 → Cumulative Sum = 50 + 20 = 70 → Difference = 100 - 70 = 30
* Select File3 → Cumulative Sum = 70 + 30 = 100 → Difference = 100 - 100 = 0 ✅ STOP
* File4 is not needed as the target is met.

Final Selected Files: File1, File2, File3

BRRLL99 · Answer

Yes I need to factor in that logic as well.

Initially thought of doing the same using Running total

Lets say 3 files total exceeds the Amount , then workflow should stop at 3 files itself

Bren_Spill2 · Answer

@BRRLL99 - If the files all have the same schema, you could use a wildcard input to bring them all in at once, making sure to include file name in the input. The run a multi-row formula to perform the calculation above and filter out rows where cumulative amount exceeds required amount.

One other consideration: what happens if the sum value of the files exceeds the desired amount? for example if file 3 above was 40 instead of 30. You'd need to factor that into your logic

If there are too many files to practically read them all in, an iterative macro might be a good approach