Sorting - data in columns - arranged finally in one column

[JP2R]

Greetings!

I'm using:
Microsoft XP
Microsoft Office 2007

I have a table with four columns of data.
It looks something like this:

Col1 Col2 Col3 Col4

VLan 123 name MACaddress
VLan 240 name MACaddress
VLan 140 name MACaddress

Using =concatenate() I have combined Column 1 and 2 and I have also combined Column 3 and 4, so that it looks this...

VLan 123 name MACaddress

Now what I'm trying to figure out is how to put it into a column so that it stacks like this:

VLan 123
name MACaddress
(skip row)
VLan 240
name MACaddress

and so on - please note that name and MACaddress are just place holders for actualy data - a persons name and the MACaddress to thier device.

Is there an easy way to do this - a function or VBA?

I would greatly appreciate any assistance offered.
Thanks so much
-- Jae :banghead:


VLan

[mikerickson]

This should do it. It does sort the returned column by the VLan column values.


Sub test()
With ThisWorkbook.Sheets("sheet1")
Range("A:A").Insert
With Range(.Cells(.Rows.Count, 2).End(xlUp), .Cells(1, 2))
.Offset(0, -1).Value = .Value
.Offset(.Rows.Count, -1).Value = .Value
.Offset(.Rows.Count * 2, -1).Value = .Value
.Offset(0, 1).Insert shift:=xlDown
End With

With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
.Resize(, 3).Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Offset(0, 1).SpecialCells(xlCellTypeBlanks).Delete shift:=xlToLeft
.EntireColumn.Delete
End With
End With
End Sub

If you do not want it sorted, how many entries are there?

LATER EDIT:
If you want a spreadsheet solution.
If "VLan 123" is in A1 and "name MACaddress" is in B1, then putting this in C1 and dragging down will work

=CHOOSE( MOD(ROW(),3)+1, "", INDEX(A:B,ROUNDUP(ROW()/3,0),1), INDEX(A:B,ROUNDUP(ROW()/3,0),2))

[david000]

This one has almost no formulas and almost no Vba.

If you have two columns side by side number them in a new column then
repeat the number again below the data in that same column.
Then sort that column.
Then you'll have a space between each entry.
Then drop one of the columns by right clicking and inserting a single cell thus offsetting the two columns.
Then select a column choose goto special blanks and type equals the one offset. Done.


Edit oops forgot about that space inbetween!

Sub AddRows()
Dim x As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For x = LastRow To 2 Step -2
Range("a" & x).EntireRow.Insert
Next x

End Sub

[JP2R]

Fantastic!

Thanks for the quick response - I'm going to take this to work and test it out.
I really appreciate this!

Most graciously
-- Jae