date wise data required

M

majidsiddique

Board Regular
Joined
Oct 22, 2018
Messages
164
Hi all,

i would like to record in date wise in sheet2:
eg: A2 start date
A3 end date


between all the data extract both date in copy sheet2. in additional criteria transaction type: Cash or A/Payable
A3: Cash or A/Payable if any select

[TABLE="width: 596"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Purhas Date [/TD]
[TD]Bill No[/TD]
[TD]Vandor[/TD]
[TD]Item[/TD]
[TD]Qty[/TD]
[TD]Rate[/TD]
[TD]Amount[/TD]
[TD]Trasaction Type[/TD]
[/TR]
[TR]
[TD]02/08/2018[/TD]
[TD]1101[/TD]
[TD]Sultan Corp[/TD]
[TD]computer screen[/TD]
[TD]5[/TD]
[TD]12000[/TD]
[TD]60000[/TD]
[TD]cash[/TD]
[/TR]
[TR]
[TD]02/08/2018[/TD]
[TD]1102[/TD]
[TD]Basit Traders[/TD]
[TD]laptop[/TD]
[TD]2[/TD]
[TD]14000[/TD]
[TD]28000[/TD]
[TD]A/Payable[/TD]
[/TR]
[TR]
[TD]02/08/2018[/TD]
[TD]1103[/TD]
[TD]Aladin Traders[/TD]
[TD]printer[/TD]
[TD]1[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[TD]A/Payable[/TD]
[/TR]
[TR]
[TD]02/08/2018[/TD]
[TD]1104[/TD]
[TD]Vass Mac[/TD]
[TD]ink remover[/TD]
[TD]4[/TD]
[TD]4000[/TD]
[TD]16000[/TD]
[TD]cash[/TD]
[/TR]
[TR]
[TD]02/08/2018[/TD]
[TD]1105[/TD]
[TD]Floppy Traders[/TD]
[TD]disk reader[/TD]
[TD]5[/TD]
[TD]8000[/TD]
[TD]40000[/TD]
[TD]cash[/TD]
[/TR]
[TR]
[TD]03/08/2018[/TD]
[TD]1107[/TD]
[TD]Basit Traders[/TD]
[TD]frank remover[/TD]
[TD]8[/TD]
[TD]9000[/TD]
[TD]72000[/TD]
[TD]cash[/TD]
[/TR]
[TR]
[TD]03/08/2018[/TD]
[TD]1112[/TD]
[TD]Floppy Traders[/TD]
[TD]fans[/TD]
[TD]3[/TD]
[TD]10000[/TD]
[TD]30000[/TD]
[TD]A/Payable[/TD]
[/TR]
[TR]
[TD]04/08/2018[/TD]
[TD]2201[/TD]
[TD]Sultan Corp[/TD]
[TD]screen 3.5 inch[/TD]
[TD]5[/TD]
[TD]8000[/TD]
[TD]40000[/TD]
[TD]cash[/TD]
[/TR]
[TR]
[TD]04/08/2018[/TD]
[TD]4401[/TD]
[TD]farooq interprises[/TD]
[TD]LED[/TD]
[TD]8[/TD]
[TD]11000[/TD]
[TD]88000[/TD]
[TD]A/Payable[/TD]
[/TR]
[TR]
[TD]04/08/2018[/TD]
[TD]5501[/TD]
[TD]Sultan Corp[/TD]
[TD]fax[/TD]
[TD]10[/TD]
[TD]5000[/TD]
[TD]50000[/TD]
[TD]A/Payable[/TD]
[/TR]
[TR]
[TD]06/08/2018[/TD]
[TD]1201[/TD]
[TD]farooq interprises[/TD]
[TD]laptop[/TD]
[TD]8[/TD]
[TD]7000[/TD]
[TD]56000[/TD]
[TD]A/Payable[/TD]
[/TR]
[TR]
[TD]06/08/2018[/TD]
[TD]1305[/TD]
[TD]Basit Traders[/TD]
[TD]screen 4.5 inc[/TD]
[TD]4[/TD]
[TD]8000[/TD]
[TD]32000[/TD]
[TD]cash[/TD]
[/TR]
[TR]
[TD]06/08/2018[/TD]
[TD]1408[/TD]
[TD]Sultan Corp[/TD]
[TD]disk 5.1[/TD]
[TD]5[/TD]
[TD]9000[/TD]
[TD]45000[/TD]
[TD]A/Payable[/TD]
[/TR]
[TR]
[TD]06/08/2018[/TD]
[TD]1509[/TD]
[TD]Basit Traders[/TD]
[TD]remover link pad[/TD]
[TD]2[/TD]
[TD]10000[/TD]
[TD]20000[/TD]
[TD]cash[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes

Book1
ABCDEFGH
1Purhas DateBill NoVandorItemQtyRateAmountTrasaction Type
22018-08-021101Sultan Corpcomputer screen51200060000cash
32018-08-021102Basit Traderslaptop21400028000A/Payable
42018-08-021103Aladin Tradersprinter150005000A/Payable
52018-08-021104Vass Macink remover4400016000cash
62018-08-021105Floppy Tradersdisk reader5800040000cash
72018-08-031107Basit Tradersfrank remover8900072000cash
82018-08-031112Floppy Tradersfans31000030000A/Payable
92018-08-042201Sultan Corpscreen 3.5 inch5800040000cash
102018-08-044401farooq interprisesLED81100088000A/Payable
112018-08-045501Sultan Corpfax10500050000A/Payable
122018-08-061201farooq interpriseslaptop8700056000A/Payable
132018-08-061305Basit Tradersscreen 4.5 inc4800032000cash
142018-08-061408Sultan Corpdisk 5.15900045000A/Payable
152018-08-061509Basit Tradersremover link pad21000020000cash
Sheet1



Book1
ABCDEFGHIJ
1dateidxPurhas DateBill NoVandorItemQtyRateAmountTrasaction Type
22018-08-0362018-08-031107Basit Tradersfrank remover8900072000cash
32018-08-0472018-08-031112Floppy Tradersfans31000030000A/Payable
4transaction type(s)82018-08-042201Sultan Corpscreen 3.5 inch5800040000cash
5cash92018-08-044401farooq interprisesLED81100088000A/Payable
6A/Payable102018-08-045501Sultan Corpfax10500050000A/Payable
7
8record count
95
10
Sheet2


In A9 just enter:

=COUNTIFS(Sheet1!$A$2:$A$15,">="&A2,Sheet1!$A$2:$A$15,"<="&A3)

In B2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$2:B2)>$A$9,"",SMALL(IF((Sheet1!$A$2:$A$15>=$A$2)*(Sheet1!$A$2:$A$15<=$A$3)*ISNUMBER(MATCH(Sheet1!$H$2:$H$15,$A$5:$A$6,0)),ROW(Sheet1!$A$2:$A$15)-ROW(Sheet1!$A$2)+1),ROWS($B$2:B2)))

In C2 just enter, copy across to J2, and copy down:

=IF($B2="","",INDEX(Sheet1!$A$2:$H$15,$B2,MATCH(C$1,Sheet1!$A$1:$H$1,0)))
 
Upvote 0
Dear Aladin,
thanks for the reply, but my question is to find all the data between two dates copy in sheet2. like below there are 5 transaction between two date 3/4/2018 to 4/8/2018, copy all 5 transaction in sheet2, it may be more than 5 transaction. actually my question is this. automatic all the data copy then paste in sheet2

[TABLE="class: cms_table, width: 596"]
<tbody>[TR]
[TD]03/08/2018[/TD]
[TD]1107[/TD]
[TD]Basit Traders[/TD]
[TD]frank remover[/TD]
[TD]8[/TD]
[TD]9000[/TD]
[TD]72000[/TD]
[TD]cash[/TD]
[/TR]
[TR]
[TD]03/08/2018[/TD]
[TD]1112[/TD]
[TD]Floppy Traders[/TD]
[TD]fans[/TD]
[TD]3[/TD]
[TD]10000[/TD]
[TD]30000[/TD]
[TD]A/Payable[/TD]
[/TR]
[TR]
[TD]04/08/2018[/TD]
[TD]2201[/TD]
[TD]Sultan Corp[/TD]
[TD]screen 3.5 inch[/TD]
[TD]5[/TD]
[TD]8000[/TD]
[TD]40000[/TD]
[TD]cash[/TD]
[/TR]
[TR]
[TD]04/08/2018[/TD]
[TD]4401[/TD]
[TD]farooq interprises[/TD]
[TD]LED[/TD]
[TD]8[/TD]
[TD]11000[/TD]
[TD]88000[/TD]
[TD]A/Payable[/TD]
[/TR]
[TR]
[TD]04/08/2018[/TD]
[TD]5501[/TD]
[TD]Sultan Corp[/TD]
[TD]fax[/TD]
[TD]10[/TD]
[TD]5000[/TD]
[TD]50000[/TD]
[TD]A/Payable


[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hey majidsiddique,

This is another approach using VBA … Try it out & let me know if it works for you

Code: 
Sub Select_Data()
Application.ScreenUpdating = False
Dim DateFrom As String, DateTo As String, Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Sheets("Sheet1") '<-- Change this to your data sheet name
Set Ws2 = Sheets("Sheet2") '<-- Change this to your destination sheet name
DateFrom = InputBox("Please enter date from here ...")
DateTo = InputBox("Please enter date to here ...")
Ws2.UsedRange.Clear
With Ws1.UsedRange
    .AutoFilter Field:=1, Criteria1:=">=" & DateFrom, Operator:=xlAnd, Criteria2:="<=" & DateTo
    .SpecialCells(xlCellTypeVisible).Copy Destination:=Ws2.Range("A1")
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
I was looking at this question also.
Try this:

Will copy rows to sheet 2
Run this script from the sheet with your data

Code: 
Sub Filter_Me_Please()
'Modified  11/17/2018  4:26:54 AM  EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim C As Long
Dim ans As Date
Dim anss As Date
ans = InputBox("Start Date")
anss = InputBox("Stop Date")
C = 1 ' Column Number Modify this to your need
Lastrow = Cells(Rows.Count, C).End(xlUp).Row
With ActiveSheet.Cells(1, C).Resize(Lastrow)
   
   .AutoFilter Field:=1, Criteria1:=">=" & CDbl(ans), Operator:=xlAnd, Criteria2:="<=" & CDbl(anss)
      counter = .Columns(C).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(2).Rows(1)
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi my answer is this,

there is run time error

.AutoFilter Field:=1, Criteria1:=">=" & CDbl(ans), Operator:=xlAnd, Criteria2:="<=" & CDbl(anss)

Run time error 1004
Auto filter method range class failed

i put date this format 3/8/2018
4/8/2018
 
Upvote 0
hi aladin,
there is another way to find out date wise data by filter.
your set of formula is super.
set heading by filter and goto purchased date filter and choose option "between".
but in this way rows are hide and my desired data get it.
i get it sooner this.
thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Create report by matching part of item and populate for each cell under item across sheets
Replies
2
Views
217
Ali M
A
A
  • Solved
transpose data int listbox on userform based on two dates or without dates
Replies
2
Views
341
Alaa mg
A
K
  • Question Question
Extract data between two sets of dates in two columns.
Replies
4
Views
1K
RasGhul
RasGhul
H
  • Question Question
Can I accurately calculate these costs in the data provided
Replies
7
Views
616
TheRobRush
T
S
  • Question Question
Date Range data retrieval
Replies
3
Views
609
Tetra201
Tetra201

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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