Extract data from one table to another based on criteria

[Gwill1983]

Hi all

I am attempting to extract data from a large table into a smaller table depending on a couple of criteria (referencing other cells)

I have a report date (cell B1) and a hold type (cell J1). My raw data table is in a seperate tab named 'data' with the columns as below:

Event No

Cust

EG

EP

OT

Event Desc

WH

Order Date

Req Date

Est Desp

PO Worst Case

#

St

Value

Alloc Value

Alloc%

OS Invoice

#No PO

B2B

Hold

Wait Reason

Report Date

Previous Report Date

Lookup ref1

Lookup ref2

Previous Report Wait Reason

PO Status Value

What I am aiming to do is to have a new table in my summary sheet which pulls ALL results from the raw data table as long as the report date in B1 matches the report date in column V of the raw data, and the hold type in J1 matches the hold type in column U (wait reason). The amount of returned rows will vary so I am struggling a little with how to ensure that all are included.

I hope that makes sense.......

Happy to provide more information if required

 

[Fluff]

How about

=FILTER(A1:AA1000,(V1:V1000=B1)*(U1:U1000=J1))

 

[Gwill1983]

Hi.

That worked, but returned all columns! Do you know of a way of specifying which columns to return?

 

[Fluff]

Do you have headers where you want the data that exactly match the headers on the data sheet?
If so what are they & where are they?

 

[Gwill1983]

Yes,
my original table is named RawData and the new table will be going onto summary sheet. I currently am trying to make FILTER and CHOOSE work with the formula below:
=FILTER(CHOOSE({1,2,3,4,5,6,7},RawData[Event No],RawData[Cust],RawData[EG],RawData[OS Invoice],RawData[Hold]),(RawData[Report Date]='Summary Sheet'!B1*RawData[Wait Reason]=SummarySheet!J1))

This is returning #REF so clearly something isn't quite right.

The columns that I would like to return are:
Event No
Cust
EG
OS Invoice
Hold
Report Date
Wait Reason

 

[Fluff]

As long as the summary headers are in the same order as the table, you can do it like this

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1		15/12/2021								15/12/2021
2	Event No	Cust	EG	EP	OT	Event Desc	WH	Order Date	Req Date	Est Desp	PO Worst Case	#	St	Value	Alloc Value	Alloc%	OS Invoice	#No PO	B2B	Hold	Wait Reason	Report Date	Previous Report Date	Lookup ref1	Lookup ref2	Previous Report Wait Reason	PO Status Value
3	a	b	c	d	e	f	g	h	i	j	k	l	m	n	o	p	q	r	s	t	15/12/2021	15/12/2021	w	x	y	z	aa
4
5
6
7	Event No	Cust	EG	OS Invoice	Hold	Wait Reason	Report Date
8	a	b	c	q	t	15/12/2021	15/12/2021
9
Main

Cell Formulas
Range	Formula
A8:G8	A8	=FILTER(FILTER(Table2,(Table2[Report Date]=B1)*(Table2[Wait Reason]=J1)),ISNUMBER(MATCH(Table2[#Headers],A7:G7,0)))
Dynamic array formulas.

 

[Gwill1983]

Brilliant! Worked perfectly!

Thanks for your help

 

[Fluff]

You're welcome & thanks for the feedback.