cacatenation script

[matthew kramer]

I'd appreciate it if someone could help me out on this.
I have a large dataset with about 50,000 lines. There are seven fields
across. I'd like to be able to do the following:

The computer cacatenates all the fields in the dataset into one
continuous line across for each of the rows in the dataset.

Then, for each row for which the name in the first column is the same,
it cacatenates that row after the previous row with the same field name
in the first column, and does this for all the rows.

For example:

country year note1 etc...
France 1990 abcd
England 1991 abcd
France 1994 1234
France 1993 aaaa
England 1992 1234
USA 2000 bbbb

becomes cacatenated as:

France 1990 abcd 1994 1234 1993 aaaa etc.
England 1991 abcd 1992 1234 etc.
USA 2000 bbbb

Thanks in advance.

Matthew Kramer

 

[Guest]

I assume you want the data in seperate columns? Your question is not quite
clear. To do that...

On the Menu bar select Data -> Text to Columns. It is hard to tell from your
post but as a guess the text is tab delimited, so select Delimited. Excel
will probabaly guess tab for you and then you can just select Ok. This will
make the columns for you...

HTH

 

[Tony]

With 50k records this might get tedious, but here's an
approach that does not require coding.

Sort the dataset on col 1. For each block of repeated
names use "&" to tack the cell contents together. Put the
formulas in, say col 8. You would have two types of
formula:

Start of block: col 1 & col2 & ... & col 7
Subsequent rows: (previous row)col 8 & col2 & ... & col 7

If this is a once-off, this might be the easiest approach
with the judicious use of fill-down. If you expect to do
this more than once on different datasets, you might try
coding it. Either way, using Excel's sort and & will
likely help.

Tony