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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,508
Messages
5,596,551
Members
414,077
Latest member
ammylar5

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
Top