Hi Bruce,

Am Wed, 6 Apr 2016 18:24:27 +0200 schrieb Claus Busch:

in your expected output you have in row 1 below Gross the value that is
behind WI in your source table. In row 2 you have a value that is in
front of WI.
Which value is correct?

I hope iseparated your data into the correct columns. Your data in
Sheet1.
Try:

Sub NewTable()
Dim varData As Variant, varOut() As Variant
Dim Cnt As Long, LRow As Long, i As Long, n As Long
Dim WName As String, Meter As String
Dim myRng As Range

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set myRng = .Range("A1:A" & LRow)
Cnt = Application.Evaluate("=SumProduct((MID(" _
& "Sheet1!" & myRng.Address & ",3,1)=""/"")*1)")
varData = .Range("A1:G" & LRow)
ReDim Preserve varOut(Cnt - 1, 4)
For i = LBound(varData) To UBound(varData)
If InStr(varData(i, 2), "@") Then
Meter = Split(varData(i, 1), "-")(0)
WName = Split(varData(i, 2), "@")(0)
End If
If InStr(varData(i, 1), "/") Then
varOut(n, 0) = Split(varData(i, 1), " ")(0)
varOut(n, 1) = Meter
varOut(n, 2) = WName
varOut(n, 3) = varData(i, 2)
varOut(n, 4) = varData(i, 7)
n = n + 1
End If
Next
End With
Sheets("Sheet2").Range("A2").Resize(Cnt, 5) = varOut
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional