moving info from columns to rows

[susanna]

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

[susanna]

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

[bergy]

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?

[Jay Petrulis]

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

[susanna]

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

[susanna]

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

[Jay Petrulis]

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

[susanna]

[susanna]

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