moving info from columns to rows

susanna

Board Regular
Joined
Apr 22, 2002
Messages
58
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top