Returning several matched values in a lookup

[DavidVernon]

Firstly thanks for reading this, people here are such huge helps

Right, here goes....

I have a list of cities in column A
In column C I have a complex formula that tells me if I have a problem in that city for a given criteria, e.g.:

=IF(Plymouth!B$13=0,"no data",IF(INDEX(LINEST(OFFSET(Plymouth!B$13,1-$B$4,0,$B$4,1)),1)<(-Plymouth!B$13*$B$5),"problem","fine"))

This returns the values of 'problem', 'fine' or 'no data' and works excellently. Column C refers to a metric (call it metric A) and I have similar formulas in subsequent columns (for metrics B,C,D etc).

I am then left with a fantastic table that shows me all the cities in column A and then their status for each metric. Great. But...

I have a load of cities and a load of metrics. I would like to only show the
Cities where I have problems. I can use the autofilter and do it metric by metric, but this means selecting 'problem' in one filter box, writing down the cities, deselecting 'problem', selecting 'problem' in the next filter box etc. This is v.time consuming.

I would like to be able to interrogate my data in some way that I am left with a table that only shows me the cities that have problems.

For example, the resulting table might look like this:

MetricA MetricB MetricC etc
City1 City5 City16
City24 City15 City60
City43 City49
etc

Also, there may be no cities with problems for a particular metric.

Any suggestions?

Thanks in advance

David

 

[Aladin Akyurek]

A scaled-down sample of your (fake) data would help...

 

[jungix]

You could add another column of data to check is there is a problem for one of the metrics. Than sort according to this criteria and you will have the list of cities with problems.

Than sorting by each metric for only this small sample will be much quicker.

 

[DavidVernon]

Here is a sample of my (now real) data..

Weekly Trend Monitoring.xls
	A	B	C	D	E	F	G	H	I
7	Country	Branch	Presstogotosheet	ORDERINTAKE			PRODUCTION
8				x	y	z	a	b	Stock
9				number	m2	tonnes	number	m2	Value
10
11	Poland	Bialystok		fine	nodata	nodata	fine	nodata	nodata
12	Poland	Bydgoszcz		fine	nodata	nodata	fine	nodata	nodata
13	Poland	Wroclaw		fine	nodata	nodata	fine	nodata	nodata
14	Poland	Skierniewice		fine	problem	nodata	fine	problem	nodata
15	Poland	Krakow		problem	nodata	nodata	fine	nodata	nodata
16	Poland	Szczecin		fine	problem	nodata	fine	fine	nodata
Issue Sheet

[/img]

the sheet has many columns and rows..

thanks in advance

David

 

[Aladin Akyurek]

Here is a sample of my (now real) data..

[....]

the sheet has many columns and rows..

thanks in advance

David

Book1
	A	B	C	D
1
2	Order Intake
3	1	2	0
4	Number	m2	tonnes
5	Krakow	Skierniewice
6		Szczecin
7
Sheet2

A3, copied across:

=COUNTIF('Issue Sheet'!D$11:D$16,"problem")

A5:

=IF(ROWS(A$5:A5)<=A$3,INDEX('Issue Sheet'!$B$11:$B$16,SMALL(IF('Issue Sheet'!D$11:D$16="problem",ROW('Issue Sheet'!D$11:D$16)-ROW('Issue Sheet'!D$11)+1),ROWS(A$5:A5))),"")

which is confirmed with control+shift+enter (not just with enter) then copied across and down.

 

[DavidVernon]

Thank you so much - a much needed high to end a very dismal day...