I have a excel sheet with contains the records as below..
also uploaded the input excelsheet and the output excel sheet(expected output).
322mpls32.net.xyz.comBW: 44.0 M Hrly Avg (IN / OUT)0:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00Mon 08/27/12 Tue 08/28/12 Wed 08/29/12 Thu 08/30/12 Fri 08/31/12 323mpls32.net.xyz.comBW: 44.0 M Hrly Avg (IN / OUT)0:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00Mon 08/27/12 Tue 08/28/12 Wed 08/29/12 Thu 08/30/12 Fri 08/31/12 324mpls32.net.xyz.comBW: 622.0 M Hrly Avg (IN / OUT)0:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00Mon 08/27/12 27% / 34%31% / 43%36% / 47%36% / 44%37% / 41%38% / 42%36% / 42%38% / 39% Tue 08/28/12 33% / 38%37% / 43%37% / 37%39% / 37%40% / 37%40% / 39%34% / 34% Wed 08/29/12 32% / 34%35% / 36%34% / 37%34% / 36%35% / 38%34% / 37%33% / 36% Thu 08/30/12 27% / 32%30% / 38%35% / 41%32% / 38%34% / 38%36% / 41%35% / 42%32% / 40% Fri 08/31/12 26% / 33%31% / 37%34% / 40%32% / 39%32% / 36%34% / 39%30% / 38%26% / 32% 326mpls32.net.xyz.comBW: 622.0 M Hrly Avg (IN / OUT)0:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00Mon 08/27/12 32% / 23%32% / 22%35% / 22%38% / 24%32% / 26%30% / 23% Tue 08/28/12 30% / 17% 35% / 20%30% / 19%34% / 19%33% / 20%31% / 21% Wed 08/29/12 32% / 18%31% / 18%37% / 18%32% / 18%34% / 21%32% / 21% Thu 08/30/12 30% / 17%30% / 18% 31% / 19%32% / 20%32% / 21% 327mpls32.net.xyz.comBW: 44.0 M Hrly Avg (IN / OUT)0:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00Mon 08/27/12 Tue 08/28/12 Wed 08/29/12 75% / 2%47% / 2% Thu 08/30/12 Fri 08/31/12 328mpls32.net.xyz.comBW: 44.0 M Hrly Avg (IN / OUT)0:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00Mon 08/27/12 Tue 08/28/12 Wed 08/29/12 Thu 08/30/12 33% / 32% 26% / 42%17% / 30%7% / 41% 49% / 4% Fri 08/31/12
Now I want to delete the records from the excel sheet based on the below criteria.
- blank lines with data should be deleted.
-
% values within the 1 hr block over 30% between 7am - 8pm time -
4 consecutive hour blocks over 30% between 7am - 8pm time -
The above criteria occurring at least 3 times within the week
Also attached the output file.
Could anyone please help me on this ?