Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Location
    Delaware, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro needed to convert one large col into many (2

    Edited by HansV to present data in tables. See <!post=Copying a Word or Excel table to a post,164109>Copying a Word or Excel table to a post<!/post>

    I have large Excel table, many rows data in one column, representing data fields, repeated for each data record. e.g.: like so:
    DATA
    john
    town1
    22345
    stan
    town2
    45690
    edith
    town3
    56129

    Many such rows. The same pattern repeats. I do not have the field name and record numbers in the form;

    <table border=1><td>field</td><td>record no</td><td>DATA</td><td>a</td><td align=right>1</td><td>john</td><td>b</td><td align=right>1</td><td>town1</td><td>c</td><td align=right>1</td><td align=right>22345</td><td>a</td><td align=right>2</td><td>stan</td><td>b</td><td align=right>2</td><td>town2</td><td>c</td><td align=right>2</td><td align=right>45690</td><td>a</td><td align=right>3</td><td>edith</td><td>b</td><td align=right>3</td><td>town3</td><td>c</td><td align=right>3</td><td align=right>56129</td></table>
    I need a macro to convert such data into columns each representing data fields.
    The result desired:

    <table border=1><td>record no/field></td><td>a</td><td>b</td><td>c</td><td align=right>1</td><td>john</td><td>town1</td><td align=right>22345</td><td align=right>2</td><td>stan</td><td>town2</td><td align=right>45690</td><td align=right>3</td><td>edith</td><td>town3</td><td align=right>56129</td></table>
    Pl. note that there may be hundreds of records and many data fields.
    Need a macro where record number is 1 to i, and data field, 1 to j.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro needed to convert one large col into many (2

    Welcome to Woody's Lounge.

    I have attached a text file with a macro. Since your data does not contain an indication of how long one record is, you must provide that information in the macro, by modifying the constant lngNumberOfFields.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •