Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cacatenation script
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 *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cacatenation script
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 "matthew kramer" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cacatenation script
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 -----Original Message----- ..... 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 *** Sent via Developersdex http://www.developersdex.com *** . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cacatenation and SUMPRODUCT | Excel Discussion (Misc queries) | |||
cacatenation code | Excel Programming | |||
Excel 2000/XP script to Excel97 script | Excel Programming | |||
VB Script Help | Excel Programming | |||
VB Script help | Excel Programming |