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:

ID	Team	Name	Status	Date	Type
1	Math	Ana	Closed	9/21/2017 <tbody> </tbody>	Critical <tbody> </tbody>
2	Math	Seff	Open	9/29/2017 <tbody> </tbody>
3	Math	Logan	Closed	9/20/2017 <tbody> </tbody>
4	Science	Ben	Open	1/8/2018 <tbody> </tbody>	Critical
5	Science	Shy	Open	1/2/2018 <tbody> </tbody>
6	Geography <tbody> </tbody>	Ems	Closed	2/9/2018 <tbody> </tbody>
7	Science	Dan	Open	1/20/2018 <tbody> </tbody>	MajorCritical <tbody> </tbody>
8	Science	John	Open	3/9/2018 <tbody> </tbody>	Major
9	Science	Jim	Open	2/27/2018 <tbody> </tbody>	Critical
10	Science	Lena	Open	2/2/2018 <tbody> </tbody>	Critical
11	Science	Tom	Open	2/25/2018 <tbody> </tbody>
12	Science	Cha	Open	2/26/2018 <tbody> </tbody>

<tbody>
</tbody>

Criteria:

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"

2. On SCIENCE tab, filter "STATUS" column and copy those lines tagged as "OPEN" to another tab sheet and rename as "CLEAN DATA"

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: ID Team Name Status Date Type 8 Science John Open 3/9/2018 <tbody> </tbody> Major <tbody> </tbody> 9 Science Jim Open 2/27/2018 <tbody> </tbody> Critical <tbody> </tbody> 12 Science Cha Open 2/26/2018 <tbody> </tbody> 11 Science Tom Open 2/25/2018 <tbody> </tbody> 10 Science Lena Open 2/2/2018 <tbody> </tbody> Critical <tbody> </tbody> 7 Science Dan Open 1/20/2018 <tbody> </tbody> MajorCritical <tbody> </tbody> 4 Science Ben Open 1/8/2018 <tbody> </tbody> Critical <tbody> </tbody> EXCLUDED 5 Science <tbody> </tbody> Shy Open 1/2/2018 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

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