Navigation: Statistic Sampling > Formatting imported record >

Create account code, description, balance column

 

 

 

Content

 

1.Create account code column

2.Create account description column

3.Create balance column

 

 

Create account code column

 

 

If you observe the pattern of the general ledger, each account code will be followed by a list of transactions.

 

We will construct an excel formula based on that pattern, and generate account code on every line of transaction.

 

Steps:

 

1.We type the formula at the K4 cell.

=IF(A4="Account Code:",B4,K3) 

Explanation:

If cell A4 contain "Account Code:",
then we will copy content from column B4,
otherwise we will copy content from cell above which is K3
 

2.The K4 cell will take the content from B4, because it fulfill the condition.

Now copy the formula at K4 downward.



 

3.When the formula is dragged downwards, different account code is assigned when we detect new account code.



 

4.Now you have successfully created the entire account code column.

 

 

Create account description column
 

 

 

 

We will use similar formula as generating account code, to generate account description on every line of transaction.

 

Steps:

 

1.We type the formula at the L4 cell.

=IF(A4="Account Code:",C4,L3) 

Explanation:

If cell L4 contain "Account Code:",
then we will copy content from column C4,
otherwise we will copy content from cell above which is L3
 

2.Copy the formula downward.
 

3.When the formula is dragged downwards, different account description is assigned when we detect new account code.
 

4.Now you have successfully created the entire account description column.



 

 

 

Create balance column

 

Steps:

 

1.We type the formula at the M4 cell.

=IF(M4="BALANCE B/F",J4,G4-H4) 

Explanation:

If cell M4 contain "BALANCE B/F",
then we will copy content from column J4,
otherwise we will use figure of G4 minus H4 (so that debit will be positive and credit negative)
 

2.Copy the formula downward.
 

3.Now you have successfully created the balance column.


 

 

 

 

 

 

Copyright © 2018 Audit Online Solution Sdn Bhd