# Allocate unique account number to particular account

#### Tyson2014

##### New Member
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.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### hohlick

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### New Member
Thanks Hohlick. It works well

Replies
1
Views
712
Replies
0
Views
481
Replies
2
Views
504
Replies
6
Views
929
Replies
2
Views
550

1,195,596
Messages
6,010,637
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back