Here is a tricky problem often faced by accountants and finance professionals: Let’s say you have 5 customers. Each of them need to pay you some money. Instead of paying the total amount in one go, they paid you in 30 small transactions. The total amount of these transactions matches how much they need to pay you. But you don’t know which customer paid which amounts. How would you reconcile the books?
If you match the transactions manually, it can take an eternity – after all there are more than 931 zillion combinations (5^30).
This is where solver can be handy. Solver can find optimal solution for problems like this before you finish your first cup of coffee.
Reconcile debits & credits using solver model – Tutorial
Step 1. Set up your solver model
In a blank sheet, list credits along a column and debits on the top in few columns, as shown below:
In the blank grid, Solver will fill 0 or 1 indicating whether credit in that row is matched with debit in that column or not.
This area is (C6:G35 in my workbook) is known as variable cell range in Solver model.
There are 2 rules to be followed when matching debits to credits:
- A credit can be matched with only one debit – ie sum of any row in C6:G35 range can be 1, at most.
- Total reconciled amount should be less than or equal to total credits – ie sum of any column in C6:G35 should be less than values in C5:G5 (debits).
To facilitate these rules, also known as constraints in solver parlance, let’s use column H & row 36.
- Write =SUM(C6:G6) in H6 and fill down the formula.
- Write =SUMPRODUCT($B$6:$B$35,C$6:C$35) in C36 and drag sideways to fill the formula in rest of the columns.
Our solver model should look like this:
Step 2: Set up optimization cell
To do its work, solver needs an optimization cell. Our goal is to maximize the amount of reconciled amount. So, in a blank cell write =SUM(C6:G36). This will be our optimization cell.
Step 3: Launch solver
Select the optimization cell (in my workbook, this is J6) and go to Data > Solver. (If you do not have solver, enable it using these instructions.)
Set up solver model as:
- Objective is to to maximize J6.
- Variable cells are C6:G35
- Constraints
- C6:G35 should be binary (o or 1)
- C36:G36 should be <= C5:G5
- H6:H35 should be <= 1
- Solver method is Simplex LP (our problem is linear)
When you are ready, Click Solve. Solver should take few minutes to find the solution.
Step 4: Examine the result
Once solver finds an answer, it will show Solver Results dialog. Click ok (you may also look at the sensitivity report). This loads the solver solution in to variable cell range.
Analyze the numbers and enjoy.
What if Solver solution is not optimum?
Occasionally, Solver fails to find optimum solution for linear problems with integer constraints. In such cases, try again by adjusting constraints & precision.
Download example workbook
Please click here to download the example workbook. Play with the solver model to learn more.
Other ways to reconcile data
If you deal with reconciliation problems, check out below examples to learn more:
- Match transactions using formulas
- Matching transactions using VBA macros
- Compare 2 lists using Excel conditional formatting
- Introduction to VLOOKUP formula – the easiest way to reconcile data
- Use SUMPRODUCT to consolidate revenues
How do you reconcile data?
Solver is a powerful way to reconcile data. It does take some time to set up the model and configure solver, but once your model is ready, Solver does all the heavy lifting.
What about you? What methods do you use to reconcile data? Please share your thoughts and tips in the comment section.
21 Responses to “Distinct count in Excel pivot tables”
The distinct count option works well but I have found that if I have a date field and want to group by year, month, etc. that option seems to be disabled. I need to do both, distinct count and group by year/month.
Example data; sales orders with item quantities with dates.
Challenge; sum the item quantities, count the distinct orders and group by month. How do I do this?
Perhaps that's not possible due to the grouping?
@Al... When you use data model based pivots, you cannot group values manually anymore. Why not use Excel 2016's default date grouping option? In this case we have just a few dates, so Excel is not grouping them, but if you have an year's worth of data, when you make the pivot with date in the row label area, Excel automatically groups them. If you have fewer dates or want to use your own grouping, just create a table with all dates, add columns with month, week, year etc. Then connect this table (these types of tables are usually called as calendar tables) to your data on date field as a relationship. Now you can create reports by month, quarter etc easily.
Is this the only way to do it in 2013? I find it rather cumbersome to have to create another data table listing dates with the another column for MONTH() and YEAR() to be able to summarise data for senior level...
I know people find adding calendar tables cumbersome, but it is a best practice and let's you add more layers of analysis quite easily. For example, adding analysis by weekday vs. weekend or by financial quarter or YTD calculations (you would need either Power Pivot DAX or some very carefully setup pivot table value field settings)
I had absolutely no idea this was possible. Very useful, nice work!
Doesn't work for 2010 version though (or at least not my works version)
Hi ,
The post has the following in it :
These instructions work only in Excel 2016, Office 365 and Excel 2013.
when i have 2 different Pivot tables, one without the enabled “Add this data to data model” option, and the other one with it enabled.. is there anyway i can link slicers between them?
if the answer is NO,, what to do ?
Quick note, the “Add this data to data model” option is not available for the Mac version.
perhaps outside scope of this article but I have found when I attempt to create a pivot table from an external data source (connection to a sql view) the "Add this data to data model" becomes greyed out. Anybody experienced and found a solution so I can start getting distinct count in my pivot tables?
Is there a way to still add a calculated field when using distinct count?
I found I can't change the date source after tick the " add this data to the data model", can you help to adv how to change the date source in such case?
Is there a way to update the source once you have added to the data model? I receive a new spreadsheet weekly and would like to update the connection so my tables pull from the new source.
Hi Crhis, I like how you have hulk (superhero) as your avatar. Do you know that there is a superhero in Excel too? It's Power Query. You can use it to solve your problem in a simple click. Here an intro if you need some guidance.
Powerful Introduction to Power Query
A big Thank you. It worked.
Hi, have survey data that I need to analyze but the challenge is that my key fields are showing horizontally. I tried to transpose the fields using Power Query, but unfortunately the new fields are returning same values on a pivot table despite using distinct values
How I can a do a pivot table with discount conts in some columns and then generate shor report filter pages. pls it drives crazy
Hi. Why grand total pivot of distinct count is 13? shouldn't it be 67?
Great Answer! Saved me lots of time!
Thank you!!!
Worked awesome! Thanks!!
Hi Chandoo,
I am using pivot tables for distinct count and now I need to update them with new set of data. But when I update the source data, all the columns and formatting of Pivot table disappears and I need to build it from Scratch.
Is there a possibility that I can update the source data with new rows added and also retain my pivot tables?