# Convert multiple rows into single columns

#### antlan1969

##### New Member
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...

 Username user1 Drive P UNCPath \\server1\share1 Drive T UNCPath \\server2\share2 Drive Z UNCPath \\server3\share1 Username user2 Drive P UNCPath \\server1\share1 Drive T UNCPath \\server2\share2 Username user3

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

To look like this.....

 Username Drive UNCPath user1 P \\server1\share1 user1 T \\server2\share2 user1 Z \\server3\share1 user2 P \\server1\share1 user2 T \\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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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")
.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
2DrivePuser1P\\server1\share1
3UNCPath\\server1\share1user1T\\server2\share2
4DriveTuser1Z\\server3\share1
5UNCPath\\server2\share2user2P\\server1\share1
6DriveZuser2T\\server2\share2
7UNCPath\\server3\share1
9DriveP
10UNCPath\\server1\share1
11DriveT
12UNCPath\\server2\share2
14
Rearrange

Replies
10
Views
986
Replies
11
Views
618
Replies
13
Views
1K
Replies
1
Views
593
Replies
3
Views
277

1,203,727
Messages
6,056,966
Members
444,899
Latest member
Excel_Temp

### 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.

### Which adblocker are you using?

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

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