vba to pull out information from multiple worksheets

abhi_jain80

New Member
Joined
May 31, 2021
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
Hi experts,

I got the vba script which is pulling out the relevant information from the "Transactions" worksheet to the "Output" worksheet against the unique transactions "Item#". All these information are available in the "Transactions" worksheet. Now I have the "Item#" in another worksheet say "Purchases" and need to pull out the other information from the "Transactions" worksheet to the "Output" worksheet.
I am trying to modify the script accordingly but could not able to do it. Can anyone of you support me with this please...thanks in advance.
Hope I am clear with the need. Attaching the images for all 3 worksheets.

VBA Code:
Sub DataTest()
    Dim vT, v
    Dim i As Long, ndx As Long, d As Object
    Dim t As Double, tc As Double, fic As Double
   
    t = Timer
    vT = Range("Transactions!A1").CurrentRegion.Value2
    Set d = CreateObject("Scripting.Dictionary")
   
    With Worksheets("Output")
        .Cells(1, 1).CurrentRegion.Offset(1).Clear
       
        'Item and Description Transactions
        For i = 2 To UBound(vT)
            d.Item(vT(i, 1)) = vT(i, 2)
        Next i
        .Cells(2, 1).Resize(d.Count) = Application.Transpose(d.keys)
        .Cells(2, 2).Resize(d.Count) = Application.Transpose(d.items)
       
        ReDim v(1 To d.Count, 1 To 6)
       
        For i = 2 To UBound(vT)
       
            ndx = Application.Match(vT(i, 1), d.keys, 0)
           
            If v(ndx, 2) = Empty Then
                v(ndx, 2) = vT(i, 3)            'First issue date
                v(ndx, 3) = vT(i, 6)            'Initial Cost
                fic = vT(i, 6)                  'First issue cost
                tc = 0
            End If
           
            v(ndx, 3) = Application.Min(v(ndx, 3), vT(i, 6))  'Min Cost
            v(ndx, 4) = Application.Max(v(ndx, 4), vT(i, 6))  'Max Cost
           
            v(ndx, 1) = v(ndx, 1) + vT(i, 4)    'Quantity
           
            tc = tc + vT(i, 7) 'running total cost to calculate avg cost
            If v(ndx, 1) <> 0 Then v(ndx, 5) = tc / v(ndx, 1) Else v(ndx, 5) = 0 'Average Cost
           
            If fic <> 0 Then v(ndx, 6) = (vT(i, 6) - fic) / fic 'Percent Change
           
        Next
       
        d.RemoveAll
       
        .Cells(2, 3).Resize(UBound(v, 1), 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
 

Attachments

  • Output.PNG
    Output.PNG
    26.4 KB · Views: 9
  • Transactions.PNG
    Transactions.PNG
    17.5 KB · Views: 9
  • Purchases.PNG
    Purchases.PNG
    23.9 KB · Views: 9

abhi_jain80

New Member
Joined
May 31, 2021
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
I am struggling to write a loop as you have suggested. I messed it up. Can you please help me with that also?

I need to sum the quantity against all unique items from "Qty" column2 of "purchases" sheet even if that item is not in "transactions" sheet and add that in the new column "qty ordered" in the "output" sheet.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,385
Office Version
  1. 2013
Platform
  1. Windows
I am struggling to understand what you need.
You could create an example with the various scenarios.
What does the purchases sheet have, what does the transactions sheet have, what does the inventory sheet have,
how you need the sums.
And the result in the output sheet.

Put here the examples with XL2BB tool.
 

abhi_jain80

New Member
Joined
May 31, 2021
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
Sorry if I have confused you. Here is an example of all 4 sheets - Transactions, Purchases, Inventory & Output.

The script you have provided is working fine for column A-H in the "output" sheet. Now I need the values in columns I & G against the items listed in column A.

Column I should come from "Column B - Purchases" sheet and Column G should come from "Column C - Inventory" sheet. These values should come even if the items are not in transactions sheet.
example: item# abcde (highlighted in yellow color) in "purchases" & "inventory" sheet which is not in "transactions" sheet. The "qty order" should appear as 45 and "bin qty" as 12 in the "output" sheet.
Hope I am clear now...Thanks

sample data .xlsb
ABCDEFG
1Item #DescriptionTransaction DateQtyType Cost Total Cost
212345678TWEEZERS, WHITE PLASTIC 5"13/01/202020ISSUE$ 2.14$ 42.71
312345678TWEEZERS, WHITE PLASTIC 5"18/02/2020100ISSUE$ 2.14$ 213.53
412345678TWEEZERS, WHITE PLASTIC 5"22/08/202020ISSUE$ 3.00$ 60.00
512345678TWEEZERS, WHITE PLASTIC 5"09/09/2020199ISSUE$ 4.00$ 796.00
623456789filter dpf01/01/20191ISSUE$ 32.00$ 32.00
723456789filter dpf06/01/20192ISSUE$ -$ -
823456789filter dpf31/01/20191ISSUE$ 23.00$ 23.00
923456789filter dpf07/02/20191ISSUE$ 21.00$ 21.00
1023456789filter dpf07/02/20191ISSUE$ 21.00$ 37.18
1120800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP09/01/20194ISSUE$ 37.18$ 148.70
1220800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP11/01/20191ISSUE$ 37.18$ 37.18
1320800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP11/01/20191ISSUE$ 37.18$ 37.18
1420800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP14/01/20192ISSUE$ 37.18$ 74.35
Transactions


sample data .xlsb
ABCDE
1Order DateQtyItem #DescriptionReceive Date
222/02/201910abcdeSMART LENS SAFETY GLASSES04/03/2019
320/08/201935abcdeSMART LENS SAFETY GLASSES21/08/2019
413/10/20202020300012-2GTPAPER, WHITE, CONTINUOUS FEED16/10/2020
519/08/2019520300012-2GTPAPER, WHITE, CONTINUOUS FEED20/08/2019
619/08/2019220300012-2GTPAPER, WHITE, CONTINUOUS FEED21/08/2019
719/02/20201020800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP21/05/2020
819/02/20202020800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP04/03/2020
919/02/20202023456789filter dpf26/02/2020
1019/02/20205820800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP21/02/2020
1119/02/20207220800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP
1204/02/20203310000076FILTER REGULATORAW4000-04CG Z19 XF313/02/2020
1304/02/20202721600008MRO300 OIL DRY PAD 1 box qty. of 10007/02/2020
1406/08/20196010000079CYL MGQL25-B7163-2012/08/2019
1523/05/20196010000080@ CYL. GUIDED 508CL MGQM25-B4827-17028/05/2019
Purchases


sample data .xlsb
ABCDE
1Item #DescriptionBin QtyOrder PointOrder Quantity
2abcdeSMART LENS SAFETY GLASSES12620
320800031BOUFFANT CAP, BLUE25091150
410000014ROUND BELT A AUTONICSLP-6020
510000021PRINT HEAD LABEL PRINT LP-1 P.HYD + SEC1625
610000011STERITRAY NOT DRILLED1124
720800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP221
810000024CYL GUIDED MGCMB25-450-R 828CL021
910000027BOUFFANT CAP, BLUE221
1023456789filter dpf043
1110000030CYL SMC MGQM32-24LX30100-00 818CL Z20021
1210000031CYL75MM-PP + 820 CL043
Inventory


sample data .xlsb
ABCDEFGHIJ
1Item #DescriptionQty IssuedFirst Issue DateMin CostMax CostAvg CostPerc ChangeQty OrderedBin Qty
2
3
4
Output
 

abhi_jain80

New Member
Joined
May 31, 2021
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
Sorry if I have confused you. Here is an example of all 4 sheets - Transactions, Purchases, Inventory & Output.

The script you have provided is working fine for column A-H in the "output" sheet. Now I need the values in columns I & G against the items listed in column A.

Column I should come from "Column B - Purchases" sheet and Column G should come from "Column C - Inventory" sheet. These values should come even if the items are not in transactions sheet.
example: item# abcde (highlighted in yellow color) in "purchases" & "inventory" sheet which is not in "transactions" sheet. The "qty order" should appear as 45 and "bin qty" as 12 in the "output" sheet.
Hope I am clear now...Thanks

sample data .xlsb
ABCDEFG
1Item #DescriptionTransaction DateQtyType Cost Total Cost
212345678TWEEZERS, WHITE PLASTIC 5"13/01/202020ISSUE$ 2.14$ 42.71
312345678TWEEZERS, WHITE PLASTIC 5"18/02/2020100ISSUE$ 2.14$ 213.53
412345678TWEEZERS, WHITE PLASTIC 5"22/08/202020ISSUE$ 3.00$ 60.00
512345678TWEEZERS, WHITE PLASTIC 5"09/09/2020199ISSUE$ 4.00$ 796.00
623456789filter dpf01/01/20191ISSUE$ 32.00$ 32.00
723456789filter dpf06/01/20192ISSUE$ -$ -
823456789filter dpf31/01/20191ISSUE$ 23.00$ 23.00
923456789filter dpf07/02/20191ISSUE$ 21.00$ 21.00
1023456789filter dpf07/02/20191ISSUE$ 21.00$ 37.18
1120800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP09/01/20194ISSUE$ 37.18$ 148.70
1220800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP11/01/20191ISSUE$ 37.18$ 37.18
1320800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP11/01/20191ISSUE$ 37.18$ 37.18
1420800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP14/01/20192ISSUE$ 37.18$ 74.35
Transactions


sample data .xlsb
ABCDE
1Order DateQtyItem #DescriptionReceive Date
222/02/201910abcdeSMART LENS SAFETY GLASSES04/03/2019
320/08/201935abcdeSMART LENS SAFETY GLASSES21/08/2019
413/10/20202020300012-2GTPAPER, WHITE, CONTINUOUS FEED16/10/2020
519/08/2019520300012-2GTPAPER, WHITE, CONTINUOUS FEED20/08/2019
619/08/2019220300012-2GTPAPER, WHITE, CONTINUOUS FEED21/08/2019
719/02/20201020800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP21/05/2020
819/02/20202020800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP04/03/2020
919/02/20202023456789filter dpf26/02/2020
1019/02/20205820800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP21/02/2020
1119/02/20207220800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP
1204/02/20203310000076FILTER REGULATORAW4000-04CG Z19 XF313/02/2020
1304/02/20202721600008MRO300 OIL DRY PAD 1 box qty. of 10007/02/2020
1406/08/20196010000079CYL MGQL25-B7163-2012/08/2019
1523/05/20196010000080@ CYL. GUIDED 508CL MGQM25-B4827-17028/05/2019
Purchases


sample data .xlsb
ABCDE
1Item #DescriptionBin QtyOrder PointOrder Quantity
2abcdeSMART LENS SAFETY GLASSES12620
320800031BOUFFANT CAP, BLUE25091150
410000014ROUND BELT A AUTONICSLP-6020
510000021PRINT HEAD LABEL PRINT LP-1 P.HYD + SEC1625
610000011STERITRAY NOT DRILLED1124
720800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP221
810000024CYL GUIDED MGCMB25-450-R 828CL021
910000027BOUFFANT CAP, BLUE221
1023456789filter dpf043
1110000030CYL SMC MGQM32-24LX30100-00 818CL Z20021
1210000031CYL75MM-PP + 820 CL043
Inventory


sample data .xlsb
ABCDEFGHIJ
1Item #DescriptionQty IssuedFirst Issue DateMin CostMax CostAvg CostPerc ChangeQty OrderedBin Qty
2
3
4
Output

Hi. I got this issue resolved. thank you
 

abhi_jain80

New Member
Joined
May 31, 2021
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
Hi DanteAmor,

Apologies for coming back to you again on the previous query. You have provided the below code which is pulling out the information from "transactions" and "purchases" sheet to the "output" sheet.
I am in need of one modification please, I am not able to figure out the way!

In "Perc Change" column in the "output sheet", I want to exclude the rows from the calculations where index number is 1 in column H in the "transactions" sheet. Can you please build this logic also? Attaching the mini-sheets for your reference. Many thanks in advance....

VBA Code:
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
ABCDEFGH
1Item #DescriptionQty IssuedFirst Issue DateMin CostMax CostAvg CostPerc Change
220800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP3289/1/2019£ 37.18£ 40.00£ 37.277.6%
323456789filter dpf61/1/2019£ 0.00£ 32.00£ 12.67-100.0%
410000076FILTER REGULATORAW4000-04CG Z19 XF36272/2/2019£ 0.37£ 40.00£ 1.12-99.0%
521600008MRO300 OIL DRY PAD 1 box qty. of 100294,2304/2/2019£ 0.00£ 40.00£ 0.37-99.0%
610000079CYL MGQL25-B7163-205244/2/2019£ 0.00£ 37.18£ 0.86-99.0%
710000080@ CYL. GUIDED 508CL MGQM25-B4827-17038512/4/2019£ 0.37£ 55.85£ 0.81-99.0%
Output


sample data .xlsb
ABCDEFGH
1Item #DescriptionTransaction DateQtyType Cost Total Cost Index Number
212345678TWEEZERS, WHITE PLASTIC 5"13/01/202020ISSUE$ -$ -1
312345678TWEEZERS, WHITE PLASTIC 5"18/02/2020100ISSUE$ 2.14$ 213.532
412345678TWEEZERS, WHITE PLASTIC 5"22/08/202020ISSUE$ 3.00$ 60.002
512345678TWEEZERS, WHITE PLASTIC 5"09/09/2020199ISSUE$ 4.00$ 796.002
623456789filter dpf01/01/20191ISSUE$ 32.00$ 32.002
723456789filter dpf06/01/20192ISSUE$ -$ -1
823456789filter dpf31/01/20191ISSUE$ 23.00$ 23.002
923456789filter dpf07/02/20191ISSUE$ 21.00$ 21.002
1023456789filter dpf07/02/20191ISSUE$ -$ -1
1120800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP09/01/20194ISSUE$ 37.18$ 148.702
Transactions


sample data .xlsb
ABCDE
1Order DateQtyItem #DescriptionReceive Date
219/02/20201020800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP21/05/2020
319/02/20202020800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP04/03/2020
419/02/20202023456789filter dpf26/02/2020
519/02/20205820800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP21/02/2020
619/02/20207220800030BOUFFANT CAP, PP728SBU000500BZ BOUFFANT CAP
704/02/20203310000076FILTER REGULATORAW4000-04CG Z19 XF313/02/2020
Purchases
 

Forum statistics

Threads
1,181,427
Messages
5,929,828
Members
436,697
Latest member
sunnypl

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
Top