VBA - Formatting and layout based on criterias

[BorisTheCat84]

Original Sheet:

Mr.Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	UUID	Whatsover1	Whatsover2	Whatsover3	Whatsover4	Whatsover5	Whatsover6	Whatsover7	Whatsover8	Whatsover9	Adjust and filter	Whatsover10
3	140542	Whatsover	Whatsover	Whatsover	Whatsover	Whatsover	5/20/2021 10:00:00 AM	5/20/2021 7:00:00 PM	Scheduled	Whatsover		3-Moderate/Limited
4			Whatsover	Whatsover	Whatsover	Whatsover	5/21/2021 10:00:00 AM	5/21/2021 10:00:00 AM			Orangevrqm is great for taste
5			Whatsover	Whatsover	Whatsover	Whatsover					Orangevrqm is really good
6			Whatsover	Whatsover	Whatsover	Whatsover					Orange is really goodwfm0
7											Orange has good result
8											Orange is really good
9											OrangeVMGT0 are everywhere
10											BlueberryVMGT0 is really good.Blueberry is delicous
11											Orange is in my house
12											Blueberry is really good
13											Blueberry is really good.Blueberry is delicous
14											BlueberryVMGT0 is really good.Blueberry is delicous1
15											Blueberry is really good.Blueberry is delicous2
16											Blueberry is reallyVMGT0 good.Blueberry is delicous3
17											Blueberry is really good.Blueberry is delicous4
18											Blueberry is really good.Blueberry is delicous5
19	140555	Whatsover					5/20/2021 1:00:00 PM	5/20/2021 3:00:00 PM	Draft	Whatsover	Orange is really good	3-Moderate/Limited
20											Orange is reallywfm0 good
21											Orangeiwfm0 has good result
22											Orange are everywhere
23	140888	Whatsover					5/20/2021 1:00:00 PM	5/20/2021 3:00:00 PM	Draft	Whatsover	Orangeiwfm0 is really good	3-Moderate/Limited
24											Orange has good result
25											Orangeiwfm0 is really good
26	May 20, 2021 8:35 AM
Sheet1

Work to be done:
A cleanup should be done in Column K:K respecting each UUID (in such not to apply the same condition on cells that belongs to the second UUID) according to the following criteria:
1- Clear Duplicates in the column
2- Clear all cells where "vrqm" or "VMGT0" or "wfm0" is not found in any word inside the cell // (note i have more but i will add them myself)
3- Shift cell up to avoid empty cells
4- Shift cell up in case the cell that matches the UUID is empty
5- Delete Empty Rows

Once that is done we will have to work on the entire table to properly format it, we will mainly need to merge empty cells in each column till they reach the line before the next UUID starts.
Finally, last task will be to merge and center the last row.

The expected output should be as follow:

Mr.Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	UUID	Whatsover1	Whatsover2	Whatsover3	Whatsover4	Whatsover5	Whatsover6	Whatsover7	Whatsover8	Whatsover9	Adjust and filter	Whatsover10
3	140542	Whatsover	Whatsover	Whatsover	Whatsover	Whatsover	5/20/2021 10:00:00 AM	5/20/2021 10:00:00 AM	Scheduled	Whatsover	Orangevrqm is great for taste	3-Moderate/Limited
4			Whatsover	Whatsover	Whatsover	Whatsover	5/21/2021 10:00:00 AM	5/21/2021 10:00:00 AM			Orangevrqm is really good
5			Whatsover	Whatsover	Whatsover	Whatsover					Orange is really goodwfm0
6			Whatsover	Whatsover	Whatsover	Whatsover					OrangeVMGT0 are everywhere
7											BlueberryVMGT0 is really good.Blueberry is delicous
8											BlueberryVMGT0 is really good.Blueberry is delicous1
9											Blueberry is reallyVMGT0 good.Blueberry is delicous3
10	140555	Whatsover	Whatsover	Whatsover	Whatsover	Whatsover	5/20/2021 1:00:00 PM	5/20/2021 3:00:00 PM	Draft	Whatsover	Orange is reallywfm0 good	3-Moderate/Limited
11											Orangeiwfm0 has good result
12
13	140888	Whatsover	Whatsover	Whatsover	Whatsover	Whatsover	5/20/2021 1:00:00 PM	5/20/2021 3:00:00 PM	Draft	Whatsover	Orangeiwfm0 is really good	3-Moderate/Limited
14			Whatsover	Whatsover	Whatsover	Whatsover	5/20/2021 1:00:00 PM	5/20/2021 3:00:00 PM
15
16	May 20, 2021 8:35 AM
Sheet2

Your help in finding out the suitable VBA Code to that will be appreciated,
Cheers!

 

[Peter_SSs]

Duplicate to: VBA - Layout and sorting with conditions

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.