Pulling data from a table if it meets certain criteria

[EmmaTM]

Hi,
If I have a large table of data similar to this:

Customer	AoD Net Limit	Over Limit	Over Limit Flag	Approaching Limit	Approaching Limit
Alpha	-	- 25,000.00	Ok	0.00%	Ok
Beta	21,957.00	- 128,043.00	Ok	14.64%	Ok
Charlie	23,470.50	- 9,529.50	Ok	71.12%	Warning
Delta	- 16,666.67	- 16,666.67	Ok	0.00%	Ok
Echo	-	-	Ok	0.00%	Ok
Foxtrot	-	-	Ok	0.00%	Ok
Golf	14,280.71	14,280.71	Warning	0.00%	Ok
Hotel	91,164.40	61,164.40	Warning	303.88%	Warning

On a seperate tab I only want to list out those that have warnings in the 'over limit flag' & 'approaching limit' columns, please can someone help with a formula to do this?

Thank you

 

[Burrgogi]

You'll need to clarify a couple of things:

1) Are you saying you want to display the name of the customers that have "warning" displayed in both Over Limit Flag and Approaching Limit columns? or either one of them?
2) What are the columns in question? A,B,C,D etc.

 

[AhoyNC]

Try:
Sheet 2

Book2
	A	B	C	D	E	F
1	Customer	AoD Net Limit	Over Limit	Over Limit Flag	Approaching Limit	Approaching Limit
2	Hotel	91164.4	61164.4	Warning	3.0388	Warning
Sheet2

Cell Formulas
Range	Formula
A2:F2	A2	=FILTER(Sheet1!A2:F9,(Sheet1!D2:D9="Warning")*(Sheet1!F2:F9="Warning"))
Dynamic array formulas.

Sheet 1

Book2
	A	B	C	D	E	F
1	Customer	AoD Net Limit	Over Limit	Over Limit Flag	Approaching Limit	Approaching Limit
2	Alpha	-	-25,000.00	Ok	0.00%	Ok
3	Beta	21,957.00	-128,043.00	Ok	14.64%	Ok
4	Charlie	23,470.50	-9,529.50	Ok	71.12%	Warning
5	Delta	-16,666.67	-16,666.67	Ok	0.00%	Ok
6	Echo	-	-	Ok	0.00%	Ok
7	Foxtrot	-	-	Ok	0.00%	Ok
8	Golf	14,280.71	14,280.71	Warning	0.00%	Ok
9	Hotel	91,164.40	61,164.40	Warning	303.88%	Warning
Sheet1

If you want data if warning is in either column then change formula to:
=FILTER(Sheet1!A2:F9,(Sheet1!D2:D9="Warning")+(Sheet1!F2:F9="Warning"))

 

[EmmaTM]

Hi - appologies, the columns above are A to F and I want to pull the entire row of data if either of those are flagged as a warning.
So in the example above I would want data pulled from columns A- F for rows 4, 8 & 9 to be displayed on a seperate tab.

Thank you

 

[AhoyNC]

My second formula above should work.

Sheet 2

Book2
	A	B	C	D	E	F
1	Customer	AoD Net Limit	Over Limit	Over Limit Flag	Approaching Limit	Approaching Limit
2	Charlie	23470.5	-9529.5	Ok	0.7112	Warning
3	Golf	14280.71	14280.71	Warning	0	Ok
4	Hotel	91164.4	61164.4	Warning	3.0388	Warning
Sheet2

Cell Formulas
Range	Formula
A2:F4	A2	=FILTER(Sheet1!A2:F9,(Sheet1!D2:D9="Warning")+(Sheet1!F2:F9="Warning"))
Dynamic array formulas.

Sheet 1

Book2
	A	B	C	D	E	F
1	Customer	AoD Net Limit	Over Limit	Over Limit Flag	Approaching Limit	Approaching Limit
2	Alpha	-	-25,000.00	Ok	0.00%	Ok
3	Beta	21,957.00	-128,043.00	Ok	14.64%	Ok
4	Charlie	23,470.50	-9,529.50	Ok	71.12%	Warning
5	Delta	-16,666.67	-16,666.67	Ok	0.00%	Ok
6	Echo	-	-	Ok	0.00%	Ok
7	Foxtrot	-	-	Ok	0.00%	Ok
8	Golf	14,280.71	14,280.71	Warning	0.00%	Ok
9	Hotel	91,164.40	61,164.40	Warning	303.88%	Warning
Sheet1

 

[EmmaTM]

Thank you very much

 

[AhoyNC]

You're welcome. Thanks for the feedback.