VBA help

Emmily

Emmily

Well-known Member
Joined
Oct 5, 2008
Messages
676
Hi, is there a macro that will produce the below result. For each team i want the count of entries per age criteria as reflected in row 1 and then i want the amount in (AUD) and then the number of entries which does not have a comment in col P and it value in AUD.

I know all of this can be done via formula, but i will have another 20 teams and the last age criteria will go to >90, so there will be a lot of formulas, hence it will slow the performance of the workbook.

Col L-P is raw data and R-S is FX data

Excel Workbook
ABCDEFGHIJKLMNOPQRS
12-56-29
2TeamNo.of itemsValue (AUD)No. of items without CommentsValue (AUD)No.of itemsValue (AUD)No. of items without CommentsValue (AUD)AmountCCYAgeSourceCommentsFX RATESRate (AUD)
3CMP1111,593.6615,796.8300.0000.008,268.57AUD1456TRACESAED3.712435
4TRACES20,000.00AUD1248TRACESANG1.809243
5LEHMAN1,000.00AUD1248TRACESARS4.053867
64,417.80GBP3CMP1TestATS10.203373
72,208.90GBP1018CMP1AUD1
8100,000.00AUD742TRACESBEF29.912398
91,163.22EUR561LEHMANBGN1.450275
10-3,320.14EUR561LEHMANGBP0.381053
119,646.51EUR561LEHMANTestBMD1.01075
12-12,618.80EUR561LEHMANBRL1.688256
13476,017.76EUR561LEHMANBSD1.01075
14-1,688,137.00JPY561LEHMANBVD2
157,187,517.00JPY561LEHMANCAD1.001098
16-6,303,971.00JPY561LEHMANCHF0.957838
17108,431.34USD561LEHMAN
18
19
20
Sheet1
 
Sort by date Sort by votes
Hi,

OK. Try this

Code: 
Sub kTest()
    
    Dim ka, k, i As Long, c As Long, n As Long, Idx, m As Long
    Dim dic1 As Object, Fx, Age As String, dic2 As Object, t()
    Dim Hdr, AgeGroup
    
    '// adjust to suit
    Const SourceShtName         As String = "Sheet1"
    Const SourceDataRange       As String = "L:P"
    Const StartRow              As Long = 3
    Const FxDataRange           As String = "R3:S16"
    Const DestRange             As String = "A1"
    '//End
    
    
    Hdr = Array("No. of items", "Value (AUD)", "No. of items without Comments", "Value (AUD)")
    AgeGroup = Array("2-5", "6-29", "30-59", "60-89", ">90")
    
    Set dic1 = CreateObject("scripting.dictionary")
        dic1.comparemode = 1
    Set dic2 = CreateObject("scripting.dictionary")
        dic2.comparemode = 1
        
    With Worksheets(CStr(SourceShtName))
        ka = Intersect(.UsedRange, .Range(CStr(SourceDataRange)))
        Fx = .Range(CStr(FxDataRange))
    End With
    
    For i = 1 To UBound(Fx, 1)
        dic1.Item(Fx(i, 1)) = Fx(i, 2)
    Next
    
    Age = ",{2,1;6,2;30,3;60,4;90,5}"
    
    ReDim k(1 To UBound(ka, 1), 1 To 21)
    
    c = 23 - Range(CStr(SourceDataRange)).Column
    
    For i = StartRow To UBound(ka, 1)
        Idx = Evaluate("=lookup(" & ka(i, 3) & Age & ")")
        If Not IsError(Idx) Then
            If Not dic2.exists(ka(i, 4)) Then
                n = n + 1
                k(n, 1) = ka(i, 4)
                If Len(ka(i, 5)) Then
                    k(n, Idx * 4 - 4 + 2) = 1
                    If CSng(dic1.Item(ka(i, 2))) Then k(n, Idx * 4 - 4 + 3) = CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                Else
                    k(n, Idx * 4 - 4 + 4) = 1
                    If CSng(dic1.Item(ka(i, 2))) Then k(n, Idx * 4 - 4 + 5) = CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                End If
                dic2.Add ka(i, 4), Array(n, c)
            Else
                t = dic2.Item(ka(i, 4))
                If Len(ka(i, 5)) Then
                    k(t(0), Idx * 4 - 4 + 2) = k(t(0), Idx * 4 - 4 + 2) + 1
                    If CSng(dic1.Item(ka(i, 2))) Then
                        k(t(0), Idx * 4 - 4 + 3) = k(t(0), Idx * 4 - 4 + 3) + CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                    End If
                Else
                    k(t(0), Idx * 4 - 4 + 4) = k(t(0), Idx * 4 - 4 + 4) + 1
                    If CSng(dic1.Item(ka(i, 2))) Then
                        k(t(0), Idx * 4 - 4 + 5) = k(t(0), Idx * 4 - 4 + 5) + CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                    End If
                End If
            End If
        End If
    Next
    
    If n Then
        With Worksheets(CStr(SourceShtName))
            If c > 0 Then .Cells(1).Resize(, c).EntireColumn.Insert
            .Rows(1).NumberFormat = "@"
            .Rows(2).WrapText = 1
            .Rows(2).Font.Bold = 1
            With .Range(CStr(DestRange))
                m = 1
                For i = 0 To UBound(AgeGroup)
                    .Offset(, m).Value = AgeGroup(i)
                    .Offset(1, m).Resize(, 4).Value = Hdr
                    m = m + 4
                Next
                .Offset(2).Resize(n, 21).Value = k
            End With
        End With
    End If
        
