Quick Sort Detail

Excel bank reconciliation become easy as “quick sort” helps you to find one by one combinations without running engine. We highly recommend that match all one by one combinations by quick sort then run engine for other combinations.

Select figures that you want to reconcile then run the Armarecon in excel bank reconciliation. Go to Tools tab and then double click the “Quick Sort” button. Program creates seven new yellow columns that assists you to reconcile one by one combinations.

Rec Row:  This column presents row of data which if you are interested to sort back your data to before process.

Absolute movement: This column shows absolute of selected figures. This column presents positive amount of selected figures without considering their sign.

Ref count: This column shows the frequency of each figure, no matter what is their sign.

Movement: This column is exactly as same as selected figures.

Ref Balance: this column shows the balance of positive and negative of each figures. For example, if we have 5 figure 7 and 3 figure -7 this column show 14. (7+7+7+7+7-7-7-7=14)

No of Open: it shows how many figures will not be matched considering positive and negative sign of these figures. For this example (7+7+7+7+7-7-7-7) this column shows 2.

Reviewed: the phrase “to be reviewed” means that there is a possibility that you match this row. If you see, “OK” means that there is no possibility to match this figure with opposite sign.

Now sort “Absolute Movement” column. After that filter “Reviewed” column by “to be reviewed”

Note: In some cases, sorting of date column before sorting of “Absolute Movement” column helps the matching progress.

As you see, Armarecon sorted figures no matter their sign.

For matching figures, you just need to select one of cells of each row together then click “Next Match” button.

Note: Select check box to activate “Next Match” button.

In this excel bank reconciliation example, I noticed figure 21 is debited one time and credited two times. Based on description I decided to match rows no 6 and 7 together and leave row 8 so I selected E6 and E7 then clicked “Next Match” button. Armarecon entered 1 in “ref” column means that these two figures have been matched with each other.

Repeat this action to match other figures.

Note 1: I selected E6 and E7 then clicked “Next Match” button. I also could select any of columns other than the E column, and the result was the same.

Note 2: when I run the program, I selected “ref” column as matching column so this Armarecon consider this column for matching.

Note 3: If we do not consider the sign of figures you can notice frequency of figures are different as below:

21      three times

29      two times

34      four times

58      three times

You can filter “Ref count” column by two then you will have only figures with frequency of two no matter of their sign. It helps you to do your reconciliations faster. You can filter for other frequencies in the next step of matching process.

As you see only figures with frequency of 2, are presented.

Note 4: you can select all desired figures then match them with one matching number. It does not need to match each figure separately. For example in this picture I matched all figures 29, 77, 81 together with one match figure.

Note 5: button “Same Date” highlights rows with the same date.

Note 6: “Quick Sort” captures only those figures taht are visible, neither zero nor error. It means that you should filter un-matched figures (Empty cells in matching column here is “ref”) then apply “Quick Sort” for un-matched figures.

Note 7: You can sorted back the data to first order by soring “Rec Row” column.