Rearranging data

[The Boondock Saint]

Hi again all, I just had some really awesome help which would have taken me
months to figure our myself, so I thought id try my luck a 2nd time and see
if I can find the solution to another problem I have,

Ive got a sheet, and its got the followings format,

A1 - Persons Name (Job Title)
B1 - Hobbies
C1 - Persons Name (Job Title)
D1- Hobbies

What Id like to be able to do, is to take the Job Title from within the
brackets, and put it into say A2 (so basically moving the text and removing
the brackets)
and then take the hobbies and put them into say A3 then follow that by
deleting the Hobbies line, so that all the data for each person is in 1 row

So A1 = Name
A2 = Title
A3 = Hobbies

B1 = Name
B2 = Title
B3 = Hobbies

etc etc all the way down the page,

Im really not even sure what this process would be called, Ive tried looking
for information but never found anything that I could grasp,

Anyone done anything like this before?

Cheers Saint.

 

[Brian]

If you have experience with VBA programming it shouldn't be too bad.

Try this as a macro:


******************************************************

Dim i As Double
Dim j As Double
Dim jt As String

Range("A1").Activate
Do Until ActiveCell.Offset(0, i).Value = ""
jt = Mid(ActiveCell.Offset(0, i).Value, InStr(1,
ActiveCell.Offset(0, i).Value, "(") + 1, Len(ActiveCell.Offset(0,
i).Value))
jt = Left(jt, Len(jt) - 1)
ActiveCell.Offset(j, 0).Value = Left(ActiveCell.Offset(0, i),
InStr(1, ActiveCell.Offset(0, i).Value, "(") - 1)
ActiveCell.Offset(j + 1, 0).Value = jt
ActiveCell.Offset(j + 2, 0).Value = ActiveCell.Offset(0, i +
1).Value


i = i + 2
j = j + 3

************************************************

 

[The Boondock Saint]

Thanks for the help Brian,

I used the following code from your example, but what its doing is only
working on the first 3 lines , and putting them down the col not across the
row,

so its going into A1 , B1 , C1 instead of it going into A1 A2 and A3 and
then deleting the extra space of B1 is that makes sense, .....

Any thoughts or ideas?
Cheers Saint

Sub Rearrange()
Dim i As Double
Dim j As Double
Dim jt As String

Range("A1").Activate
Do Until ActiveCell.Offset(0, i).Value = ""
jt = Mid(ActiveCell.Offset(0, i).Value, InStr(1, ActiveCell.Offset(0,
i).Value, "(") + 1, Len(ActiveCell.Offset(0, i).Value))
jt = Left(jt, Len(jt) - 1)
ActiveCell.Offset(j, 0).Value = Left(ActiveCell.Offset(0, i), InStr(1,
ActiveCell.Offset(0, i).Value, "(") - 1)
ActiveCell.Offset(j + 1, 0).Value = jt
ActiveCell.Offset(j + 2, 0).Value = ActiveCell.Offset(0, i + 1).Value
i = i + 2
j = j + 3
Loop
End Sub

 

[Brian]

First of all, sorry for the late response, I didn't check the boards
all weekend.

Second, I got messed up with your A1 references. I thought that your
info was in a row, and you wanted it in a column. Typically A, B, C
etc refer to columns, so that A1 and B1 are on the same row.


Try This:

Sub Rearrange()
Dim i As Double
Dim j As Double
Dim jt As String

Range("A1").Activate
Do Until ActiveCell.Offset(i, 0).Value = ""
jt = Mid(ActiveCell.Offset(i, 0).Value, InStr(1,
ActiveCell.Offset(i, 0).Value, "(") + 1, Len(ActiveCell.Offset(i,
0).Value))
jt = Left(jt, Len(jt) - 1)
ActiveCell.Offset(i, 0).Value = Left(ActiveCell.Offset(i, 0),
InStr(1, ActiveCell.Offset(i, 0).Value, "(") - 1)
ActiveCell.Offset(i, 1).Value = jt
ActiveCell.Offset(i, 2).Value = ActiveCell.Offset(i + 1, 0).Value
i = i + 2
Loop

Range("A1").Activate
Do Until ActiveCell.Value = ""
If ActiveCell.Offset(0, 1).Value = "" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Activate
End If

Loop



End Sub

 

[The Boondock Saint]

Awesome Brian,
Thanks so much for that, thats making it work perfectly.

Cheers for that, thats brillant

Cheers Saint