Macro to extract values based on Month and year

[howard]

I have dates in Column b and values in Col C


I need to extract the items in Col A to C in sheet 'Data" based in the month and year in Col P and total value in Col Q and copy these to sheet Extraction


I have manually extracted the data to show you what I am trying to achieve

I want to extract the individual items where the value equals the value in Col Q as well as for the month and year in Col P


I would like a macro to extract this automatically

I have posted on the link below as well

Macro to extract values based on Month and year [SOLVED]

I have dates in Column b and values in Col C I need to extract the items in Col A to C in sheet 'Data" based in the month and year in Col P and total value in Col Q and copy these to sheet Extraction I have manually extracted the data I would like a macro to extract this automatically

www.excelforum.com

It would be appreciated if someone could kindly assist me



macro to extract values based on month and year macro to extract values.xlsx

 

[arunsjain]

You can summarise data using pivot table based on month. Or use power query and get data like "Group By" month column.

 

[howard]

Thanks for the reply and your input

​

In my example I want to extract all the items for Jan-2020 totalling 66,194.36. A Piviot table in this instance will not help

The only way to do this is using VBA. I have manually extracted the items totalling 66,194.36 pertaining to Jan-2020

It would be appreciated if someone can assist me in this regard

 

[lrobbo314]

Something like this...

Sub iLikeEnglishMuffins()
Dim data As Worksheet: Set data = Sheets("Data")
Dim res As Worksheet: Set res = Sheets("Extraction")
Dim ar() As Variant: ar = data.Range("A2:C" & data.Range("A" & Rows.Count).End(xlUp).Row).Value
Dim d As Date: d = "1/8/2020"
Dim sd As Object: Set sd = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(ar)
    If ar(i, 2) = d Then sd(ar(i, 1)) = sd(ar(i, 1)) + ar(i, 3)
Next i

res.Range("A1:C1").Value = Array("stock No.", "Dates", "Total Amount")
With res.Range("A2").Resize(sd.Count, 1)
    .Value = Application.Transpose(sd.keys)
    .Offset(, 1).Value = d
    .Offset(, 2).Value = Application.Transpose(sd.items)
End With
End Sub

 

[howard]

Thanks for the code


when running the macro , I get "application-defined or object-defined error" and the code below is highlighted

With res.Range("A2").Resize(sd.Count, 1)

Kindly test & amend

 

[lrobbo314]

My guess would be that it's because the dictionary didn't get anything added to it. Testing worked on this end. My guess is there is some issue with the dates. Your date format looks different than the US format, make sure that the date in the code matches the dates in your data.

 

[howard]

Thanks. Changes date format and it works

I would like you to make one change. I would like to be able extract a range of dates where the values total the value in Col Q for eg from 08/01/2020 (format dd/mm/yyyy) to say 10/01/2020


Kindly amend your code to accommodate this


Thanks


Howard

 

[mse330]

Based on the sample file you've shared, the amount in cell Q2 = 66,194.36 whereas the total in sheet Extraction = 64,982.10 ... It is not clear to me what is the criteria to select the data based on … If a summation that is equal to or less than the amount in Q2 then you would select stock number A6 to A9.

 

[howard]

My Apologies. The value in Q2 should have been R 64,982.10

If items on sheet "Data" do not total value in Q2 then no data to be extracted on sheet 'Extraction"

 

[mse330]

I used excel solver on the data for the month of Jan-20 and interestingly there's another set of possible answers equaling 64,982.10

Stock numbers 8, 9, 12, 16, 18 & 19