Excel Filtering for a Summary Tab

[ItalianPlatinum]

I have a 5k+ row sheet that contains Invoices, job Ids, Merchants, with Due Dates and Shipment dates among a lot of other unique data. I need a way to quickly and easily identify based off of year when something was shipped. I want to maintain one sheet lets call it data. Then be able to look at another sheet say (2024) or (2025) to reference right there automatically what has been shipped for that year. The sheet 2024 will have all the columns in the data tab that I populated. So essentially a summary filtered by Ship date of 2024. Would anyone be able to help? The user isn't very computer inclined so having them filter or advance filtering themselves is a no can do. I know Excel has FILTER and UNIQUE filter formulas that I think may work but I had issues getting them to work so looking for some help. Thank you in adcance!

Job #	Shipment Date	Due Date	A	B	C	D	X	X	X	X
1	1/1/2023	12/2/2022	AA	12	123	1234	X	X	X	X
2	1/1/2022	12/2/2021	BB	66	55	77	X	X	X	X
3	1/1/2024	12/2/2023	CC	99	88	24	X	X	X	X
4	10/1/2024	9/1/2024	DD	856	874	314	X	X	X	X
5	10/5/2020	9/5/2020	EE	101	202	303	X	X	X	X
6	9/9/2020	8/10/2020	FF	444	555	666	X	X	X	X
7	9/1/2024	8/2/2024	GG	888	999	110	X	X	X	X
8	11/1/2024	10/2/2024	HH	5687	45874	2365	X	X	X	X

 

[ItalianPlatinum]

Could I do it via VBA? Is that sort of the only way I have then?

 

[LegendaryAxe]

Not sure if the columns will spill properly in Excel 2013, but this is a solution that uses older functions that should be available in that version (had to look at some of Mike Girvin's old videos to figure out how to do the aggregate/small/row thing):

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1													Year	Count
2													2024	4
3	Job #	Shipment Date	X	X	X	X	X	X	X	X	X
4	1	1/1/2023	X	X	X	X	X	X	X	X	X		3	1/1/2024	X	X	X	X	X	X	X	X	X
5	2	1/1/2022	X	X	X	X	X	X	X	X	X		4	10/1/2024	X	X	X	X	X	X	X	X	X
6	3	1/1/2024	X	X	X	X	X	X	X	X	X		7	9/1/2024	X	X	X	X	X	X	X	X	X
7	4	10/1/2024	X	X	X	X	X	X	X	X	X		8	11/1/2024	X	X	X	X	X	X	X	X	X
8	5	10/5/2020	X	X	X	X	X	X	X	X	X
9	6	9/9/2020	X	X	X	X	X	X	X	X	X
10	7	9/1/2024	X	X	X	X	X	X	X	X	X
11	8	11/1/2024	X	X	X	X	X	X	X	X	X
Sheet2

Cell Formulas
Range	Formula
N2	N2	=SUMPRODUCT(1*(YEAR(B4:B11)=M2))
M8:M11,M4:W7	M4	=IF(ROWS($M$4:M4)>$N$2,"",INDEX($A$4:$K$11,AGGREGATE(15,6,(ROW($A$4:$K$11)-ROW($A$4)+1)/(YEAR($B$4:$B$11)=$M$2),ROWS($M$4:M4)),0))
Dynamic array formulas.

 

[Fluff]

Not sure if the columns will spill properly in Excel 2013

Fraid not, spill ranges only appeared in 2021.

 

[LegendaryAxe]

Fraid not, spill ranges only appeared in 2021.

Hmmm. Okay, here's an even more old-fashioned way. Hopefully this works in 2013:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1													Year	Count	Columns
2													2024	4	10
3	Job #	Shipment Date	X	X	X	X	X	X	X	X	X
4	1	1/1/2023	X	X	X	X	X	X	X	X	X		3	1/1/2024	X	X	X	X	X	X	X	X	X
5	2	1/1/2022	X	X	X	X	X	X	X	X	X		4	10/1/2024	X	X	X	X	X	X	X	X	X
6	3	1/1/2024	X	X	X	X	X	X	X	X	X		7	9/1/2024	X	X	X	X	X	X	X	X	X
7	4	10/1/2024	X	X	X	X	X	X	X	X	X		8	11/1/2024	X	X	X	X	X	X	X	X	X
8	5	10/5/2020	X	X	X	X	X	X	X	X	X
9	6	9/9/2020	X	X	X	X	X	X	X	X	X
10	7	9/1/2024	X	X	X	X	X	X	X	X	X
11	8	11/1/2024	X	X	X	X	X	X	X	X	X
12
Sheet2

Cell Formulas
Range	Formula
N2	N2	=SUMPRODUCT(1*(YEAR(B4:B11)=M2))
M4:W12	M4	=IF(COLUMNS(M$4:M4)>$O$2,"",IF(ROWS($M$4:M4)>$N$2,"",INDEX($A$4:$K$11,AGGREGATE(15,6,(ROW($A$4:$K$11)-ROW($A$4)+1)/(YEAR($B$4:$B$11)=$M$2),ROWS($M$4:M4)),COLUMNS($M4:M4))))

 

[ItalianPlatinum]

Ok so this would require the user to drag down the formula based off the criteria that could fit? Guess I’ll just drag it down a large amount and pw protect it. I’ll test this out this week. Thanks for the help

 

[LegendaryAxe]

Ok so this would require the user to drag down the formula based off the criteria that could fit? Guess I’ll just drag it down a large amount and pw protect it. I’ll test this out this week. Thanks for the help

No problem!

Yes, you'd have to drag the formula down (in my example) to row 12, then to column W. Hope it works.

 

[Cubist]

Could I do it via VBA? Is that sort of the only way I have then?

VBA is probably the optimal solution given your history with large data sets.

 

[LegendaryAxe]

I forgot we were dealing with 5k+ rows of data. I have a feeling the formula I posted could be really slow if you're dealing with large data, so, yes, you might want to consider VBA instead.

 

[ItalianPlatinum]

Yeah that is what I was sort of alluding to wasn’t sure if it would bog it down I haven’t tried yet I won’t have access to the workstation until mid week anyways

 

[Cubist]

Here's a quick and dirty script. This filter the data on Sheet1 based on the year in M2. If there are rows that meet the criteria, it creates a new sheet and paste the data in A1.

Sub FilterByYear()
    Dim filterYear As Long
    Dim ws As Worksheet, newSheet As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    filterYear = ws.Range("M2").Value
    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
    
    ws.Range("A3:K" & lastRow).AutoFilter Field:=2, _
        Criteria1:=">=" & DateSerial(filterYear, 1, 1), _
        Operator:=xlAnd, _
        Criteria2:="<=" & DateSerial(filterYear, 12, 31)
    
    ' Check if there are visible rows
    On Error Resume Next
    Set rng = ws.Range("A4:K" & lastRow).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If Not rng Is Nothing Then
        ' Create a new sheet
        Set newSheet = ThisWorkbook.Sheets.Add
        newSheet.Name = "FilteredData_" & filterYear
        
        ' Copy filtered cells to the new sheet
        ws.Rows(3).Copy Destination:=newSheet.Range("A1") ' Copy header
        rng.Copy Destination:=newSheet.Range("A2")        ' Copy filtered data
    Else
        MsgBox "No data found for the specified year.", vbExclamation
    End If
    
    ws.AutoFilterMode = False
End Sub