Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: moving info from columns to rows

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi -
    I have a report that downloads to excel with information in columns:

    account name code tel #
    12345 john pe 5551212
    12345 joe sl 5551213
    12345 jane ca 5551214
    6789 bill sl 4441111
    6789 brenda ca 4441112
    6789 brad pe 4441113

    and I need to move everything into rows like this:

    account name code tel# name code tel#
    12345 jane ca 5551212 joe sl 5551213...
    6789 brenda ca 4441112 bill sl 4441111

    Is there a way to automate this so that I don't have to cut and paste everything...

    I am new at this posting thing, so i hope this comes out right!!!

    ~s



  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok... not sure if my original post made enough sense...sorry...let me try again...

    anyway... I have this download, and it comes out with the following headings:
    account (A1), name (B1), code (C1) and tel# (D1). So A2-A5 could all be the same account with B2-B5 having the names of the various people working on that account. I need to change it so that there is only one row per account, and the rest of the information will be put into that row. ie: Row 2, A2 will contain the account number, Row 2, B2 will contain a name, Row 2, C2 will contain the corresponding code to the name in B2, Row 2, D2 will contain the tel #, Row 2, E2 will contain the next name associated with that account, Row 2,F2 will contain the code associated with the name in E2, Row 2, G2 will contain the tel# and so on and so forth.
    So rather than having four rows and four columns for each account, I need to have 1 row and 12 columns for each account.

    Is this any clearer? Probably not... I apologize... but if anyone can figure out what the heck i am talking about, I would be hugely grateful!

    ~s

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Is it okay to post the new data on a new spreadsheet, or does it have to go on existing sheet? Is there a limit on the number of names associated with an account?


  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I have the same questions as bergy, but I went forward and wrote something which may help.

    I made the assumption that there is no issue with retrieving the data to a worksheet (i.e. your import already works and/or you have the file open and active).

    Also, I am writing the data on the active sheet to "Sheet2" so please change to suit.

    If there are any accounts which will cause more than 256 entries, this will bomb for sure.

    Finally (!!!) if there are any blanks in the telephone number field, then your columns will not align consistently.

    '-----------------
    Sub test()
    Dim counter As Long, lastrow As Long, x As Long
    Dim matchrow As Long, lastcol As Integer

    With ActiveSheet
    .UsedRange
    lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    .Columns("G:G").Clear
    .Columns("A:A").AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=Range("G1"), Unique:=True
    .Range("G1").Delete shift:=xlUp

    counter = WorksheetFunction.CountA(.Range("G:G"))
    .Range("G1:G" & counter).Copy Sheets("sheet2").Range("A1")
    .Range("G1:G" & counter).ClearContents

    For x = 2 To lastrow
    matchrow = Application.Match(.Cells(x, 1), Sheets("Sheet2").Range("A:A"), 0)
    lastcol = Sheets("Sheet2").Cells(matchrow, 256).End(xlToLeft).Column
    .Range(.Cells(x, 2), .Cells(x, 4)).Copy Sheets("Sheet2").Cells(matchrow, lastcol + 1)
    Next x
    End With
    MsgBox "Done!"

    End Sub
    '---------------

    HTH,
    Jay

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi all -
    thanks so much so far... I will try the code. but in answer to the questions, yes, it is absolutely ok to post the data on a new spreadsheet. Also, there will never be any more than 5 names associated with a particular account. In fact, there will ALWAYS be 5 names associated with a particular account.

    Thanks again for all of your help!!!!

    ~s

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay -
    I tried your code, and it worked great... only problem is (my fault!) I neglected to mention one of the columns... there is a column for the account #, name, ID, code and tel#... I ran your code and if i didn't delete one of the columns, it did it for me. Unfortunately, i know nothing yet about actually WRITING codes (i think i'm lucky enough to know where to put them once they're written!!!). Therefore, I do not know where to go into your code to make an amendment...
    any help there?
    thanks again!!!

    ~s

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Susanna,

    I just answered a post that required the exact change you need.

    Change
    .Range(.Cells(x, 2), .Cells(x, 4)).Copy Sheets("Sheet2").Cells(matchrow, lastcol + 1)


    to

    .Range(.Cells(x, 2), .Cells(x, 5)).Copy Sheets("Sheet2").Cells(matchrow, lastcol + 1)

    the difference is here:
    Cells(x, 4)).Copy should be Cells(x, 5)).Copy

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  9. #9
    Board Regular
    Join Date
    Apr 2002
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    oops... guess my previous post means i'm speechless! jay, thank you so very much for all of your help.. it worked like a charm.

    susanna

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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