I'm trying to figure out how to write a code in excel to do this I have a list of data like this:
a 12345 name address ect.
b 12345 name address ect.
c 12345 name address ect.
d 12345 name address ect.
e 12345 name address ect.
What I want to do is to move the data perpendiculare like this:
a 12345 name address ect. name address ect. name address ect.
I also need it to cut off the letter and the account number when it is moved and it always needs to move the corresponding data to the same location. If there isnt a b record then the spot where b record would have been would be blank. Each account will only have upto 5 records a-e. I am not a programmer so I dont know if this can be don or not. But since I have alot of records it isnt something that I can do by hand. Any help or advice would be greatly appreciated, thanks Neal.
Moving Data
WolframW
Hello Neal,
You did not provide the data structure as requested so have made some assumptions
1) A,B,C,D or E will be in one cell
2) Account number will be in a separate cell
3) Info will be in a separate cell too ie one cell for each piece of info
see below for data structure.
[Code Start]
Sub moveData()
Dim off As Integer
Dim rowID As String
Dim thisAccount As String
Dim currentAccount As String
Dim thisInfo As String
Dim dataBcol As Integer
Dim dataCcol As Integer
Dim dataDcol As Integer
Dim dataEcol As Integer
Dim columnOffset As Integer
Dim writeRow As Integer
dataBcol = 6 ' B to write in column F which is the 6th letter of the alphabet
dataCcol = 11 ' C to write in column K which is the 11th letter of the alphabet
dataDcol = 17 ' D to write in column Q which is the 17th letter of the alphabet
dataEcol = 23 ' E to write in column W which is the 23rd letter of the alphabet
Range("A1").Select
off = 0
columnOffset = 1
rowID = Selection.Offset(off, 0)
Do While rowID <> "" ' do while Column A contains something
currentAccount = Selection.Offset(off, 1) ' read account nbr
thisAccount = Selection.Offset(off, 1) ' set thisAccount to Current Account
thisInfo = Selection.Offset(off, 2) ' read the information in this row
Do While thisAccount = currentAccount ' do while the account is same as the one when A was read
Select Case rowID
Case "a", "A" ' decide what to do depending on row ID
writeRow = Selection.Offset(off, 0).Row ' make a note of which row to write to
Case "b", "B"
columnOffset = dataBcol - 1 ' set column to write to minus 1 to allow for
Case "c", "C" ' the fact that Col A is column 0 not column 1
columnOffset = dataCcol - 1
Case "d", "D"
columnOffset = dataDcol - 1
Case "e", "E"
columnOffset = dataEcol - 1
End Select
If rowID <> "A" Then
Selection.Offset(writeRow - 1, columnOffset).Value = thisInfo ' only write to sheet if rowID is not A
End If
off = off + 1 ' set pointer to next row
thisAccount = Selection.Offset(off, 1) ' read row, column 2
rowID = Selection.Offset(off, 0) ' read row, column 1
thisInfo = Selection.Offset(off, 2).Value ' read row, column 3
Loop ' do again if account is same as when it was read when rowID was A
Loop ' do again if rowID is not blank
End Sub
[Code Ends]
[Data Structure]
The original data is left where it is, you can delete with a macro ie
working down from row A1
if the rowID is not equal to A then
delete that row
endif
Please reply to let me know how you get on.
Good luck
Chas
nojetlag
This will not be a problem at all, how many cells with the name field occupy and how many cells for the address
ie is is going to be:
a 12345 lastname firstname address1 address2 address3 lastname firstname address1 address2 address3 lastname firstname address1 address2 address3 etc or will the name occupy only 1 cell and the address only 1 cell.
Let me know this info and I will post some code for it.
ChasAA
Edward1
The data will be like this:
A acct# debtor info
B acct# Comaker info
C acct# misc. info
D.acct# Misc. info
E. acct# Misc. info
A. acct# debtor info
B. acct# Comaker info.
C. acct# misc. info
D. acct# misc. info
E. acct# misc info
There will be on set for each debtor account. I need the data to be moved to a single line per debtor. But not every account will have all the data. B info has to start at cell f and c info starts at k and d info at q and e info at w. But when the b data is moved I need the leading letter and account number removed. So the lines would look like this:
A acct# debtor info comaker info misc. info misc. info misc. info
A acct# debtor info comaker info misc. info misc. info misc. info
Does this make sense