This will list up when irregular happens in col.D at the bottom...
Sub test()
Dim a, b, x, y, i As Long, ii As Long, iii As Long, n As Long, myName
With [a6].CurrentRegion
x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(1).Address & "<>1,row(1:" & .Rows.Count & ")))"), False, 0)
y = Filter(.Parent.Evaluate("transpose(if(" & .Columns(1).Address & "=1,row(1:" & .Rows.Count & ")))"), False, 0)
If UBound(y) < 0 Then Exit Sub
a = .Value
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
For i = 0 To UBound(x)
n = n + 1
For ii = 1 To UBound(a, 2): b(n, ii) = a(x(i), ii): Next
If b(n, 1) Like "*,*" Then
myName = Trim$(Split(Trim$(Split(b(n, 1), ",")(1)))(0))
For ii = 0 To i + 2
If ii > UBound(y) Then Exit For
If a(y(ii), 4) <> "" Then
If Trim$(Split(a(y(ii), 4), ":")(0)) = myName Then
n = n + 1
For iii = 2 To UBound(a, 2)
b(n, iii) = a(y(ii), iii)
Next
b(n, 1) = a(x(i), 1): a(y(ii), 4) = Empty
End If
End If
Next
End If
Next
For i = 0 To UBound(y)
If a(y(i), 4) <> "" Then
n = n + 1
For ii = 1 To UBound(a, 2): b(n, ii) = a(y(i), ii): Next
End If
Next
.Value = b
End With
End Sub
Bookmarks