End Sub

HTH
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
I ran the code but did not see the highlighted results under the comments header and values

Excel Workbook
ARSTU
1>90
2No. of itemsValue (AUD)No. of items without CommentsValue (AUD)
3TRACES3128,268.57
4CMP115,796.8315,796.83
5LEHMAN9278920.125
Sheet1






Krishnakumar said:
Hi,

OK. Try this

Code: 
Sub kTest()
    
    Dim ka, k, i As Long, c As Long, n As Long, Idx, m As Long
    Dim dic1 As Object, Fx, Age As String, dic2 As Object, t()
    Dim Hdr, AgeGroup
    
    '// adjust to suit
    Const SourceShtName         As String = "Sheet1"
    Const SourceDataRange       As String = "L:P"
    Const StartRow              As Long = 3
    Const FxDataRange           As String = "R3:S16"
    Const DestRange             As String = "A1"
    '//End
    
    
    Hdr = Array("No. of items", "Value (AUD)", "No. of items without Comments", "Value (AUD)")
    AgeGroup = Array("2-5", "6-29", "30-59", "60-89", ">90")
    
    Set dic1 = CreateObject("scripting.dictionary")
        dic1.comparemode = 1
    Set dic2 = CreateObject("scripting.dictionary")
        dic2.comparemode = 1
        
    With Worksheets(CStr(SourceShtName))
        ka = Intersect(.UsedRange, .Range(CStr(SourceDataRange)))
        Fx = .Range(CStr(FxDataRange))
    End With
    
    For i = 1 To UBound(Fx, 1)
        dic1.Item(Fx(i, 1)) = Fx(i, 2)
    Next
    
    Age = ",{2,1;6,2;30,3;60,4;90,5}"
    
    ReDim k(1 To UBound(ka, 1), 1 To 21)
    
    c = 23 - Range(CStr(SourceDataRange)).Column
    
    For i = StartRow To UBound(ka, 1)
        Idx = Evaluate("=lookup(" & ka(i, 3) & Age & ")")
        If Not IsError(Idx) Then
            If Not dic2.exists(ka(i, 4)) Then
                n = n + 1
                k(n, 1) = ka(i, 4)
                If Len(ka(i, 5)) Then
                    k(n, Idx * 4 - 4 + 2) = 1
                    If CSng(dic1.Item(ka(i, 2))) Then k(n, Idx * 4 - 4 + 3) = CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                Else
                    k(n, Idx * 4 - 4 + 4) = 1
                    If CSng(dic1.Item(ka(i, 2))) Then k(n, Idx * 4 - 4 + 5) = CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                End If
                dic2.Add ka(i, 4), Array(n, c)
            Else
                t = dic2.Item(ka(i, 4))
                If Len(ka(i, 5)) Then
                    k(t(0), Idx * 4 - 4 + 2) = k(t(0), Idx * 4 - 4 + 2) + 1
                    If CSng(dic1.Item(ka(i, 2))) Then
                        k(t(0), Idx * 4 - 4 + 3) = k(t(0), Idx * 4 - 4 + 3) + CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                    End If
                Else
                    k(t(0), Idx * 4 - 4 + 4) = k(t(0), Idx * 4 - 4 + 4) + 1
                    If CSng(dic1.Item(ka(i, 2))) Then
                        k(t(0), Idx * 4 - 4 + 5) = k(t(0), Idx * 4 - 4 + 5) + CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                    End If
                End If
            End If
        End If
    Next
    
    If n Then
        With Worksheets(CStr(SourceShtName))
            If c > 0 Then .Cells(1).Resize(, c).EntireColumn.Insert
            .Rows(1).NumberFormat = "@"
            .Rows(2).WrapText = 1
            .Rows(2).Font.Bold = 1
            With .Range(CStr(DestRange))
                m = 1
                For i = 0 To UBound(AgeGroup)
                    .Offset(, m).Value = AgeGroup(i)
                    .Offset(1, m).Resize(, 4).Value = Hdr
                    m = m + 4
                Next
                .Offset(2).Resize(n, 21).Value = k
            End With
        End With
    End If
        
