Concatenate Problem- Please help!!!!

GMichel · Mar 12th, 2002, 07:57 AM

Here is my dilemna:

I have 4 columns of data. Each column represents a segment of a general ledger account number.
The first column has about 54 rows, which represents the Fund. (5 characters)

The second column is the Account, and there are 71 of those. (6 characters)

The third column is the Program and there are 39. (3 characters)

Lastly there is the Project column and there are 9 of them. (2 characters)

So in essence I would like a formula that will generate all the accounts for me in this format:

XXXXX.XXXXXX.XXX.XX
54x71x39x9= 348,894 general ledger accounts

In addition, in the column following each of the columns is a short description. I would like to do the same thing above with the descriptions in the following format:

Fund descr-Account name-Program-Project

I have been trying to do this using the Concatenate function but to no avail. Can someone please help me?

Thanks
Gil

GMichel · Mar 12th, 2002, 08:32 AM

You may also feel free to email me any solution to this.

Barrie Davidson · Mar 12th, 2002, 08:53 AM

Quote:

XXXXX.XXXXXX.XXX.XX
54x71x39x9= 348,894 general ledger accounts

You're not going to be able to get this in Excel, you're limited to 65,536 values.

GMichel · Mar 12th, 2002, 08:58 AM

OK, granted that was the case, I would break this up into mini-projects,...thats if I had a way to actually merge each field into one GL account....

any ideas?

g_erhard · Mar 12th, 2002, 10:01 AM

Hrmmm...

Excel might be a bit confining... have you considered Access? It doesn't have the data limit, and is also inherently 'shareable' (multiuser). The the data formats that you are proposing wouldn't be hard to implement as an Access form or report, but you would have to define each "thing" in Access beforehand (what makes up a Fund, an Account, a Program, and a Project, and how they all interrelate.)

Access can also dump data to Excel if you need it to produce charts and do other data analysis tricks Access can't handle.

unf. I can't go into the nuts and bolts here, you would have to seek out an Access help board for detailed answers.

GMichel · Mar 12th, 2002, 10:06 AM

although you cant go into the nuts and bolts, could you point me a little?

How in Access would I set this up?

Ultimately I would need the end product to be a csv text file, which I know is no problem for Access.

Thanks for your reply

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 12th, 2002, 07:57 AM	#1
GMichel New Member Join Date: Mar 2002 Posts: 5	Here is my dilemna: I have 4 columns of data. Each column represents a segment of a general ledger account number. The first column has about 54 rows, which represents the Fund. (5 characters) The second column is the Account, and there are 71 of those. (6 characters) The third column is the Program and there are 39. (3 characters) Lastly there is the Project column and there are 9 of them. (2 characters) So in essence I would like a formula that will generate all the accounts for me in this format: XXXXX.XXXXXX.XXX.XX 54x71x39x9= 348,894 general ledger accounts In addition, in the column following each of the columns is a short description. I would like to do the same thing above with the descriptions in the following format: Fund descr-Account name-Program-Project I have been trying to do this using the Concatenate function but to no avail. Can someone please help me? Thanks Gil

Mar 12th, 2002, 08:32 AM	#2
GMichel New Member Join Date: Mar 2002 Posts: 5	You may also feel free to email me any solution to this.

Mar 12th, 2002, 08:58 AM	#4
GMichel New Member Join Date: Mar 2002 Posts: 5	OK, granted that was the case, I would break this up into mini-projects,...thats if I had a way to actually merge each field into one GL account.... any ideas?

Mar 12th, 2002, 10:01 AM	#5
g_erhard New Member Join Date: Mar 2002 Location: San Ramon CA Posts: 23	Hrmmm... Excel might be a bit confining... have you considered Access? It doesn't have the data limit, and is also inherently 'shareable' (multiuser). The the data formats that you are proposing wouldn't be hard to implement as an Access form or report, but you would have to define each "thing" in Access beforehand (what makes up a Fund, an Account, a Program, and a Project, and how they all interrelate.) Access can also dump data to Excel if you need it to produce charts and do other data analysis tricks Access can't handle. unf. I can't go into the nuts and bolts here, you would have to seek out an Access help board for detailed answers.

Mar 12th, 2002, 10:06 AM	#6
GMichel New Member Join Date: Mar 2002 Posts: 5	although you cant go into the nuts and bolts, could you point me a little? How in Access would I set this up? Ultimately I would need the end product to be a csv text file, which I know is no problem for Access. Thanks for your reply