Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Location
    TX
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reformatting Text List into Columns (Excel 97)

    I am trying to convert or reformat a list of text information into column-row format. I see that it can be done using Transform if the format is identical for each group of data, but then you have to select each group of data. I have 1,000 names in this list. It looks like the following:

    ACUPUNCTURE
    ALABAMA ORIENTAL MEDICAL ARTS
    NAME 1
    603 - 37th Street South
    Birmingham, AL 35222
    205-324-6003



    PAIN MANAGEMENT
    ALABAMA ORIENTAL MEDICAL ARTS
    NAME 2
    603 - 37th Street South
    Birmingham, AL 35222
    205-324-6003



    CHRONIC FATIGUE - FIBROMYALGIA
    NAME 3
    GENERAL PRACTICE HOLISTIC
    1904 Delaware Ave.
    Suite 300
    Jasper, AL 35501
    205-2215510
    chronic@generalhealth.com

    What tools are available to select cells and copy the data to another worksheet so they will be in column-row format?

    Thanks, Bruce

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Reformatting Text List into Columns (Excel 97)

    If there is some definitive way of knowing when/how a record begins or ends, then some VBA code could most likely provide an answer. Is there at least one blank row between records, or does each record occupy the same nunber of rows (using blanks if required) ?
    If not, could the data be re-imported so that either of the above conditions could be met.

    Is there a chance that 205- is common to each record ?

    Andrew C

  3. #3
    New Lounger
    Join Date
    Apr 2001
    Location
    TX
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reformatting Text List into Columns (Excel 97)

    I can set it up so that there is a blank between each record.

    Another possiblity is to look for the text, "Dr. "

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Reformatting Text List into Columns (Excel 97)

    If a blank row exists between each record, the following code should copy the records from Sheet 1 to Sheet2 , using just one row per record on sheet 2.

    However no attempt has been made to insure that each field will align properly, if they are not in the same order for each record. <pre>Sub TransformRecords()
    Dim RecordNumber As Long, _
    ThisRow As Long, _
    totRows As Long
    totRows = Sheets(1).UsedRange.Rows.Count
    RecordNumber = 0
    Application.ScreenUpdating = False
    Sheets(1).Select
    Range("A1").Activate
    Do
    Selection.CurrentRegion.Copy
    Sheets(2).Range("A1").Offset(RecordNumber, 0) _
    .PasteSpecial Paste:=xlAll, Transpose:=True
    Sheets(1).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ThisRow = ActiveCell.Row
    RecordNumber = RecordNumber + 1
    Loop Until ThisRow > totRows
    Sheets(1).Range("A1").Activate
    Sheets(2).Activate
    Sheets(2).UsedRange.Select
    With Selection
    .WrapText = False
    .Columns.AutoFit
    End With
    Sheets(2).Range("A1").Select
    Application.ScreenUpdating = True
    End Sub</pre>

    I attach a workbook, with the data you provided as a sample. I replicated the records a number of times.

    Andrew C
    Attached Files Attached Files

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reformatting Text List into Columns (Excel 97)

    I stole that file, Andrew. Christmas is coming and I work for a software support desk. I'm REAL sick of converting all those mixed-up address lists (sometimes from both Excel and Word, sometimes from last year's LABELS--yukk!) and this'll be real helpful now that I'm into VBA enough to know what to do with your code. Thanks!

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Reformatting Text List into Columns (Excel 97)

    Your are welcome, and probably you can improve it as I am sure there is scope.

    The big snag is that if the number of rows or the content of equivalent rows do not match, it would be difficult if not impossible to construct a properly fielded table.

    In the data given by Bruce, the NAME position of the last record does not match the preceeding records. However, in that case it may be possible to something as it was mentioned the word Dr was common to all, hence that field at least could be allocated properly.

    Andrew

  7. #7
    New Lounger
    Join Date
    Apr 2001
    Location
    Rockton, Illinois, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reformatting Text List into Columns (Excel 97)

    I have a similar situation, where i have lots of records(three columns of data), with each record separated by an empty row. my problem is that i want to copy the first row of the record (the date) and then transpose the text in the last column with the exception of the last row.
    Attached Files Attached Files

Posting Permissions

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