Creating an auto clean-up macro for a client's data file

[Consolidated]

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:

• 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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Account Code[/TD]
[TD]Account Name[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Mr. Pink[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Mr. Brown[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Mr. Orange[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]Salaries[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1100[/TD]
[TD]Guns[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Mr. Pink[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12000[/TD]
[TD]Tips[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As you can see above,

1. There is an account (1000) called Salaries, and the exported file places the sub-accounts above the main account, but in the same column.
2. 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.
3. 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.

 

[NotC90]

Re: Bit of an Excel puzzle - Creating an auto clean-up macro for a client's data file

Hello,

unfortunately i don't know vba, so i can't give any tips how to go about this with a macro.

I did however come up with a formula that might do the trick: (worked for your sample-dataset at least)

=IF(LEN(A2)<=3,INDEX([B]$A2:$A$8[/B],MATCH(TRUE,LEN([B]$A2:$A$8[/B])>3,0))&"-"&A2,A2&"-Total")

If you paste this formula in a new column and adapt the ranges i marked above it should give you unique codes. (it's an array formula - so enter it via ctrl-shift-enter and then drag it down)

I'm sure there are more efficient ways to do this but here's how it works: for cells that are less than 4 digits it will look for the next 4+digit-number as the "parent".

Hope this is headed in the direction you are looking for.

 

[Consolidated]

Re: Bit of an Excel puzzle - Creating an auto clean-up macro for a client's data file

Hey NotC90, thanks for the response!

Yes this is close to what I was looking for. Just one more thing I was hoping you could help me out with before I try getting to my final destination; I was hoping to combine the sub-totalling of the amounts as part of the formula, so I'm guessing something of a SUBTOTALIF formula that calculates a subtotal for the parent account and all its subs in column C.

 

[NotC90]

Re: Bit of an Excel puzzle - Creating an auto clean-up macro for a client's data file

Hello,

this should work if you put it in column C (with column B being the new unique-code-formula)

=IF(AND(RIGHT(B2,5)="Total",E2="");SUMIFS([B]$E$2:$E$8[/B],[B]$B$2:$B$8[/B],A2&"-*"),E2)

It just takes the values from the existing Amount column and for Totals without an Amount-value from the source it calculates it.

Hope that helps.

BR