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

 

[vij]

This is the data I am getting for 3 different sets of data for Noida Sec 25A, Sec 127, and Bangalore.
I have the following observations:
1. If the formula is copied across say 7 rows and the value greater than 5 is true for 4 rows then 3 rows have #N/A.
2. Duplicate Data is being posted in cells.

I am enclosing the formula dragged across the cells having duplicate data as an example.

Sector 25A
1. =IF(ROWS(C20:C20)<=C$18,INDEX('DeviceAlarms'!$C$71:$AZ$71,MATCH(0,IF('DeviceAlarms'!$C$103:$AZ$103>5,COUNTIF(KPI_Achieved!$C$18:C18,'Device Alarms'!$C$71:$AZ$71)),0)),"")
2. =IF(ROWS(C21:C21)<=C$18,INDEX('Device Alarms'!$C$71:$AZ$71,MATCH(0,IF('Device Alarms'!$C$103:$AZ$103>5,COUNTIF(KPI_Achieved!$C$18:C19,'Device Alarms'!$C$71:$AZ$71)),0)),"")

Sector 127
1. =IF(ROWS(G20:G20)<=G$18,INDEX('Device Alarms'!$C$36:$W$36,MATCH(0,IF('Device Alarms'!$C$68:$W$68>5,COUNTIF($G$18:G18,DeviceAlarms!$C$36:$W$36)),0)),"")
2. =IF(ROWS(G21:G21)<=G$18,INDEX('Device Alarms'!$C$36:$W$36,MATCH(0,IF('Device Alarms'!$C$68:$W$68>5,COUNTIF(KPI_Achieved!$G$18:G19,DeviceAlarms!$C$36:$W$36)),0)),"")

Same is the case for Bangalore.

		number of devices exceeding 5				number of devices exceeding 5				number of devices exceeding 5
	Location	Device Name	No of Alarms		Location	Device Name	No of Alarms		Location	Device Name	No of Alarms
	Noida Sec 25A	802-5-01 NOI:TURN LANE 1 IN	7		Sector 127	821-1-01 NOI:FL8 MAIN RECP EXIT	7		Bangalore	822-4-01 BNG:GF REAR WING IDC	6.7
	Noida Sec 25A	802-5-01 NOI:TURN LANE 1 IN	7		Sector 127	821-1-01 NOI:FL8 MAIN RECP EXIT	7		Bangalore	822-4-01 BNG:GF REAR WING IDC	6.7
	Noida Sec 25A	802-6-00 NOI:TURN LANE 2 OUT	10		Sector 127	821-1-01 NOI:FL8 MAIN RECP EXIT	10		Bangalore	#N/A	#N/A
	Noida Sec 25A	802-6-00 NOI:TURN LANE 2 OUT	10
	Noida Sec 25A	#N/A	#N/A
	Noida Sec 25A	#N/A	#N/A

<colgroup><col span="2"><col><col><col><col span="2"><col span="4"><col></colgroup><tbody>
</tbody>

 

[QU4487]

Stupid question. Have you remembered to press ctrl+shift+enter. I forget sometimes, and then #N/A sometimes shows for me too.

 

[vij]

I did press ctrk+shift+enter and dragged the formula across 10 rows (A2:C10)
1.What I noticed is in case the value is less than 5 then I am getting #N/A
2. In case the value exceeds 5 then two rows i.e. say (A4:c4) and A5:C5) get populated with the same data.
Just to elucidate:

Number of Devices Exceeding 5 Alarms
Location	Device Name	No of Alarms
Noida Sec 25A	802-1-01 NOI:FLR1 TURN3 IN	5
Noida Sec 25A	802-1-01 NOI:FLR1 TURN3 IN	5
Noida Sec 25A	802-2-00 NOI:SERVICE ELEV	6
Noida Sec 25A	802-2-00 NOI:SERVICE ELEV	6
Noida Sec 25A	#N/A	#N/A

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Please help.

 

[vij]

Hi, Going through your sheet I noticed that the formula in cell B3 is “=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)),"")”

<tbody>
</tbody>

And in cell B4 is
“=IF(ROWS($B$3:B4)<=B$1,INDEX(Sheet2!$B$1:$J$1,MATCH(0,IF(Sheet2!$B$16:$J$16>5,COUNTIF($B$1:B3,Sheet2!$B$1:$J$1)),0)),"")”
I presume that the formula in B3 has been dragged to cell B4.
My query is how has “COUNTIF($B$1:B1,Sheet2!$B$1:$J$1)),0)),"")”
Changed to “COUNTIF($B$1:B3,Sheet2!$B$1:$J$1)),0)),"")”
That is $B$!:B1 become $B$1:B3 ????
Request make me follow the logic please.
Thanks,
Vij

 

[QU4487]

Sorry for the slow reply. Was stuck in a meeting all day.

Just gone back over my work sheet. You'll be hard pressed to follow the logic. The formula doesn't quite work.

As I originally said, I'd borrowed from somebody else's solution. There solution didn't have the heading (my row 2) I'd tried to hash round the issue, but seemed to have messed it up. If you take out the headings and use the following formulas it seems to work fine;

Location =IF(B2="","",Sheet2!$A$1)
Device name {=IF(ROWS($B$2:B2)<=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)),"")}
No. of Alarms {=IF(ROWS($B$2:B2)<=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)),"")}

However I'm now at a loss on how to put the headings back in.

 

[vij]

Will try and get back shortly. Thanks for the effort.

 

[vij]

HI QU4487,

Refer #11 above.
I was able to solve the problem by dragging the formula in the second row down and across.
Thanks for all your help. It was a pleasure interacting with you and getting a solution. Much appreciated.
Thanks,
Vij