Grouping of Data based on Criteria

[unknownymous]

Hi Guys,

Can you possibly help me on creating a macro that groups data based on below sample criteria:

Raw Data:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Team[/TD]
[TD]Name[/TD]
[TD]Status[/TD]
[TD]Date[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Math[/TD]
[TD]Ana[/TD]
[TD]Closed[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl63, width: 56, align: right"]9/21/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="width: 56"]Critical[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Math[/TD]
[TD]Seff[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]9/29/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Math[/TD]
[TD]Logan[/TD]
[TD]Closed[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]9/20/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Science[/TD]
[TD]Ben[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]1/8/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Critical[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Science[/TD]
[TD]Shy[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]1/2/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="width: 73"]Geography[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Ems[/TD]
[TD]Closed[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]2/9/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Science[/TD]
[TD]Dan[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]1/20/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="width: 56"]MajorCritical[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Science[/TD]
[TD]John[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]3/9/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Major[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Science[/TD]
[TD]Jim[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]2/27/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Critical[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Science[/TD]
[TD]Lena[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]2/2/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Critical[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Science[/TD]
[TD]Tom[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]2/25/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Science[/TD]
[TD]Cha[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]2/26/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Criteria:

[TABLE="width: 940"]
<tbody>[TR]
[TD]1. On Raw tab, filter based on "TEAM" column and those that falls under "Science" , copy the header and each row to another tab and rename the sheet as "SCIENCE"[/TD]
[/TR]
[TR]
[TD]2. On SCIENCE tab, filter "STATUS" column and copy those lines tagged as "OPEN" to another tab sheet and rename as "CLEAN DATA"[/TD]
[/TR]
[TR]
[TD]3. On the CLEAN DATA tab - Filter the "DATE" column and get those that are within the 60 calendar days from the latest date (will be the final list); For those beyond the 60 calendar days, if they have a note type with Critical, MajorCritical & Major, add them on the final list (with orange highlight) and for those with blank note add a separator line and indicate as "EXCLUDED"

So basically, there will be 3 tabs - Raw, Science & Clean Data. The "Clean Data" tab will look like this:


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Team[/TD]
[TD]Name[/TD]
[TD]Status[/TD]
[TD]Date[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Science[/TD]
[TD]John[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]3/9/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="width: 56"]Major[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Science[/TD]
[TD]Jim[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]2/27/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="width: 56"]Critical[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Science[/TD]
[TD]Cha[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]2/26/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Science[/TD]
[TD]Tom[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]2/25/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Science[/TD]
[TD]Lena[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]2/2/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="width: 56"]Critical[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Science[/TD]
[TD]Dan[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]1/20/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="width: 56"]MajorCritical[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Science[/TD]
[TD]Ben[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]1/8/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="width: 56"]Critical[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EXCLUDED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="width: 73"]Science[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Shy[/TD]
[TD]Open[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: right"]1/2/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]


PS: The Columns might be interchangeable so might be useful if search will be use in looking for the header name instead of range (like search for "Team" instead of B:B)


Any help will be much appreciated. Thanks a lot