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.

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

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.

[TABLE="width: 851"]
<colgroup><col span="2"><col><col><col><col span="2"><col span="4"><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD]number of devices exceeding 5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]number of devices exceeding 5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]number of devices exceeding 5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Location[/TD]
[TD]Device Name[/TD]
[TD]No of Alarms[/TD]
[TD] [/TD]
[TD]Location[/TD]
[TD]Device Name[/TD]
[TD]No of Alarms[/TD]
[TD] [/TD]
[TD]Location[/TD]
[TD]Device Name[/TD]
[TD]No of Alarms[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Noida Sec 25A[/TD]
[TD]802-5-01 NOI:TURN LANE 1 IN[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]Sector 127[/TD]
[TD]821-1-01 NOI:FL8 MAIN RECP EXIT[/TD]
[TD="align: right"]7[/TD]
[TD] [/TD]
[TD]Bangalore[/TD]
[TD]822-4-01 BNG:GF REAR WING IDC[/TD]
[TD="align: right"]6.7[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Noida Sec 25A[/TD]
[TD]802-5-01 NOI:TURN LANE 1 IN[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]Sector 127[/TD]
[TD]821-1-01 NOI:FL8 MAIN RECP EXIT[/TD]
[TD="align: right"]7[/TD]
[TD] [/TD]
[TD]Bangalore[/TD]
[TD]822-4-01 BNG:GF REAR WING IDC[/TD]
[TD="align: right"]6.7[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Noida Sec 25A[/TD]
[TD]802-6-00 NOI:TURN LANE 2 OUT[/TD]
[TD]10[/TD]
[TD] [/TD]
[TD]Sector 127[/TD]
[TD]821-1-01 NOI:FL8 MAIN RECP EXIT[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD]Bangalore[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Noida Sec 25A[/TD]
[TD]802-6-00 NOI:TURN LANE 2 OUT[/TD]
[TD]10[/TD]
[TD] [/TD]
[TD]
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Noida Sec 25A[/TD]
[TD="align: center"]#N/A[/TD]
[TD]#N/A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Noida Sec 25A[/TD]
[TD="align: center"]#N/A[/TD]
[TD]#N/A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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:
[TABLE="width: 607"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]Number of Devices Exceeding 5 Alarms[/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD]Device Name[/TD]
[TD]No of Alarms[/TD]
[/TR]
[TR]
[TD]Noida Sec 25A[/TD]
[TD]802-1-01 NOI:FLR1 TURN3 IN[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Noida Sec 25A[/TD]
[TD]802-1-01 NOI:FLR1 TURN3 IN[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Noida Sec 25A[/TD]
[TD]802-2-00 NOI:SERVICE ELEV[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Noida Sec 25A[/TD]
[TD]802-2-00 NOI:SERVICE ELEV[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Noida Sec 25A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

Please help.

 

[vij]

[TABLE="width: 626"]
<tbody>[TR]
[TD]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)),"")”
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
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