Quote Originally Posted by DarinM View Post
Hi Sam,

I know - it's insane. Partially the problem because our CRM was created in Germany for their database, which only has one account # and one pricing. The USA has the same database, and way less discounts. I am in Canada where we have 'free' healthcare or heavily discounted, hense all the -000's. However, some clinics have different accounts for different purchases, which I think is insane, because we are doing their accounting for them...but whatever haha. This is why I am trying to manipulate the exported data into something a bit more useful for my department.

I believe everything on 1 line would be much better because eventually when I do v-lookups to edit this database, i won't be able to do that as well with different tabs. Also, I'll be using this list from a marketing perspective where I need their e-mail addresses and different mailing addresses.

I will see if that would be useful but I am slightly skeptical .

Mperrah,

that is what I envisioned, except yours looks a bit redundant? Instead of account 1/2/3/4, and listing the same account # in the pricing columns, why wouldn't we just put the 'main' (whatever we determine is the main) in account 1, then all the rest account #s into the pricing columns? again this is really only for the accounts that have the same mailing address.

There may only be ~50-75 accounts that have different account #s, with different pricing with same mail address, but there are 1000's of same account #s but with different pricing. Almost every account will have 3 pricing accounts, regular, discounted and government.

012345-000 , -001, -004 etc.

Does that make anymore sense?


edit - also to note, the database where I am exporting this from - gets updated if not daily, then weekly. so whatever we do, having a way to export and then re-organize it into this fashion is the most important part, but we can look at that later, figuring out what the layout will be and seeing if we can copy values into certain columns then deleted the row we just copied from is a starting point...!

Darin
I see I left some confusion there.

Those suggested tables are not what you separate your incoming data into, rather they are permanent tables that only get updated when your client list changes.

Your incoming data would be processed against those lookup databases. The only pre-processing needed on the incoming data would be to split the account numbers on the "-" into 2 new columns (3 columns: Full Acctt#, 6 digit # and 3 digit suffix.

For Marketing purposes have an Email database.

According to the KISS Principle:
The accounting calculations only need to know the Full acct # and the Pricing Classification.
Only above that, do they need to know the Billing and Ship To addresses
Marketing needs to know the Email addresses.

I m sure that I left out a lot, but break your needs down that way.

I looked at your RealSample book, but I didn't see any Accounting figures in there, so I am still in the dark, or at least, Twilight about what you do and need. We here at VBAX have a combined total of hundreds of years experience in many different fields of business. Don't be afraid to ask for the best way to use Office to handle a particular business task. Someone will know.