Allocate unique account number to particular account

[Tyson2014]

Hi guys,
Hope everyone has a great day.

I have an issue for importing some raw data into accounting software. I highly appreciate if you can provide some suggestions.

As you can see below, I have a list of account number and account description. The reason why they have the same account number is there has been no account number before, only account description and $ balance (is not shown in the table below) are provided. I used some formulas to allocate number "1000" for Cash at Bank and "2000" for Investment. However, I'm stuck to have a unique number for a specific account. I’m thinking there might be another formula which can turn duplicate account number (the second and third rows) into the following figures (second table)

Account number	Account description
1000	Cash at bank - UBS
1000	Cast at bank - ANZ
1000	Cash at bank - HSBC
2000	Investment - UK
2000	Investment - America
2000	Investment - Australia

<tbody>
</tbody>

Kindly refer to the second table for desired outcome.

Desired outcomes:
Account number	Account description
1000	Cash at bank - UBS
1001	Cast at bank - ANZ
1002	Cash at bank - HSBC
2001	Investment - UK
2002	Investment - America
2003	Investment - Australia

<tbody>
</tbody>

Again, thank you for having a look at this post and give it a try. Highly appreciate your support. Cheers.

 

[hohlick]

supposing your current accounts are in column A starting from 2nd row, and sorted, place this formula in column c:
=IF(A2=A1,C1+1;A2)
and fill down

if not sorted: from C2 fill down: =COUNTIF($A$2:A2,A2)+A2

 

[Fazza]

I suspect there is a bigger picture that is not clear.

Likely a new master (Lookup) list should be made. So that, per the sample data, 1000 is always 'Cash at bank - UBS', and 1001 always 'Cash at bank - ANZ'

Then every time there is data, the same unique code will be used for each distinct account description.

(The alternative would be to have a simple formula each day/time to give the daily description but it would vary day to day. It is easy for me to imagine this would be a bad approach, but not knowing exactly what is being done I can't be sure.)

Anyway, I envisage making a master table and then looking up that table every time the account identifier is required. So a lookup formula so that the same ID goes with each description every time, not potentially a different ID each occasion the ID is populated.

HTH. regards

 

[Tyson2014]

Hi Fazza,

Thank you for pointing out an issue for me to consider.

Normally, most of the accounting software will export the trial balance which has account number, account description and the amount. In that situation, it will be extremely easy to import that trial balance into other accounting software.

However, some times you cannot export full list above. For this example above, I only have account description and the amount. In order to import to other accounting software, I need to create “dummy” account number for each account and then import them into other accounting software.

The biggest advantage is that it saves me a lot of time (insert extra column and fill series). However, next year I will do the same process and I cannot utilise last year account as they will have different account no. For example: Cash at bank- UBS: acc no. 1000 but it might be acc no. 1100 in the following year.

Therefore, I have to create a master trial balance as below:

Account description	Account number
Cash at bank	1000
Investment	2000
Liabilities	3000

<tbody>
</tbody>

As you can see, any account description has “Cash at bank” will be allocated account number 1000. Therefore, it will leads to the situation where two accounts have the same nature “Cash at bank” but are different bank accounts:

Account description	Account number
Cash at bank - UBS	1000
Cash at bank - ANZ	1000

<tbody>
</tbody>

As they are unique, therefore I have to perform the formula as Hohlick advised above. It will leads to the following

Account description	Account number
Cash at bank - UBS	1000
Cash at bank - ANZ	1001

<tbody>
</tbody>

Again, get back to the main purpose of these exercises. I need to import them into the accounting software by allocate them a unique account number but still have some “rule” so that I can manipulate them.

As you can see in table 1, the Cash at bank will start with number 1. The investment will start with number 2. Therefore, as long as I can categorise them into reasonable classification, I can ensure that I can manipulate all the account which start with number 1 or Cash at bank account section.

To sum up, I really appreciate your advice to have a master table to allocate acc no. to each acc. That’s exactly what I did. That’s why you can see all the Cash at bank account (ANZ or UBS) both start with number 1 in their acc. no. To make the process less painful ( there are about 10000 acc names – I have to do for more than 100 clients), I try to avoid going to specific. Therefore, I create dummy number such as 1,2,3 in the end of each acc to distinct them with others.

If you have other thoughts, please share with me. I really appreciate them.

 

[Tyson2014]

Thanks Hohlick. It works well