Convert multiple rows into single columns

antlan1969

New Member
Joined
Mar 1, 2016
Messages
1
Hi,

I hope I can describe this properly. I have imported a load of CSVs into 1 big file but the 3 headings I want to use are all rows at the moment.

I am a bit of an Excel newbie when it comes to manipulating data. I need help trying to convert the data from something like this...

Usernameuser1
DriveP
UNCPath\\server1\share1
DriveT
UNCPath\\server2\share2
DriveZ
UNCPath\\server3\share1
Usernameuser2
DriveP
UNCPath\\server1\share1
DriveT
UNCPath\\server2\share2
Usernameuser3

<colgroup><col><col></colgroup><tbody>
</tbody>

To look like this.....

UsernameDriveUNCPath
user1P\\server1\share1
user1T\\server2\share2
user1Z\\server3\share1
user2P\\server1\share1
user2T\\server2\share2

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I am figuring if I can get the data I have into this format it will be easier to analyse.

Any help would be appreciated!

Cheers,

Anthony
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Welcome to the MrExcel board!

You could try this macro. (Post back if you need instructions for how to implement it)
It does assume that the first username is in row 1.

Test in a copy of your workbook.

Rich (BB code):
Sub Rearrange()
  Dim a, b
  Dim i As Long, k As Long
  
  a = Range("A1").CurrentRegion.Value
  ReDim b(1 To UBound(a), 1 To 3)
  i = 1
  Do While i < UBound(a)
    k = k + 1
    If a(i, 1) = "Username" Then
      b(k, 1) = a(i, 2)
      i = i + 1
    Else
      b(k, 1) = b(k - 1, 1)
    End If
    b(k, 2) = a(i, 2): b(k, 3) = a(i + 1, 2)
    i = i + 2
  Loop
  With Range("D1:F1")
    .Value = Array("Username", "Drive", "UNCPath")
    .Offset(1).Resize(k).Value = b
    .EntireColumn.AutoFit
  End With
End Sub


For sample data in columns A:B below, the code produced columns D:F

Excel Workbook
ABCDEF
1Usernameuser1UsernameDriveUNCPath
2DrivePuser1P\\server1\share1
3UNCPath\\server1\share1user1T\\server2\share2
4DriveTuser1Z\\server3\share1
5UNCPath\\server2\share2user2P\\server1\share1
6DriveZuser2T\\server2\share2
7UNCPath\\server3\share1
8Usernameuser2
9DriveP
10UNCPath\\server1\share1
11DriveT
12UNCPath\\server2\share2
13Usernameuser3
14
Rearrange
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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