Grouping of Data based on Criteria

U

unknownymous

Board Regular
Joined
Sep 19, 2017
Messages
249
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You must log in or register to reply here.

Similar threads

H
  • Question Question
Macro to download zip, unpack and copy data
Replies
0
Views
655
HeinrichPaul
H
F
  • Question Question
Looking to index some data based on multiple criteria, then index further data based on the 1st indexed data results
2
Replies
13
Views
872
KRice
K
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
357
Anthony47
A
k3yn0t3
  • Solved
XLOOKUP (multiple criteria) not working
Replies
3
Views
493
k3yn0t3
k3yn0t3
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
294
Jagadeshrao
J

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top