End Sub
HTH
Click to expand...
 
Upvote 0
Hi,

This is what I got.

Book1
ABCDEFGHIJKLMNOPQRSTU
12-56-2930-5960-89>90
2No. of itemsValue (AUD)No. of items without CommentsValue (AUD)No. of itemsValue (AUD)No. of items without CommentsValue (AUD)
3TRACES4129268.6
4CMP1111593.6615796.832
5LEHMAN18
Sheet1
 
Upvote 0
With your result its now missing the Total no of items, and AUD Value in R and S. The purpose of this task is to show management what the total entries are for each team and how many entries are there which have no comments and its AUD value.

Excel Workbook
ARSTU
1>90
2No. of itemsValue (AUD)No. of items without CommentsValue (AUD)
3TRACES3128,268.57
4CMP115,796.8315,796.83
5LEHMAN9278920.125
Sheet1






Krishnakumar said:
Hi,

This is what I got.

******** ******************** ************************************************************************><center><table align="center" cellpadding="0" cellspacing="0"><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="22" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tr><td align="left">Microsoft Excel - Book1</td><td style="font-family: caption; color: rgb(255, 255, 255); font-size: 9pt;" align="right">___Running: 11.0 : OS = </td></tr></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); height: 25px; border-right: 0.5pt solid rgb(0, 0, 0);" colspan="22" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tr><td style="font-family: caption; color: rgb(0, 0, 0); font-size: 10pt;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td align="right" valign="center"><form name="formCb059465"><input *******="window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);" value="Copy Formula" name="btCb290334" type="button"></form></td></tr></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="22" bgcolor="white"><table border="0"><tr><form name="formFb543420"></form><td style="width: 60px;" align="middle" bgcolor="white"><select gtbfieldid="101" onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name="sltNb142651"><option selected="selected" value="">A1</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="white"><input gtbfieldid="102" size="80" name="txbFb965317"></td></tr></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle" width="2%">
</td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>A</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>B</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>C</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>D</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>E</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>F</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>G</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>H</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>I</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>J</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>K</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>L</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>M</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>N</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>O</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>P</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>Q</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>R</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>S</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>T</center></td><td style="background-color: rgb(212, 208, 200); font-family: menu; color: black; font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" align="middle"><center>U</center></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200); font-family: menu; color: rgb(0, 0, 0); font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0);" align="middle" width="2%"><center>1</center></td><td style="border: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom;">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">2-5</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">6-29</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">30-59</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">60-89</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">>90</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);">
</td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200); font-family: menu; color: rgb(0, 0, 0); font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0);" align="middle" width="2%"><center>2</center></td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">No. of items</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">Value (AUD)</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">No. of items without Comments</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">Value (AUD)</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">No. of items</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">Value (AUD)</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">No. of items without Comments</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; font-weight: bold; border-right: 0.5pt solid rgb(0, 0, 0);">Value (AUD)</td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200); font-family: menu; color: rgb(0, 0, 0); font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0);" align="middle" width="2%"><center>3</center></td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">TRACES</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">4</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">129268.6</td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200); font-family: menu; color: rgb(0, 0, 0); font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0);" align="middle" width="2%"><center>4</center></td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">CMP1</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">1</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">11593.66</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">1</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">5796.832</td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200); font-family: menu; color: rgb(0, 0, 0); font-size: 10pt; border-top: 0.5pt solid rgb(0, 0, 0);" align="middle" width="2%"><center>5</center></td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: left; border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">LEHMAN</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">1</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">8</td><td style="border-bottom: 0.5pt solid rgb(0, 0, 0); text-align: right; background-color: rgb(255, 255, 255); font-family: Calibri; color: rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; border-right: 0.5pt solid rgb(0, 0, 0);">
</td></tr><tr><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); background-color: rgb(212, 208, 200);" colspan="22"><table valign="TOP" align="left" width="100%"><tr><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); background-color: rgb(255, 255, 255); width: 120pt;" align="left">Sheet1</td><td>
</td></tr></table></td></tr></table>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>
Click to expand...
 
Upvote 0
Hi,

What about this one ?

