VBA: Group rows by a certain cell info (mini sheet included)

SereneSea

SereneSea

New Member
Joined
Feb 2, 2022
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
Hello all, I am so new to this VBA but I hope you can help.
I have a shared training sheet where other users can enter their training status and its represented in a gantt chart. I would like a code that:
  • If a person adds a row at the bottom of a table and writes a Trainer name in columne E, ie Beta, it automatically moves the whole row to where the rest of the Betas are in.
  • My B column is used as a data label for the chart and to make the chart look nice it needs to be merged, otherwise it looks repetitive if a code can do this as well. Picture below as an example what I like to see and not see.
  • And if possible, each time someone inserts a row in the last column to have all the formulas automatically get added (ie column C concatenate)
Thank you so much in advance.

Example1.xlsm
ABCDEFGHIJKLMNOPQR
1Training Plan 2021DO not delete Columns K to O please
2
3Plan Start4/1/2021
4Trainer for chartConcate for chartTestTrainerTraineePRIORITYSTARTPLAN ENDACTUAL END% COMPLETEProject StartDays to StartCompleteIncompletePlan Days
5AlphaTest 1 (T: Alpha)Test 1AlphaHotelHIGH7/1/20218/1/20217/9/2021100%4/1/20219131031
6Test 2 (T: Alpha)Test 2AlphaDeltaHIGH6/1/20217/1/2021100%4/1/20216130030
7Test 3 (T: Alpha)Test 3AlphaJuliettHIGH4/1/20215/5/20214/13/2021100%4/1/2021034034
8Test 4 (T: Alpha)Test 4AlphaCharlieHIGH6/1/20217/1/20216/10/2021100%4/1/20216130030
9Test 5 (T: Alpha)Test 5AlphaGolfHIGH5/1/20216/1/20217/7/2021100%4/1/20213031031
10BetaTest 6 (T: Beta)Test 6BetaAlphaLOW7/1/20216/30/202225%4/1/20219191273364
11Test 7 (T: Beta)Test 7BetaEchoHIGH7/1/20213/31/202275%4/1/202191204.7568.25273
12Test 8 (T: Beta)Test 8BetaDeltaLOW3/1/202212/1/20210%4/1/20213340-90-90
13Test 9 (T: Beta)Test 9BetaJuliettLOW9/1/202112/1/20210%4/1/202115309191
14Test 10 (T: Beta)Test 10BetaCharlieLOW9/1/202110/1/20210%4/1/202115303030
15Test 11 (T: Beta)Test 11BetaLimaLOW9/1/202110/1/20210%4/1/202115303030
16CharlieTest 12 (T: Charlie)Test 12CharlieRomeoMedium - refresher5/15/202110/31/202175%4/1/202144126.7542.25169
17CharlieTest 13 (T: Charlie)Test 13CharlieAplhaLow9/1/202110/1/20210%4/1/202115303030
18DeltaTest 14 (T: Delta)Test 14DeltaHotelMedium - refresher5/15/20215/30/20215/25/2021100%4/1/20214415015
19DeltaTest 15 (T: Delta)Test 15DeltaDeltaMedium - refresher5/15/20215/30/20215/25/2021100%4/1/20214415015
20DeltaTest 16 (T: Delta)Test 16DeltaJuliettHIGH5/1/20216/1/2021100%4/1/20213031031
21DeltaTest 17 (T: Delta)Test 17DeltaCharlieHIGH5/1/20216/1/2021100%4/1/20213031031
22EchoTest 18 (T: Echo)Test 18EchoGolfHigh6/1/202110/31/2021100%4/1/2021611520152
23EchoTest 19 (T: Echo)Test 19EchoZuluLow9/1/202111/1/202150%4/1/202115330.530.561
24EchoTest 20 (T: Echo)Test 20EchoYankeeMEDIUM1/1/20223/31/202250%4/1/202127544.544.589
25EchoTest 21 (T: Echo)Test 21EchoWhiskeyMedium6/1/20217/1/2021100%4/1/20216130030
26EchoTest 22 (T: Echo)Test 22EchoAphaLow11/1/202112/1/20210%4/1/202121403030
27EchoTest 23 (T: Echo)Test 23EchoDeltaMedium10/31/202111/30/202150%4/1/2021213151530
28FoxtrotTest 24 (T: Foxtrot)Test 24FoxtrotDeltaLow9/1/202110/1/202125%4/1/20211537.522.530
29FoxtrotTest 25 (T: Foxtrot)Test 25FoxtrotJuliettHigh - need to understand calculation and macro5/1/20218/1/202125%44317236992
30FoxtrotTest 26 (T: Foxtrot)Test 26FoxtrotLimaMedium - refresher6/1/20217/1/2021100%4/1/20216130030
31GolfTest 27 (T: Golf)Test 27GolfBetaHIGH8/1/202112/31/202150%4/1/20211227676152
32GolfTest 28 (T: Golf)Test 28GolfCharlieLOW6/1/20218/1/20210%4/1/20216106161
33BetaTest 29 (T: Beta)Test 29BetaAlphaHIGH7/1/20213/31/202275%44378204.7568.25273
34
Training Plan
Cell Formulas
RangeFormula
M5:M33M5=IF(ISBLANK(H5),0,H5-L5)
N5:N33N5=$K5*$P5
O5:O33O5=P5-N5
P5:P33P5=IF(ISBLANK(I5),0,I5-H5)
B5,B10,B16:B33B5=Table2[@Trainer]
C5:C33C5=CONCAT(Table2[@Test]," (T: ",Table2[@Trainer],")")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K22:K28Other TypeDataBarNO
G22:G28Cell Valuecontains "LOW"textNO
G22:G28Cell Valuecontains "MEDIUM"textNO
G22:G28Cell Valuecontains "HIGH"textNO
K29:K33Other TypeDataBarNO
G29:G33Cell Valuecontains "LOW"textNO
G29:G33Cell Valuecontains "MEDIUM"textNO
G29:G33Cell Valuecontains "HIGH"textNO
K18:K21Other TypeDataBarNO
G18:G21Cell Valuecontains "LOW"textNO
G18:G21Cell Valuecontains "MEDIUM"textNO
G18:G21Cell Valuecontains "HIGH"textNO
K16:K17Other TypeDataBarNO
G16:G17Cell Valuecontains "LOW"textNO
G16:G17Cell Valuecontains "MEDIUM"textNO
G16:G17Cell Valuecontains "HIGH"textNO
K10:K15Other TypeDataBarNO
G10:G15Cell Valuecontains "LOW"textNO
G10:G15Cell Valuecontains "MEDIUM"textNO
G10:G15Cell Valuecontains "HIGH"textNO
K5:K9Other TypeDataBarNO
G5:G9Cell Valuecontains "LOW"textNO
G5:G9Cell Valuecontains "MEDIUM"textNO
G5:G9Cell Valuecontains "HIGH"textNO
K34:K35Other TypeDataBarNO
G34:G35Cell Valuecontains "LOW"textNO
G34:G35Cell Valuecontains "MEDIUM"textNO
G34:G35Cell Valuecontains "HIGH"textNO
Cells with Data Validation
CellAllowCriteria
G5:G34ListHIGH,MEDIUM,LOW
K5:K21List=$C$49:$C$53
K22:K33List=$C$48:$C$52
K34:K35List=$D$48:$D$52


Gantt Chart.JPG
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Move Row based on a cell value then merge and unmerge cells - attached sample workbook
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Fluff said:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Move Row based on a cell value then merge and unmerge cells - attached sample workbook
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Click to expand...
I apologize, I will add links if I cross post again. My first time cross posting.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Cut and Paste certain cells of certain rows / if bored code optimization
Replies
0
Views
855
BobHex
B

Forum statistics

Threads
1,214,927
Messages
6,122,309
Members
449,080
Latest member
jmsotelo

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