Transpose data from single column to multiple columns

[Blowzorn]

Hi All this is my first post hope you can help.

I have a range of data in a single column that I need to transpose in to multiple columns and rows.
My problem is that each data set can vary between 4 and 8 rows depending on how many SSIDs (VAPs) are assigned to the AP.
Each data set is separated by a "!"

What I'm trying to achieve is something like
AP-GROUP VAP_1 VAP_2 VAP_3 VAP_4 Radio_1 Radio_2 Profile

I've included an example of the raw data below.

Many thanks in advance.

!
ap-group "BUILDING_123"
virtual-ap "STAFF_VAP"
virtual-ap "GUEST_VAP"
dot11a-radio-profile "0dBm-15dBm_POWER_RADIOPROFILE_11A"
dot11g-radio-profile "0dBm-15dBm_POWER_RADIOPROFILE_11G"
ap-system-profile "VLAN801-VRRP213"
!
ap-group "CUSTOMER-TEST"
virtual-ap "CUSTOMER_VAP"
dot11a-radio-profile "0dBm_POWER_RADIOPROFILE_11A"
dot11g-radio-profile "0dBm_POWER_RADIOPROFILE_11G"
ap-system-profile "VLAN799-VRRP108"
!
ap-group "GENERAL-VLAN123-VRRP123"
virtual-ap "CUSTOMER_1_VAP"
virtual-ap "CUSTOMER_2_VAP"
virtual-ap "CUSTOMER_3_VAP"
dot11a-radio-profile "30dBm_POWER_RADIOPROFILE_11A"
dot11g-radio-profile "30dBm_POWER_RADIOPROFILE_11G"
ap-system-profile "VLAN123-VRRP123"

 

[VoG]

Try this with a copy of your sheet

Sub Addrs()
Dim Area As Range, LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("A" & i).Value = "!" Then Range("A" & i).ClearContents
Next i
For Each Area In Columns("A").SpecialCells(xlCellTypeConstants).Areas
    Area(1).Offset(, 1).Resize(, Area.Rows.Count).Value = Application.Transpose(Area)
Next Area
Columns("A").Delete
Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Blowzorn]

Peter.
Thanks for your prompt reply
However, I should have said I have no experience of VB so not sure where to start with this.

BR Paul

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor. Select Module from the Insert menu. Copy the code and paste it into the white space on the right. Press ALT + Q to close the code window.

Press ALT + F8, click on Addrs then click the Run button.

 

[Blowzorn]

Wow.

That's pretty amazing.
No idea what or how that's done.. Looks like I need to learn me some VB!
There's a few formatting bits where there's a miss match of columns but very easy sorted.

Big thanks once again.

Paul