Extracting row & column data based on variable criteria

[Theomo]

I haven't a clue as to how to attempt this.

I have a sheet with data going down rows (customers) and across columns (monthly dates). Each cell is populated with sales for that particular customer and month.

I need to be able to (on another sheet or workbook preferably) "extract" those rows and columns based on 2 input cells by the user - start month and end month.

E.G. I have 12 columns Jan 05 - Dec 05 and 10 rows (Customer 1 - 10). If the user puts in June 05 - Oct 05, I only want to display those columns (all rows) on a new sheet. If the input chages to May 05 - July 05, I only want to display those columns.

Any ideas?

Thanks....

 

[just_jon]

Welcome to MrExcel - In which 2 cells will the criteria be given, or should it be prompted for?

 

[Theomo]

Any cells... user input... one cell will be labeled: Start date, the other End Date. Prompting isn't necessary

I am not concerned about getting bogus entries in those cells (like a number or end date being prior to start date, etc.)

 

[just_jon]

Any cells... user input... one cell will be labeled: Start date, the other End Date. Prompting isn't necessary

I am not concerned about getting bogus entries in those cells (like a number or end date being prior to start date, etc.)

Which cells exactly, though -- A1/A2?

And when you give it start/stop timeframes I'm assuming they will be matches to the column headings i your data table. [ You can use the column headers as data validation to the input cells. ]

 

[Aladin Akyurek]

Care to provide a small sample?

 

[Theomo]

example

Okay....here we go datasheet:

Jan 05 Feb 05 Mar 05 Apr 05
Cust 1 10 20 5 6
Cust 2 20 5 6 10
Cust 3 5 6 10 20
Cust 4 6 10 20 5

Begin Date: Feb 05
End Date Mar 05

Output would be:
Feb 05 Mar 05
Cust 1 20 5
Cust 2 5 6
Cust 3 6 10
Cust 4 10 20

If you change Begin Date and End Date, the output would change also.

Hope that helps...

 

[Aladin Akyurek]

Re: example

Okay....here we go datasheet:

Jan 05 Feb 05 Mar 05 Apr 05
Cust 1 10 20 5 6
Cust 2 20 5 6 10
Cust 3 5 6 10 20
Cust 4 6 10 20 5

Begin Date: Feb 05
End Date Mar 05

Output would be:
Feb 05 Mar 05
Cust 1 20 5
Cust 2 5 6
Cust 3 6 10
Cust 4 10 20

If you change Begin Date and End Date, the output would change also.

Hope that helps...

Source (Data sheet)

Book1
	A	B	C	D	E
1	Cust	Jan-05	Feb-05	Mar-05	Apr-05
2	Cust-1	10	20	5	6
3	Cust-2	20	5	6	10
4	Cust-3	5	6	10	20
5	Cust-4	6	10	20	5
Source

Make sure that B1:E1 consists of the first day dates like this: 1-Jan-05, 1-Feb-05, 1-Mar-05, etc., custom formatted as mmm-yy.

Destination (Output)

Book1
	A	B	C	D
1	Begin Date	Feb-05
2	End Date	Mar-05
3	4	1
4	Cust	Feb-05	Mar-05
5	Cust-1	20	5
6	Cust-2	5	6
7	Cust-3	6	10
8	Cust-4	10	20
9
Destination

B1:B2 are used to specify start and end months of interest. Would be better to offer dropdown lists in these cells by means of data validation based on the date headers of the Source sheet.

A3:

=MATCH(REPT("z",255),Source!A2:A65536)

B3:

=(B2>=B1)+0

A5, copied across then down:

=IF($B$3*(A$4<>""),IF(ROW()-ROW(A$5)+1<=$A$3,INDEX(Source!$A$2:$E$5,ROW()-ROW(A$5)+1,MATCH(A$4,Source!$A$1:$E$1,0)),""),"")

 

[just_jon]

And a macro solution to go with A.A.'s fine formula set -

Data

Book1
	A	B	C	D	E	F
1		Jan-05	Feb-05	Mar-05	Apr-05	May-05
2	Cust 1	10	20	5	6
3	Cust 2	20	5	6	10
4	Cust 3	5	6	10	20
5	Cust 4	6	10	20	5
6
Sheet1

You input -

Book1
	A	B	C	D	E
1	Start Dt=	Feb-05	End Dt=	Mar-05	Run Now
2
Sheet2

After clicking on cell E1, you get -

Book1
	A	B	C	D	E
1	Start Dt=	Feb-05	End Dt=	Mar-05	Run Now
2		Feb-05	Mar-05
3	Cust 1	20	5
4	Cust 2	5	6
5	Cust 3	6	10
6	Cust 4	10	20
Sheet2

Code --<font face=Courier New><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target<SPAN style="color:#00007F">As</SPAN> Range)<SPAN style="color:#00007F">If</SPAN> Intersect(Target, [E1])<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN><SPAN style="color:#007F00">' execute only on E1</SPAN>
Application.EnableEvents =<SPAN style="color:#00007F">False</SPAN>
Application.ScreenUpdating =<SPAN style="color:#00007F">False</SPAN><SPAN style="color:#00007F">Dim</SPAN> i<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>, j<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>, k<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><SPAN style="color:#00007F">With</SPAN> Application.WorksheetFunction<SPAN style="color:#007F00">' find the columns of interest in the data table</SPAN>
    Range("A2:M" & .Max(2, Cells(Rows.Count, "A").End(xlUp).Row)).Clear<SPAN style="color:#007F00">' get rid of prior data</SPAN>
    i = .Match(CLng([B1].Value), Sheets("Sheet1").Range("A1:M1"), 0)<SPAN style="color:#007F00">' find 1st column</SPAN>
    j = .Match(CLng([D1].Value), Sheets("Sheet1").Range("A1:M1"))<SPAN style="color:#007F00">' find ending column</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
k = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row<SPAN style="color:#007F00">' end of data down in table</SPAN><SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")
    .Range("A1:A" & k).Copy Range("A2")<SPAN style="color:#007F00">' copy the customers over</SPAN>
    .Range(.Cells(1, i), .Cells(k, j)).Copy Range("B2")<SPAN style="color:#007F00">' and the months</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
Application.CutCopyMode =<SPAN style="color:#00007F">False</SPAN><SPAN style="color:#007F00">' get rid of marching ants</SPAN>
Application.Goto [B1]<SPAN style="color:#007F00">' return to cell</SPAN>
Application.ScreenUpdating =<SPAN style="color:#00007F">True</SPAN>
Application.EnableEvents =<SPAN style="color:#00007F">True</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>