extract data from spreadsheet

Question

I have an Worksheet that contains 20 columns, 14 of which are contain dates. I want to extract all data (entire rows) to another tab in the same workbook that match a date range in any of the 14 columns that contain dates.

Answer 1

Hi,

Load the data in the Query Editor, right click on the first 3 columns and select "Unpivot Other columns". Click on Close and Load. Now you should be able to filter easily.

Answer 2

Here is some data. These dates are Calculated with a formula (add 6months to previous date). On a summary page I have a begin date (B1) and End date (D1). I am trying to get the entire row to go to the Summary page if any of the dates fall within the date range.

AO	CSJ	Status	Letting Date	Time Begin	Work Begin	1st Audit <br>due	2nd Audit <br>due	3rd Audit <br>due	4th Audit <br>due	5th Audit due	6th Audit <br>due	7th Audit <br>due	8th Audit <br>due	9th Audit <br>due	10th Audit <br>due	11th Audit <br>due	12th Audit <br>due	13th Audit <br>due	14th Audit <br>due
NTCAO	000813221	Active	12/5/2017	6/6/2018	7/23/2018	12/6/2018	6/6/2019	12/6/2019	6/6/2020	12/6/2020	6/6/2021	12/6/2021	6/6/2022	12/6/2022	6/6/2023	12/6/2023	6/6/2024	12/6/2024	6/6/2025
NTCAO	000813248	Active	5/4/2023	11/16/2023	12/7/2023		11/16/2024	5/16/2025	11/16/2025	5/16/2026	11/16/2026	5/16/2027	11/16/2027	5/16/2028	11/16/2028	5/16/2029	11/16/2029	5/16/2030	11/16/2030
NTCAO	000814139	NotActive	6/5/2024	8/17/2024		2/17/2025	8/17/2025	2/17/2026	8/17/2026	2/17/2027	8/17/2027	2/17/2028	8/17/2028	2/17/2029	8/17/2029	2/17/2030	8/17/2030	2/17/2031	8/17/2031
NTCAO	001310091	Active	9/7/2023	11/23/2023	3/4/2024	5/23/2024	11/23/2024	5/23/2025	11/23/2025	5/23/2026	11/23/2026	5/23/2027	11/23/2027	5/23/2028	11/23/2028	5/23/2029	11/23/2029	5/23/2030	11/23/2030

Answer 3

Hi,

try this method

( I assume that in the 1st row there are headings )

'=======================

if you are not familiar with macros,

step1

Save  your Workbook with extension .xlsm (macros enabled workbook)

Step2

2a) press ALT+F11 to open Visual Basic

2b) from the ribbon, select: Insert > Module and paste the code below on the right 

pic

.

2c) Press ALT+Q to Close Visual Basic

Step3

To run the macro, press ALT+F8, 

select '**Macro1'**from the list and click the run button.

or

add a button and assign the  vba macro

'======================

[Update-2]

Option Explicit 'START VBA

Dim ws1, ws2, ddate, rFind, nRow, nCol, x, t, fg

'

Sub Macro1()

'## 11-08-2024 ##

fg = False

ddate = DateSerial**(2024, 10, 1) '<< search for date: Oct 1st 2024**

Set ws1 = Sheets**("Sheet1")'<< sheet name, change as needed**

nRow = ws1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

nCol = ws1.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column

Application.ScreenUpdating = False

Set ws2 = Sheets.Add

ws1.Cells(1, 1).Resize(, nCol).Copy ws2.Cells(1, 1)

t = 2

For x = 2 To nRow

Set rFind = ws1.Rows(x).Find(ddate)

If rFind Is Nothing Then

'nothing

Else

ws1.Cells(x, 1).Resize(, nCol).Copy ws2.Cells(t, 1)

t = t + 1

End If

Next x

If ws2.Cells(2, 1) = "" Then

fg = True

Application.DisplayAlerts = False

ws2.Delete

Application.DisplayAlerts = True

End If

If fg = True Then

MsgBox "nothing found"

Else

ws2.UsedRange.EntireColumn.AutoFit

End If

Application.ScreenUpdating = True

End Sub 'END VBA

Answer 4

Hi,

Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.

Answer 5

Not a lot of detail, but if your 20 columns are A:T on a sheet named "Data Sheet", and your date range is defined on the other sheet by cells B2 (starting date) and B3 (ending date), then use

=LET(d,'Data sheet'!A:T,FILTER(d,BYROW(d,LAMBDA(x,AND(MIN(x)<=B3,MAX(x)>=B2)))))