Consolidated
New Member
- Joined
- Jan 3, 2017
- Messages
- 2
Hello everyone,
I've been lurking here a while, and have gained lots of insight from other posts, for which I am grateful. It's time I started my own thread though.
Background:
We get annual data files from a client (For those interested, it's an export of a company's nominal ledger on Excel). the data from these files has to be mapped, and re-categorised into our own categorisation.
Problem:
The excel file that the client's accounting system spits out has it's own mapping in column A, where each number corresponds to a particular account. Problems facing me are:
I'll summarise the above in this excerpt:
<tbody>
</tbody>
As you can see above,
Goal:
I'm trying to automate this mapping process, because otherwise this is a massive time-sink. I guess ideally I'd like to create a macro where once we open up the data file from the client, it would take care of creating a proper map, because the output will then be applied to an in-house account map with a simple VLOOKUP.
With that said, what I hope is possible:
A macro that can generate and sort all those accounts and sub-accounts into all unique codes that are consistent every year, so 'salaries' will always be 1000-X, regardless of how many people they hire/fire. Since accounts are being added/removed I figured INDEX would not work.
If anyone could help/give me ideas on how I should go about doing so, I would be very grateful.
I've been lurking here a while, and have gained lots of insight from other posts, for which I am grateful. It's time I started my own thread though.
Background:
We get annual data files from a client (For those interested, it's an export of a company's nominal ledger on Excel). the data from these files has to be mapped, and re-categorised into our own categorisation.
Problem:
The excel file that the client's accounting system spits out has it's own mapping in column A, where each number corresponds to a particular account. Problems facing me are:
- The system has sub-accounts, but these all end up in column A as well.
- Every year, new accounts are added, and old ones deleted.
- Some old accounts are NOT deleted, and we get a sheet 20km long with dormant accounts.
I'll summarise the above in this excerpt:
Account Code | Account Name | Amount |
100 | Mr. Pink | 25 |
200 | Mr. Brown | |
300 | Mr. Orange | 15 |
1000 | Salaries | |
1100 | Guns | 100 |
100 | Mr. Pink | 1 |
12000 | Tips |
<tbody>
</tbody>
As you can see above,
- There is an account (1000) called Salaries, and the exported file places the sub-accounts above the main account, but in the same column.
- The main account code has at least one more digit than a sub-account code, but that's the only constant rule. I.e., Mr. Pink can have a code 1000-100, but later on we may find an account called "Tips" which has a main code of 12000, so Mr. Pink's tips would be coded under 12000-100.
- The main account row has no total, UNLESS there are no sub-accounts attached to that account, like the account (1100) Guns.
Goal:
I'm trying to automate this mapping process, because otherwise this is a massive time-sink. I guess ideally I'd like to create a macro where once we open up the data file from the client, it would take care of creating a proper map, because the output will then be applied to an in-house account map with a simple VLOOKUP.
With that said, what I hope is possible:
A macro that can generate and sort all those accounts and sub-accounts into all unique codes that are consistent every year, so 'salaries' will always be 1000-X, regardless of how many people they hire/fire. Since accounts are being added/removed I figured INDEX would not work.
If anyone could help/give me ideas on how I should go about doing so, I would be very grateful.