Share via

Have Recon with only 2 columns but might have several rows on other to match, how can I find Matches?

J806 0 Reputation points
2023-01-14T18:58:32.0333333+00:00

Have a general Ledger account debit / credit but sometimes/most it several credits to match one debit, i have been doing by adding subtracting and takes a long long time. Plus they don't really have a unique name that would match the debit to credits, included example. THank you!!!!

User's image

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,682 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,689 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emily Hua-MSFT 27,591 Reputation points
    2023-01-16T09:12:07.7266667+00:00

    Hi @J806

    You can try Solver function to get the one combinaton equals to the given sum.

    According to your example, I make a sample to show more details as following.

    • The cells of column F will show "1" if the "Credit" amount are included in the combinaton that equals to one given sum. In cell F21, I enter formula =SUMPRODUCT(F2:F20,B2:B20).
      Capture19
    • Go to File > Options > Add-ins > Excel Add-ins, Go > Tick the box of "Solver Add-in" > Click OK. Locate to Data tab > Analyze group > Solver.
    • Set "$F$21" to be the "Set Objective". Choose "Value" option, and enter "500" (the Debit Amount value). Select the range "$F$2:$F$20" to be "By Changing Variable Cells". Add one constraints as the image below. Capture21 And then, please click "Solve". Capture22 You will find the results. Capture23

    But if there are multiple combinatons equals to a given Debit Amount, and you want to list them all. Then you need the VBA code or 3rd-party add-ins.

    Here is an article "Find a set of amounts that match a target value" (Please Note: Since the web site is not hosted by Microsoft, the link may changed without notice. Microsoft does not guarantee the accuracy of this information), it give us the VBA code. Hope it could be helfpul to you.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.