Move data automatically to another column - hairy one?

[Edmee]

I have a large spreadsheet (> 5000 rows) where I need to move data from certain rows from one column to the column next to it. Column A looks as follows:

A1 Server1\directory1A2 Group Users has rights
A3 Group TechUsers has rights
A4 Server1\directory2A5 Group Users has rights
A6 Server1\directory3A7 Group Users has rights
A8 Group Mining has rights
A9 Group Engineering has rights
A10 Server4\directory1A11 Group Users has rights
etc.....

Now I need to move the contens of cells that contain the Rights data to the column on the right of the corresponding Server cell above it. For instance, I would need to move the contents of cell A5, "Group users has rights", to cell B4 to be associated with the server mentioned in the cell above it. Now the cell contents of A2 and A3 would both need to be moved to the right of A1 to be associated with the server mentioned above it. Can the contents of multiple cells be appended or would they overwrite? How would I go about either appending numerous cell contents into another cell automatically, or move cell contents into consecutive colums, ie A2 to move to B1 and A3 to move to C1???

 

[Dreamboat]

Yep. A little hairy I think. Can you email the file? Feel free.
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[DarkSun]

Try this bit of code.

Dim ServerAtRow As Integer
Dim GroupPosition As Integer

Private Sub CommandButton1_Click()
For i = 1 To WorksheetFunction.CountA(ActiveSheet.Range("A:A&quot)

If InStr(1, ActiveSheet.Range(&quot;A&quot; & i).Value, &quot;\&quot <> 0 Then
ServerAtRow = i
GroupPosition = 2
End If

If Right(ActiveSheet.Range(&quot;A&quot; & i).Value, 10) = &quot;has rights&quot; Then
ActiveSheet.Cells(ServerAtRow, GroupPosition) = ActiveSheet.Range(&quot;A&quot; & i).Value
ActiveSheet.Range(i & &quot;:&quot; & i).Select
Selection.Delete
GroupPosition = GroupPosition + 1
i = i - 1

End If

Next i
ActiveSheet.Range(&quot;A1&quot.Select
End Sub

 

[Edmee]

The following solution was e-mailed to me by the wonderful &quot;Tech Support Girl&quot;:

I opened the file in Word. I did this:

Find: FDPE
Replace with: RIGHTS \\FDPE

Find: 5 spaces
Replace with: Tab character

Find: Tab + 4 spaces
Replace with: Tab character

Find: 4 spaces
Replace with: 4 hyphens (this was for those 6-letter strings that didn't
always have 6 letters)

Find: 2 Tabs
Replace with: 1 tab (until 0 replacements made, in other words all had been
replaced)

Find: Paragraph return symbol
Replace with: PARARETURN

Find: RIGHTS
Replace with: Paragraph return symbol + RIGHTS (this makes each line start
with RIGHTS and all &quot;records under that were tabbed to the right of them)

Find: PARARETURN (Remaining ones that didn't have RIGHTS following it)
Replace with: | character (called a Pipe)


Opened file in Excel using Pipe as delimited--as well as tabs

Now, some of these had 3 or 4 to the right of them. So I copied columns ABC
to second sheet, delete columns BC, copy new columns ABC to bottom of second
sheet's records, go back and delete columns BC until no more column BCs
left.

Deleted all records on second sheet that had path but no link. Sorted by
path.

I hope that what is left over is all your records.