Consolidate multiple columns into one

[COforlife]

I would like to consolidate all the values in multiple columns into one long column. Anyone know how to do this? Thanks.

[RuralGuy]

[Field1] & [Field2] & [Field3]...
...but why would you want to do this?

[COforlife]

First off, I don't want them combining the values which what you suggested does. I want it to list the all the values in column 1 and then under that the values for column 2. It more has to do with the retarded nature of the software I'm pulling data from, I've contacted them and there is nothing I can do there.

[dcrake]

If you read the suggestion correctly you would have noticed that the different fields wer concatenated using the & ampersand as opposed to the + plus sign. The plus sign would perform mathmatically on adjoining numeric fields, whereas the & string the values together.

As RG stated why do you want to do this and how is the user going to differentiate when one field ends and another starts unless you use some soft of delimiter.

David

[oddsteven]

If what you want to do is combine three fields into one, then [Field1] & " " & [Field2] & " " & [Field3] will give you all three fields pulled into one with a space between each entry - you could then copy that entire column, start a new table, and past the column into it. That sounds sloppy but if it's a one off thing that's quick and easy.

[oddsteven]

Alternatively you could copy all three columns into an MSWord file, and combine them there, using MSWord tables. Also relatively sloppy but it works.

[COforlife]

The problem lies in the fact I have 40 columns, each about 300 rows and I have to do this often. When I use the '&' it puts all 40 columns into one cell, I need one column with (40x300 or 12000) rows. Each cell is actually text, not numbers, so its not the '+' thing. I'm still looking for an easier solution than copy/paste.

[RuralGuy]

So you need all of the fields (columns) to be appended to the table as additional records (rows), right? Can you explaix *why* you need this so we can better understand what you are up against?

[COforlife]

Yes, basically in these fields are Scholarship names, but in them is also the Student IDs. I need these in one long list in one column so I can break it out as well as add info in columns next to them. Below is an example:

Column 1 Column 2 Column 3...etc.
12aa 12bb 05cc
15aa 16bb 25cc
19aa 21bb 26cc

I need:
Column 1
12aa
15aa
19aa
12bb
16bb
21bb
05cc
25cc
26cc

[RuralGuy]

So you need the Field2 entries appended to the Field1 entries and then append the Field3, etc?

[COforlife]

Yes, that is correct.

[RuralGuy]

Any chance you could post a sample db so we would have something to work with while creating the function you need?

[COforlife]

Attached is a sample database to work with.

[RuralGuy]

The attached should give you something to work with.

[COforlife]

RuralGuy,

Thanks for the database. I unfortunately though do not know VB well enough to know how to run the module. Your help has been appreciated, thanks so much.

Chad