vba code - build logic around percentage change calculations

[abhi_jain80]

Hi folks,

An expert helped me out with the below code which is pulling out the information from "transactions" and "purchases" worksheets to the "output" worksheet.

I need to build-in one more logic - to exclude all the rows from the "perc change" calculations (column H, "output" sheet) where the index number is 1 in column H, "transactions" sheet. I tried a lot to modify the code accordingly but failed to do so. Can anyone help me out please? Attaching the mini-sheets for your reference. Many thanks in advance....

Sub DataTest()
  Dim vT As Variant, vP As Variant, v As Variant, ky As Variant
  Dim i As Long, ndx As Long, d As Object, d2 As Object
  Dim t As Double, tc As Double, fic As Double
 
  t = Timer
  vP = Range("Purchases!A1").CurrentRegion.Value2
  vT = Range("Transactions!A1").CurrentRegion.Value2
  Set d = CreateObject("Scripting.Dictionary")
  Set d2 = CreateObject("Scripting.Dictionary")
  ReDim v(1 To UBound(vP), 1 To 8)
 
  'Unique Items from Purchase
  For i = 2 To UBound(vP)
    d2(vP(i, 3)) = vP(i, 4)
  Next i
 
  'Unique Items from Transactions
  For i = 2 To UBound(vT)
    d(vT(i, 1)) = Empty
  Next i
 
  'd.RemoveAll
  For Each ky In d2.keys
    If d.exists(ky) Then
      For i = 2 To UBound(vT)
        'Read only the items that are in Purchase
        If vT(i, 1) = ky Then
          'Item and data from Transactions
          If d(vT(i, 1)) = Empty Then
            ndx = ndx + 1
            d(vT(i, 1)) = ndx
            v(ndx, 1) = vT(i, 1)        'Item
            v(ndx, 2) = vT(i, 2)        'Description
            v(ndx, 4) = vT(i, 3)        'First issue date
            v(ndx, 5) = vT(i, 6)        'Initial Cost
            fic = vT(i, 6)              'First issue cost
            tc = 0
          Else
            ndx = d(vT(i, 1))
          End If
          
          v(ndx, 3) = v(ndx, 3) + vT(i, 4)    'Quantity
          v(ndx, 5) = Application.Min(v(ndx, 5), vT(i, 6))  'Min Cost
          v(ndx, 6) = Application.Max(v(ndx, 6), vT(i, 6))  'Max Cost
          
          tc = tc + vT(i, 7) 'running total cost to calculate avg cost
          If v(ndx, 3) <> 0 Then v(ndx, 7) = tc / v(ndx, 3) Else v(ndx, 7) = 0 'Average Cost
          If fic <> 0 Then v(ndx, 8) = (vT(i, 6) - fic) / fic 'Percent Change
        End If
      Next i
    Else
      ndx = ndx + 1
      v(ndx, 1) = ky            'Item
      v(ndx, 2) = d2(ky)        'Description
    End If
  Next ky
 
  With Worksheets("Output")
    .Cells(1, 1).CurrentRegion.Offset(1).Clear
    .Cells(2, 1).Resize(ndx, UBound(v, 2)).Value = v
    With .UsedRange
      .Columns("A:B").NumberFormat = "@"
      .Columns("C").NumberFormat = "#,##0"
      .Columns("D").NumberFormat = "d/m/yyyy"
      .Columns("E:G").NumberFormat = "$* #,##0.00"
      .Columns("H").NumberFormat = "0.0%"
    End With
  End With
 
  MsgBox Timer - t
End Sub

sample data .xlsb
	A	B	C	D	E	F	G	H
1	Item #	Description	Qty Issued	First Issue Date	Min Cost	Max Cost	Avg Cost	Perc Change
2	20800030	BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP	328	9/1/2019	£ 37.18	£ 40.00	£ 37.27	7.6%
3	23456789	filter dpf	6	1/1/2019	£ 0.00	£ 32.00	£ 18.86	-34.4%
4	10000076	FILTER REGULATORAW4000-04CG Z19 XF3	627	2/2/2019	£ 0.37	£ 40.00	£ 1.12	-99.0%
5	21600008	MRO300 OIL DRY PAD 1 box qty. of 100	294,230	4/2/2019	£ 0.00	£ 40.00	£ 0.37	-99.0%
6	10000079	CYL MGQL25-B7163-20	524	4/2/2019	£ 0.00	£ 37.18	£ 0.86	-99.0%
7	10000080	@ CYL. GUIDED 508CL MGQM25-B4827-170	385	12/4/2019	£ 0.37	£ 55.85	£ 0.81	-99.0%
8	10000081	ACTUATOR ROTARY SMC11-CDRA1BS50-90C A53L	1,267	7/2/2019	£ 0.37	£ 56.00	£ 0.68	-99.0%
9	10000086	CDBM2L25H-D7362-30 503CL	4	8/2/2019	£ 37.18	£ 37.18	£ 37.18	0.0%
Output

sample data .xlsb
	A	B	C	D	E	F	G	H
1	Item #	Description	Transaction Date	Qty	Type	Cost	Total Cost	Index Number
2	12345678	TWEEZERS, WHITE PLASTIC 5"	13/01/2020	20	ISSUE	$ -	$ -	1
3	12345678	TWEEZERS, WHITE PLASTIC 5"	18/02/2020	100	ISSUE	$ 2.14	$ 213.53	2
4	12345678	TWEEZERS, WHITE PLASTIC 5"	22/08/2020	20	ISSUE	$ 3.00	$ 60.00	2
5	12345678	TWEEZERS, WHITE PLASTIC 5"	09/09/2020	199	ISSUE	$ 4.00	$ 796.00	2
6	23456789	filter dpf	01/01/2019	1	ISSUE	$ 32.00	$ 32.00	2
7	23456789	filter dpf	06/01/2019	2	ISSUE	$ -	$ -	1
8	23456789	filter dpf	31/01/2019	1	ISSUE	$ 23.00	$ 23.00	2
9	23456789	filter dpf	07/02/2019	1	ISSUE	$ 21.00	$ 21.00	2
10	23456789	filter dpf	07/02/2019	1	ISSUE	$ -	$ -	1
Transactions

sample data .xlsb
	A	B	C	D	E
1	Order Date	Qty	Item #	Description	Receive Date
2	19/02/2020	10	20800030	BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP	21/05/2020
3	19/02/2020	20	20800030	BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP	04/03/2020
4	19/02/2020	20	23456789	filter dpf	26/02/2020
5	19/02/2020	58	20800030	BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP	21/02/2020
6	19/02/2020	72	20800030	BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP
7	04/02/2020	33	10000076	FILTER REGULATORAW4000-04CG Z19 XF3	13/02/2020
Purchases