Extract of data based on specific criteria

[Manexcel]

I have a macro enabled workbook. The main worksheet, Year 2019, is made up of rows for the complete year.

01Jan19
to 31Jan19
.
.
01Dec19
to 31Dec19


Each day of month e.g. 01Jan19, may have one or more rows. The format of this date field is nnmmmnn

i wish extract all rows for a given month to another workbook eg jan or feb or dec. so if there are 31 rows for January I end up with 31 rows in the other workbook and only the selected columns from the year 2019 worksheet

and these rows to be updated automatically when the main worksheet, year 2019, is updated.


The main column headings are ;

start date - subject - who booked - start time - end time - description

These key columns are either text or date or time formatted.

can anyone assist in providing a formula to achieve this.

thank you for your consideration.

 

[RasGhul]

Could you post some sample data?

 

[Manexcel]

As requested below is sample data. Thank you for your time

Master sheet - Year 2019

Date	Day	No in Mth	MeetingFunction	No.	Func	Mbrs.	Fee	Start Time	End Time
01Jan19	Tue	1	Name 1	nnnn	BH	1	£1.00	00:01	23:59
01Jan19	Tue	1	Name 2	nnnn	BH	2	£2.00	00:01	23:59
01Jan19	Tue	1	Name 3	nnnn	BH	3	£3.00	00:01	23:59
01Jan19	Tue	1	Name 4	nnnn	BH	4	£4.00	00:01	23:59
01Jan19	Tue	1	Name 5	nnnn	BH	5	£5.00	00:01	23:59
01Jan19	Tue	1	Name 6	nnnn	BH	6	£6.00	00:01	23:59
02Jan19	Wed	1	Name 7	nnnn	TSaUCLSA	7	£7.00	00:01	23:59
02Jan19	Wed	1	Name 8	nnnn	TSaUCLSA	8	£8.00	00:01	23:59

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

to 31Dec19

Monthly sheet for Jan

Subject	start Date	Start Time	End Date	End Time	All day event	Description
Col D from 'master workbook' sheet Year 2019	Col A 'master workbook' sheet Year 2019	Col I from 'master workbook' sheet Year 2019	Col A from 'master workbook' sheet Year 2019	Col J from 'master workbook' sheet Year 2019	not used	Col S from 'master workbook' sheet Year 2019
Name 1	01Jan19	00:01	01Jan19	23:59		Function: BH, Mem.: , Hire Charge: £, Secretary: , Contact 1: , Contact 2: , Email: , Notations: IF APL: (Approx. Attend: , Meet Pur.: , Spec. Req.: , Notes: )
Name 2	01Jan19	00:01	01Jan19	23:59		Function: BH, Mem.: , Hire Charge: £, Secretary: , Contact 1: , Contact 2: , Email: , Notations: IF APL: (Approx. Attend: , Meet Pur.: , Spec. Req.: , Notes: )
Name 3	01Jan19	00:01	01Jan19	23:59		Function: BH, Mem.: , Hire Charge: £, Secretary: , Contact 1: , Contact 2: , Email: , Notations: IF APL: (Approx. Attend: , Meet Pur.: , Spec. Req.: , Notes: )
Name 4	01Jan19	00:01	01Jan19	23:59		Function: BH, Mem.: , Hire Charge: £, Secretary: , Contact 1: , Contact 2: , Email: , Notations: IF APL: (Approx. Attend: , Meet Pur.: , Spec. Req.: , Notes: )
Name 5	01Jan19	00:01	01Jan19	23:59		Function: BH, Mem.: , Hire Charge: £, Secretary: , Contact 1: , Contact 2: , Email: , Notations: IF APL: (Approx. Attend: , Meet Pur.: , Spec. Req.: , Notes: )
Name 6	01Jan19	00:01	01Jan19	23:59		Function: BH, Mem.: , Hire Charge: £, Secretary: , Contact 1: , Contact 2: , Email: , Notations: IF APL: (Approx. Attend: , Meet Pur.: , Spec. Req.: , Notes: )
Name 7	02Jan19	00:01	02Jan19	23:59		Function: BH, Mem.: , Hire Charge: £, Secretary: , Contact 1: , Contact 2: , Email: , Notations: IF APL: (Approx. Attend: , Meet Pur.: , Spec. Req.: , Notes: )

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[RasGhul]

Hi Man,


This is working on my mock-up Workbooks, note that because we're working with a formula to index values from another workbook the Master workbook must be open for the formula to extract, otherwise you will get a Update Links prompt from excel. Use cell A2 (Jan Sheet) to enter in the start date for the month of each sheet you need to build.

Change the Sheet and Workbook names to suit your actual files and you'll need to increase this range to suit how many rows your Master sheet has or will be;

=IF(ROWS($A$5:A5)>COUNTIFS('[Master 2019.xlsx]2019'!$A$2:$A$100,">="&$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$100,"<"&$B$2),"",INDEX('[Master 2019.xlsx]2019'!$D$2:$D$9,SMALL(IF(AND('[Master 2019.xlsx]2019'!$A$2:$A$9>=$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$9<$B$2),ROW('[Master 2019.xlsx]2019'!$A$2:$A$9)-ROW('[Master 2019.xlsx]2019'!$A$2)+1),ROWS($A$5:A5))))

