Transpose value into table meeting conditions from another dynamic table

[vij]

Hi,
I have data as under in a sheet. I want to capture data for any value which exceed 5 in cells ( B16:J16) and the corresponding devices in cells (B1:J1) for that value. The values in cells B16 to J16 keeps on changing daily.
The expected results which are updated daily are shared below the table.

Noida	802-1-01 NOI:FLR1 TURN3 IN	802-1-17 NOI:FIRE ALARM	802-1-26 NOI:FLR3 FIRE STR 1	802-1-27 NOI:FLR4 FIRE STR 1	802-1-33 NOI:FLR5 FIRE STR 1	802-2-00 NOI:SERVICE ELEV	802-3-00 NOI:CAFE SIDE DOOR	802-3-01 NOI:FLR1 TURN3 OUT	802-4-00 NOI:SHIPPING REAR DR
19-Jul-13	0	0	0	0	0	0	0	75	0
20-Jul-13	10	1	1	1	80	1	1	1	1
21-Jul-13	20	2	5	2	2	2	2	2	22
22-Jul-13	5	3	3	3	7	3	3	3	3
23-Jul-13	6	4	4	4	4	22	44	4	4
24-Jul-13	7	5	7	5	5	5	25	5	5
25-Jul-13	9	6	6	6	9	6	6	6	6
26-Jul-13	0	0	0	0	0	0	0	0	0
27-Jul-13	0	0	0	0	0	0	0	0	0
28-Jul-13	0	0	0	0	0	0	0	0	0
29-Jul-13	0	0	0	0	0	0	0	0	0
30-Jul-13	0	0	0	0	0	0	0	0	0
31-Jul-13	0	0	0	0	0	0	0	0	0
Total	57	21	26	21	107	39	81	96	41
Average	4	2	2	2	8	3	6	7	3
Expected result
Location	Device Name	No of Alarms
Noida	802-1-33 NOI:FLR5 FIRE STR 1	8
	802-3-00 NOI:CAFE SIDE DOOR	6
	802-3-01 NOI:FLR1 TURN3 OUT	7

<tbody>
</tbody>

Thanks,
Vijay

 

[hippiehacker]

you could ise the countif formula for the numbers of alarms? if the cells are always the same range

 

[vij]

That will not do, as the number of alarms will only be populated in the expected results table if the value exceeds 5 and the corresponding device name will also be highlighted then. If the alarms are less than 4.99 then the cells will not be populated.

 

[QU4487]

I think I've found a solution. It's stolen from a solution to a similar question on a different forum site.
[SOLVED] Extracted names from list if value greater than 2

I've assumed that your data is on sheet 2 and your summary is on sheet 1. I've also added a further cell to your summary, stating the number of devices exceeding 5 (Sheet 1, cell B1). Therefore you headings are in cells A2:C2

Under Device Name (B3) {=IF(ROWS($B$3:B3)<=B$1,INDEX(Sheet2!$B$1:$J$1,MATCH(0,IF(Sheet2!$B$16:$J$16>5,COUNTIF($B$1:B1,Sheet2!$B$1:$J$1)),0)),"")}

Under No. of Alarms (C3) {=IF(ROWS($B$3:B3)<=B$1,INDEX(Sheet2!$B$16:$J$16,MATCH(0,IF(Sheet2!$B$16:$J$16>5,COUNTIF($B$1:B1,Sheet2!$B$1:$J$1)),0)),"")}

Under Location (A3) =IF(COUNTA(B3)=1,Sheet2!$A$1,"")

You then drag down these formulas a further 8 rows. Also be aware that the first to formulas are array formulas, and as such you need to press ctrl+shift+enter to get them to work.

Hope this makes sense. If not here's a link to my worksheet.

https://www.dropbox.com/s/1n616dg4j...ng conditions from another dynamic table.xlsx

 

[vij]

Thanks QU4487. It works absolutely fine. Thanks for such a prompt reply and solution.

 

[QU4487]

Glad to help.

 

[vij]

Thanks QU4487, this works just fine. However,once I drag the formula down and across and the values of alarm is less than 5 I get a value of #N/A. Is there any way of avoiding this?

 

[QU4487]

Just realised that my formula for the location was pretty naff. Try this one instead;

=IF(B3="","",Sheet2!$A$1)

You should only be dragging the formulas down. Where do you get the #N/A appearing? I can't seem to replicate your issue.

 

[vij]

I have dragged the formula down to cover 15 rows. Therefore for all rows where the value is more than 5 the formula works and for others it becomes #N/A. In case the value for these cells is also greater than 5 then the values are automatically updated. i.e. the formula works just fine.

 

[QU4487]

I can't seem to replicate the issue. Could you post a link to an example of your worksheet?