Code: 
Sub kTest()
    
    Dim ka, k, i As Long, c As Long, n As Long, Idx, m As Long
    Dim dic1 As Object, Fx, Age As String, dic2 As Object, t()
    Dim Hdr, AgeGroup
    
    '// adjust to suit
    Const SourceShtName         As String = "Sheet1"
    Const SourceDataRange       As String = "L:P"
    Const StartRow              As Long = 3
    Const FxDataRange           As String = "R3:S16"
    Const DestRange             As String = "A1"
    '//End
    
    
    Hdr = Array("No. of items", "Value (AUD)", "No. of items without Comments", "Value (AUD)")
    AgeGroup = Array("2-5", "6-29", "30-59", "60-89", ">90")
    
    Set dic1 = CreateObject("scripting.dictionary")
        dic1.comparemode = 1
    Set dic2 = CreateObject("scripting.dictionary")
        dic2.comparemode = 1
        
    With Worksheets(CStr(SourceShtName))
        ka = Intersect(.UsedRange, .Range(CStr(SourceDataRange)))
        Fx = .Range(CStr(FxDataRange))
    End With
    
    For i = 1 To UBound(Fx, 1)
        dic1.Item(Fx(i, 1)) = Fx(i, 2)
    Next
    
    Age = ",{2,1;6,2;30,3;60,4;90,5}"
    
    ReDim k(1 To UBound(ka, 1), 1 To 21)
    
    c = 23 - Range(CStr(SourceDataRange)).Column
    
    For i = StartRow To UBound(ka, 1)
        Idx = Evaluate("=lookup(" & ka(i, 3) & Age & ")")
        If Not IsError(Idx) Then
            If Not dic2.exists(ka(i, 4)) Then
                n = n + 1
                k(n, 1) = ka(i, 4)
                k(n, Idx * 4 - 4 + 2) = 1
                If CSng(dic1.Item(ka(i, 2))) Then k(n, Idx * 4 - 4 + 3) = CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                If Len(ka(i, 5)) Then
                    k(n, Idx * 4 - 4 + 4) = 1
                    If CSng(dic1.Item(ka(i, 2))) Then k(n, Idx * 4 - 4 + 5) = CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                End If
                dic2.Add ka(i, 4), Array(n, c)
            Else
                t = dic2.Item(ka(i, 4))
                k(t(0), Idx * 4 - 4 + 2) = k(t(0), Idx * 4 - 4 + 2) + 1
                If CSng(dic1.Item(ka(i, 2))) Then
                    k(t(0), Idx * 4 - 4 + 3) = k(t(0), Idx * 4 - 4 + 3) + CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                End If
                If Len(ka(i, 5)) Then
                    k(t(0), Idx * 4 - 4 + 4) = k(t(0), Idx * 4 - 4 + 4) + 1
                    If CSng(dic1.Item(ka(i, 2))) Then
                        k(t(0), Idx * 4 - 4 + 5) = k(t(0), Idx * 4 - 4 + 5) + CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                    End If
                End If
            End If
        End If
    Next
    
    If n Then
        With Worksheets(CStr(SourceShtName))
            If c > 0 Then .Cells(1).Resize(, c).EntireColumn.Insert
            .Rows(1).NumberFormat = "@"
            .Rows(2).WrapText = 1
            .Rows(2).Font.Bold = 1
            With .Range(CStr(DestRange))
                m = 1
                For i = 0 To UBound(AgeGroup)
                    .Offset(, m).Value = AgeGroup(i)
                    .Offset(1, m).Resize(, 4).Value = Hdr
                    m = m + 4
                Next
                .Offset(2).Resize(n, 21).Value = k
                On Error Resume Next
                .Offset(2).Resize(n, 21).SpecialCells(4).Value = 0
                On Error GoTo 0
            End With
        End With
    End If
        
End Sub

BTW, don't quote entire post every time if it's not relevant.
 
Upvote 0
Ok we are getting close. Ok with the below, the code should not put 1 where there is comment, for CMP1 for 2-5 age criteria, if there is a comment then i don't expect a result in "No of items without comments"

This is what i should be getting

