Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2005
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel - Fill? (Excel 2002)

    I have an Excel file that I have converting into a text file for import. I am creating two lines of text for each row in different columns. I then need to copy those two columns into another sheet one after another. Example

    See attachment

    I need the output to be in exact order.
    A
    1 smith account report
    2 smith summary report
    3 jones account report
    4 jones summary report
    5 tyler account report
    6 tyler summary report

    I have a lot of data and never the same number of rows. I have tried to use "fill" but I can't seem to receive the correct data. What I receive is
    A1
    B1
    A3
    B3
    etc.

    I have also tried to insert a column and fill 1a in column A and 1b in column B. And then I copied column B to the end of column A and sorted. The results for this will then be 10a coming before 1a because they are text.

    Any ideas?

    Thank you!

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

    Re: Excel - Fill? (Excel 2002)

    Why not use insert empty columns to the left of both data columns and fill them with 1, 2, 3 etc.?

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>1</td><td align=right>1</td><td>smith account report</td><td align=right>1</td><td>smith summary report</td><td align=center>2</td><td align=right>2</td><td>jones account report</td><td align=right>2</td><td>jones summary report</td><td align=center>3</td><td align=right>3</td><td>tyler account report</td><td align=right>3</td><td>tyler summary report</td></table>
    Cut column C and D and paste below column A and B.
    Sort on column A.
    Delete column A.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - Fill? (Excel 2002)

    First, it would make it a lot easier for us to help you if you would post the workbook that we can use the test with rather than a .jpg image of it. Then we have to type everything back in to test our solutions.

    If that information is in worksheet Sheet1 and you want to do this with formulas, then go to an empty worksheet in the workbook and put these formulas in cells A1 and B1:

    <code>
    =ROW()
    =OFFSET(Sheet1!$B$1,INT(((ROW()-1)/2)),MOD(ROW()-1,2))
    </code>

    Select those two cells and copy them down as far as needed to get all of the data.

    The code below assumes your list is on Sheet1, and it will create the new list on Sheet2. It will clear all contents from Sheet2 first:

    <code>
    Public Sub CreateNewList()
    Dim lLastRow As Long, I As Long, J As Long
    lLastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    J = 0
    Worksheets("Sheet2").Cells.ClearContents
    For I = 0 To lLastRow
    Worksheets("Sheet2").Range("A1").Offset(J, 0).Value = J + 1
    Worksheets("Sheet2").Range("A1").Offset(J, 1).Value = Worksheets("Sheet1").Range("B1").Offset(I, 0).Value
    J = J + 1
    Worksheets("Sheet2").Range("A1").Offset(J, 0).Value = J + 1
    Worksheets("Sheet2").Range("A1").Offset(J, 1).Value = Worksheets("Sheet1").Range("C1").Offset(I, 0).Value
    J = J + 1
    Next I
    End Sub
    </code>
    Legare Coleman

  4. #4
    Lounger
    Join Date
    Mar 2005
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - Fill? (Excel 2002)

    Thank you for your response. Sorry about not inserting my problem correctly. How do I insert the file?

    The code I received does not seem to be working. The results I receive are
    1 smith summary
    2
    3 jones Summary
    4
    5 tyler summary

    Any ideas?

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

    Re: Excel - Fill? (Excel 2002)

    When you write a post or a reply, there is a box in which you can specify an attachment, either by typing the path and file name or (much easier) by clicking Browse...

    Note the instructions: file must be less than 100 KB (use a zip file if the workbook is too big), and don't preview the post/reply after filling the attachment box.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - Fill? (Excel 2002)

    You insert the .xls file the same way you inserted the .jpg file in your first post. Click on the Browse button next the "Attach a file" box, navigate to the .xls file, and click on it to select it, then click on the Open button. Just be sure that the .xls file is less than 100kb in size.

    I created a worksheet that looked just like the worksheet in your .jpg image and tested the code on that file and it worked. So, I guess I will have to see your workbook to tell you why it did not work on it. My best guess is that in the real workbook, column A does not have the row numbers like the .jpg you posted and what is in columns B and C in the .jpg are shifted to columns A and B.
    Legare Coleman

Posting Permissions

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