Book1
	A	B	C	D	E	F
1	1st of Month	End Of Month
2	01-01-19	31-01-19
3
4	Subject	Start Date	Start Time	End Date	End Time	Description
5	Name 1	01-01-19	0:01	01-01-19	23:59	Function: BH, Mem.: ,
6	Name 2	01-01-19	0:01	01-01-19	23:59	Function: BH, Mem.: ,
7	Name 3	01-01-19	0:01	01-01-19	23:59	Function: BH, Mem.: ,
8	Name 4	01-01-19	0:01	01-01-19	23:59	Function: BH, Mem.: ,
9	Name 5	01-01-19	0:01	01-01-19	23:59	Function: BH, Mem.: ,
10	Name 6	01-01-19	0:01	01-01-19	23:59	Function: BH, Mem.: ,
11	Name 7	02-01-19	0:01	02-01-19	23:59	Function: BH, Mem.: ,
12	Name 8	02-01-19	0:01	02-01-19	23:59	Function: BH, Mem.: ,
Sheet1

Cell Formulas
Range	Formula
B2	=EOMONTH(A2,0)
B5	{=IF(ROWS($A$5:A5)>COUNTIFS('[Master 2019.xlsx]2019'!$A$2:$A$100,">="&$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$100,"<"&$B$2),"",INDEX('[Master 2019.xlsx]2019'!$A$2:$A$9,SMALL(IF(AND('[Master 2019.xlsx]2019'!$A$2:$A$9>=$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$9<$B$2),ROW('[Master 2019.xlsx]2019'!$A$2:$A$9)-ROW('[Master 2019.xlsx]2019'!$A$2)+1),ROWS($A$5:A5))))}
A5	{=IF(ROWS($A$5:A5)>COUNTIFS('[Master 2019.xlsx]2019'!$A$2:$A$100,">="&$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$100,"<"&$B$2),"",INDEX('[Master 2019.xlsx]2019'!$D$2:$D$9,SMALL(IF(AND('[Master 2019.xlsx]2019'!$A$2:$A$9>=$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$9<$B$2),ROW('[Master 2019.xlsx]2019'!$A$2:$A$9)-ROW('[Master 2019.xlsx]2019'!$A$2)+1),ROWS($A$5:A5))))}
C5	{=IF(ROWS($A$5:A5)>COUNTIFS('[Master 2019.xlsx]2019'!$A$2:$A$100,">="&$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$100,"<"&$B$2),"",INDEX('[Master 2019.xlsx]2019'!$I$2:$I$9,SMALL(IF(AND('[Master 2019.xlsx]2019'!$A$2:$A$9>=$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$9<$B$2),ROW('[Master 2019.xlsx]2019'!$A$2:$A$9)-ROW('[Master 2019.xlsx]2019'!$A$2)+1),ROWS($A$5:A5))))}
D5	{=IF(ROWS($A$5:A5)>COUNTIFS('[Master 2019.xlsx]2019'!$A$2:$A$100,">="&$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$100,"<"&$B$2),"",INDEX('[Master 2019.xlsx]2019'!$A$2:$A$9,SMALL(IF(AND('[Master 2019.xlsx]2019'!$A$2:$A$9>=$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$9<$B$2),ROW('[Master 2019.xlsx]2019'!$A$2:$A$9)-ROW('[Master 2019.xlsx]2019'!$A$2)+1),ROWS($A$5:A5))))}
E5	{=IF(ROWS($A$5:A5)>COUNTIFS('[Master 2019.xlsx]2019'!$A$2:$A$100,">="&$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$100,"<"&$B$2),"",INDEX('[Master 2019.xlsx]2019'!$J$2:$J$9,SMALL(IF(AND('[Master 2019.xlsx]2019'!$A$2:$A$9>=$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$9<$B$2),ROW('[Master 2019.xlsx]2019'!$A$2:$A$9)-ROW('[Master 2019.xlsx]2019'!$A$2)+1),ROWS($A$5:A5))))}
F5	{=IF(ROWS($A$5:A5)>COUNTIFS('[Master 2019.xlsx]2019'!$A$2:$A$100,">="&$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$100,"<"&$B$2),"",INDEX('[Master 2019.xlsx]2019'!$S$2:$S$9,SMALL(IF(AND('[Master 2019.xlsx]2019'!$A$2:$A$9>=$A$2,'[Master 2019.xlsx]2019'!$A$2:$A$9<$B$2),ROW('[Master 2019.xlsx]2019'!$A$2:$A$9)-ROW('[Master 2019.xlsx]2019'!$A$2)+1),ROWS($A$5:A5))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RasGhul]

Note that I dragged the formula down 40 Rows on the month sheet to make sure all the rows of data are covered.

Here is a copy of my mockup sheet for reference;

https://www.dropbox.com/s/tosvtn4qqytc4p5/Jan 2019.xlsx?dl=0