Sheet1

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 95.2px;"><col style="width: 205.6px;"><col style="width: 64px;"><col style="width: 115.2px;"><col style="width: 198.4px;"><col style="width: 218.4px;"><col style="width: 357.6px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>R</td><td>S</td><td>T</td><td>U</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td style="text-align: right;">2-5</td><td>
</td><td>
</td><td>
</td><td>>90</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="font-weight: bold;">
</td><td style="font-weight: bold;">No. of items</td><td style="font-weight: bold;">Value (AUD)</td><td style="font-weight: bold;">No. of items without Comments</td><td style="font-weight: bold;">Value (AUD)</td><td style="font-weight: bold;">No. of items</td><td style="font-weight: bold;">Value (AUD)</td><td style="font-weight: bold;">No. of items without Comments</td><td style="font-weight: bold;">Value (AUD)</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>TRACES</td><td style="text-align: center;">0</td><td style="text-align: center;">0</td><td style="text-align: center;">0</td><td style="text-align: center;">0</td><td style="text-align: center;">3</td><td style="text-align: center;">128,268.57</td><td style="text-align: center;">3</td><td style="text-align: center;">128268.5703</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>CMP1</td><td style="text-align: center;">1</td><td style="text-align: center;">11593.66309</td><td style="background-color: rgb(255, 255, 0); text-align: center;">0</td><td style="background-color: rgb(255, 255, 0); text-align: center;">0</td><td style="text-align: center;">1</td><td style="text-align: center;">5,796.83</td><td style="text-align: center;">1</td><td style="text-align: center;">5,796.83</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td>LEHMAN</td><td style="text-align: center;">0</td><td style="text-align: center;">0</td><td style="text-align: center;">0</td><td style="text-align: center;">0</td><td style="text-align: center;">9</td><td style="text-align: center;">-162660.75</td><td style="background-color: rgb(255, 255, 0); text-align: center;">8</td><td style="background-color: rgb(255, 255, 0); text-align: center;">-168374.65</td></tr></tbody></table>
 
Upvote 0
By doing that, it produces no result with entries which have no comments.

The result in #16 is what i expect

Excel Workbook
ABCDERSTU
12-5>90
2No. of itemsValue (AUD)No. of items without CommentsValue (AUD)No. of itemsValue (AUD)No. of items without CommentsValue (AUD)
3TRACES00003128,268.5700
4CMP1111593.663090015,796.8300
5LEHMAN00009-162660.7500
Sheet1
 
Upvote 0
Hi,

This is what I got.


Excel Workbook
ABCDERSTUVWXYZAAABACAD
1*2-5***>90***
2*No. of itemsValue (AUD)No. of items without CommentsValue (AUD)No. of itemsValue (AUD)No. of items without CommentsValue (AUD)AmountCCYAgeSourceCommentsFX RATESRate (AUD)
3TRACES00004129268.64129268.68,268.57AUD1456TRACESAED3.712435
4CMP1111593.660015796.83215796.83220,000.00AUD1248TRACESANG1.809243
5LEHMAN000090801,000.00AUD1248TRACESARS4.053867
6*********4,417.80GBP3CMP1TestATS10.20337
7*********2,208.90GBP1018CMP1AUD1
8*********1,00,000.00AUD742TRACESBEF29.9124
9*********1,163.22EUR561LEHMANBGN1.450275
10*********-3,320.14EUR561LEHMANGBP0.381053
11*********9,646.51EUR561LEHMANTestBMD1.01075
12*********-12,618.80EUR561LEHMANBRL1.688256
13*********4,76,017.76EUR561LEHMANBSD1.01075
14*********-16,88,137.00JPY561LEHMANBVD2
15*********71,87,517.00JPY561LEHMANCAD1.001098
16*********-63,03,971.00JPY561LEHMANCHF0.957838
17*********1,08,431.34USD561LEHMAN
Sheet1



Replace again those lines with

Code: 
If Len(Trim$(ka(i, 5))) = 0 Then
 
Upvote 0
Kris, could replace your fx rates with these. And see what result you get. As a test i changed few rates to accomodate the data.

Excel Workbook
ACAD
2FX RATESRate (AUD)
3AED3.712435
4ANG1.809243
5ATS10.203373
6AUD1
7BEF29.912398
8JPY1.450275
9GBP0.381053
10BMD1.01075
11EUR1.688256
12BSD1.01075
13BVD2
14CAD1.001098
15USD0.957838
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Alternative to complex SumProduct to rank data by specific variable
Replies
3
Views
766
Peter_SSs
Peter_SSs
M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
959
MoonDancer
M
A
  • Solved
why the code is slow despite of using advanced filter with simple data
2 3
Replies
23
Views
426
abdelfattah
A
Emka
  • Question Question
Web Scrap on Chrome via VBA
Replies
0
Views
140
Emka
Emka
N
  • Question Question
Data Transformation using VBA like subtotal
Replies
1
Views
146
Domenic
D

Forum statistics

Threads
1,224,564
Messages
6,179,543
Members
452,924
Latest member
JackiG

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top