Filter query

[tuggers1975]

Hi all

I have a large amount of data to sort through
I need to pull the contents of a non adjacent cells into a separate sheet based on the date matching the date shown in a specific cell.

Example :
Data sheet A1:R5000
Dates in column D

Report sheet has specific date in A1

If this date matches date in data sheet column D
Enter all instances in report sheet, copying cell from column F and date into separate columns without blanks

 

[MountainFog]

Can you share sample data, include what the desired output would be?

 

[Ludwig]

If the data is defined in an excel table, then this might be what you're looking for -

FILTER( tablename[[FirstColName]:[LastColName]], tablename[Date] = A1 , "no data" )

If you're not using an Excel table then try -

FILTER( A1:R5000 , D1:D5000 = A1 , "no data" )

BUT ... you seem to be saying that the date is the value in the first cell of the first column of data. This seems to clash with the fact that your data starts in the same cell ?? I wonder if the date value in A1 you refer to, and hence the FILTER formula you are looking for, are on a separate worksheet in the workbook?
Sample data would help clarify this.

 

[Fluff]

@Ludwig the OP's profile clearly shows 2013 & 2010, therefore does not have functions such as Filter.

 

[Ludwig]

Apologies Fluff (& tuggers1975), I missed that. I should remember to check that field!

I haven't used Excel 2010 for many years now but here's an option.

A manual & messy way to achieve this would be to turn on & use a filter on the columns, manually selecting the date value required each time for column D. Then manually select all the data and copy it to another sheet.

• PS: As well as addressing the date selection field being A1 and also part of the "data" itself, you would also need to insert a line above the data as a "heading" row to avoid having the first data row used as the column headers.

A VBA macro could also be written which would get invoked each time the value in A1 changed, but for me I avoid using VBA if at all possible, and would leave that for someone else to write.