I have data that is downloaded into sheet Imported Data
I need to extract data on sheet "Extract" from sheet "imported data" based on a date range and total Value
for eg extracted data from 05/02/2020 to say 10/02/2020 (date format dd/mm/yyyy) where the values in that range totals 64304.87
I have tried write code to do this, but cannot get any data extracted
It would be appreciated if someone could amend my code to accommodate my requirement
Please Login or Register to view this content.
Last edited by Howardc1001; 05-06-2020 at 10:39 PM.
Hi Howardc1001,
This should do the job:
Regards,Please Login or Register to view this content.
Robert
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
Thanks for the help Robert
I need to extract the data as follows from sheet "imported data" based on the following criteria
1) Type eg Type
F1 - DIS 120
F1 - DIS 180
2) Date range eg
Date Date
>=05Feb2020 <=10Feb2020
3) The value for the date range and Type eg
<=64304.87
It would be appreciated if you would kindly amend your code to accommodate the above
That's a bit different to your original request
Regards,Please Login or Register to view this content.
Robert
Last edited by Trebor76; 05-07-2020 at 03:27 AM.
Thanks for amending the code Robert
The values that are being extracted is more that the total value stipulated in the criteria for the date range as well as for the criteria type.
The criteria will change regularly. It would be better for the VBA code to refer to the cell/s pertaining to the criteria as opposed to it to be hard coded
It would be appreciated if you could kindly amend the code
Howard...May I ask how long solver takes to extract the values that meet the target value...
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
Have a look at cells B2:B5 of the Filter Criteria tab on the attached which govern what data gets filtered and transferred (refer Macro4).
@ Robert
What Howard is trying to achieve is the following...
Filter between two dates....Then filter Column G and keep only rows that add up to 64304.87
Untitled.png
Re: Macro to extract data based on date range and values
Hi,
as it could be done without any code just using an advanced filter, so easy to reproduce the single codeline needed …
Please share....so easy to reproduce the single codeline
Re: Macro to extract data based on date range and values
Hi Sintek !
Edit : I 'misread' as there was no attachment at the creation of this thread so it seems it's not an EZ filter & copy ?
Howard, according to your source data attach a workbook with the expected result, I'll better see if an advanced filter is the way to go …
Last edited by Marc L; 05-07-2020 at 08:45 AM.
I am trying to find other solution other than solver or literally looping each possible value multiple times to equate to required Target...
Image of solution posted above took my code 55 seconds...Crazy...
Re: Macro to extract data based on date range and values
I maybe have a faster solution but it requires a smart enough sheet with real dates, not poor "string date" like in the attachment
in order I can use an advanced filter, then for the sum it may be easy but depending on a crystal clear explanation of the need
as they are variations on how to calculate the sum like sequentially from a date sorted extraction,
find the better combination in order to be the closer to the sum, find the first combination equals to the sum, …
As guessing can't be coding !
See attached....Post 8 is required output
In fact it's ok for dates, was trapped 'cause of the left formatting !
I take a glance now to your attachment …
Re: Macro to extract data based on date range and values
I don't know if really post #8 is what is expected 'cause of the calculation variations,
the reason why the initial post must have an explanation & an attachment, both smart enough with nothin' to guess …
What should be the result when the exact sum can't be found ? What if …
Coding before any complete context is often just a waste of time !
Re: Macro to extract data based on date range and values
Howie,
if it's an extraction for an exact reconciliation - meaning if no combination matches the sum it returns nothing -
I already have an 'instant' solution but I just expect your confirmation before writing any codeline …
Hi Marc , Roberty & Sintek
Thanks for all your input
Sintek, was 100% correct in Post # 8 of what I wanted to achieve. If there is no match nothing must be returned
I have attached sample data and shown on sheet "Extract what I am trying to achieve by manually extracting the data
Ok it's just a reconciliation …
Edit : I will keep criterias on destination sheet 'Extract' …
Last edited by Marc L; 05-07-2020 at 01:47 PM.
Try this demonstration !
According to your last attachment, on the sheet 'Extract' :
- columns S & W must be empty or the advanced filter can't work as expected.
- Dates & sum to reconciliate must be in cells P2:R2, dates in columns U:V are managed by the procedure.
- R2 sum is optional.
This demonstration is not optimized for negative numbers,
the more data extracted, the longer reconciliation time …
You must paste these VBA procedures to the Sheet4 (Extract) worksheet module :
PHP Code:Sub VSum(V(), S$, ByVal C@, ByVal F&)
Dim R&, Z@
For R = F To UBound(V)
Z = C - V(R, 1)
If Z = 0 Then
S = "H" & R: Exit For
ElseIf Z > 0 Then
VSum V, S, Z, R + 1: If S > "" Then S = S & ",H" & R: Exit For
End If
Next
End Sub
Sub Demo1()
Const F = "yyyy/m/d"
Dim R, V(), S$
R = [R2].Value2: If Not IsNumeric(R) Then Beep: Exit Sub
Me.UsedRange.Offset(1).Columns("A:G").Clear
[T1].CurrentRegion.Offset(1).Columns("B:C").ClearContents
With [T1].CurrentRegion.Rows
If .Count > 1 Then
.Item("2:" & .Count).Columns("B:C") = Array([P1] & Format([P2], F), [Q1] & Format([Q2], F))
Application.ScreenUpdating = False
Sheet5.[A1].CurrentRegion.AdvancedFilter xlFilterCopy, .Cells, [A1].CurrentRegion
End If
End With
With [A1].CurrentRegion.Columns("A:H")
If .Rows.Count > 1 And R Then
.Sort [G1], xlDescending, Header:=xlYes
V = .Item(7).Value2
VSum V, S, R, 2
If S = "" Then
R = 2: Beep
Else
Range(S) = 1
R = Application.Count(.Item(8)) + 2
.Sort [H1], , [F1], , xlAscending, , , xlYes
End If
If R <= .Rows.Count Then .Rows(R & ":" & .Rows.Count).Clear
.Item(8).Clear
Else
R = .Rows.Count + 1
End If
If R > 2 Then Cells(R + 1, 6).Resize(, 2) = Array(" Total :", Application.Sum(.Item(7)))
End With
Application.ScreenUpdating = True
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 05-07-2020 at 11:23 PM.
Thanks for the clarification sintek
Based on this article this should do the job on the workbook I posted in thread 7:
Regards,Please Login or Register to view this content.
Robert
Re: Macro to extract data based on date range and values
Originally Posted by sintek
According to Howard' sample, my post #20 demonstration needs less than 0.1 second and
with the full data set in the sheet 'Imported Data' it requires less than 14 seconds on an old slow tests laptop …
Hi Marc
Thanks very Much for all the effort
Your code works perfectly where there are 2 Types. Where there is only one type, then the data is not extracted for the total value
I have highlighted in yellow 2 items in sheet "Imported Data" that she be extracted to sheet "Extract"
Please check and amend. I should be able to have up to 5 different types under heading "Type" on sheet "Extract" in Col T
Thanks for your help Robert
When running your code on the workbook I used in Post # 7, I get a run time error "Application defined or object defined error and the code below is highlighted
Please Login or Register to view this content.
Please test & advise
Try this:
Please Login or Register to view this content.
Re: Try this demonstration !
Thanks for the rep' !
First clear cell W1, when you need only one type clear cell T3.
Demonstration amended in post #20 and working as expected now …
Nice one this gents...As Type is also a condition... And split seconds needed for code...
Please Login or Register to view this content.
Thanks for all the effort Robert
Code works perfectly
Hi Sintek
Thanks for all your effort
Code works perfectly and extracts super fast
this one had to make me think lol...Tx for rep +
I'm sure it did as it was complex.
I have tested it with various criteria and working perfectly
Many thanks indeed for resolving this
I'm glad it all worked out and thanks for rep
Like sintek said this got the grey matter going
As they say 2 heads re better than one
once again many thanks to all of you for coming up with a solution
Re: Macro to extract data based on date range and values
Howard,
I hope you have seen my post #26 for my post #20 code amended
as it's the fastest (7 to 12 times according to your initial sample) using an advanced filter & a light 'combinatorics engine' …
Here we are like in your previous thread with the near same subject where we started with a dumb data sheet
(but well ended warming a couple of neurons with a smarter sheet so needing a light Excel basics VBA code for an instant result)
but according to your initial sample, the dates & types criterias extract only ten rows, so any combinatoric way seems fast enough …
So, if you are always in this schema with few rows matching the dates & types criterias,
choose the way you better understand in case you have to maintain it.
But the more rows matching the criterias, the longer reconcilation time …
According to your attachment, I ran the worst case, dates for all the year and all types so the full data set,
my light procedure needs less than 14 seconds to display the expected result on an old slow tests laptop,
after 3 minutes without any result I stopped the execution of Sintek's code,
Trebor's result was fast but with a 'not found' message …
But maybe I was wrong with my criterias for a well setup of their procedures, if they have time to test the same …
Hi Marc
I tested your code and forgot to reply to you
Many thanks for your effort
Robert, Sintek & you did a great job and it is sincerely appreciated
@ Marc L
Geez, really...that sucks...Worked so hard on that...Did not test with a larger data set or multiple numerous criteria...Only initial Thread requirement...after 3 minutes without any result I stopped the execution of Sintek's code
Re: Macro to extract data based on date range and values
As I wrote, according to the initial schema, any of our ways well does the job,
as an user don't see the difference between my process needing less than 0.05s and yours with more than 0.3s
as this user is obviously so happy to not have to do it manually !
Like I often say, the best procedure is not always the best one but the one the OP can well handle and maintain …
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks