VBA - Layout and sorting with conditions

[BorisTheCat84]

Hi Team,

I'm facing the current scenario, I will start by placing the sheet that i'm working in and then bring up what has to be done:

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					Orangevrqm is great for taste
5			Whatsover	Whatsover	Whatsover	Whatsover					Orangevrqm is really good
6			Whatsover	Whatsover	Whatsover	Whatsover					Orange has good result
7											Orange is really good
8											OrangeVMGT0 are everywhere
9											Orange is in my house
10											Blueberry is really good
11											Blueberry is really good.Blueberry is delicous
12											BlueberryVMGT0 is really good.Blueberry is delicous1
13											Blueberry is really good.Blueberry is delicous2
14											Blueberry is reallyVMGT0 good.Blueberry is delicous3
15											Blueberry is really good.Blueberry is delicous4
16											Blueberry is really good.Blueberry is delicous5
17	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
18											Orangeiwfm0 has good result
19											Orange are everywhere
20	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
21											Orange has good result
22											Orangeiwfm0 is really good
23	May 20, 2021 8:35 AM
Sheet1

So Mainly 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:

UUID	Whatsover1	Whatsover2	Whatsover3	Whatsover4	Whatsover5	Whatsover6	Whatsover7	Whatsover8	Whatsover9	Adjust and filter	Whatsover10
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
		Whatsover	Whatsover	Whatsover	Whatsover	5/20/2021 10:00:00 AM	5/20/2021 10:00:00 AM			Orangevrqm is really good
		Whatsover	Whatsover	Whatsover	Whatsover					Orange is really goodwfm0
		Whatsover	Whatsover	Whatsover	Whatsover					OrangeVMGT0 are everywhere
										BlueberryVMGT0 is really good.Blueberry is delicous
										Blueberry is reallyVMGT0 good.Blueberry is delicous3
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
										Orangeiwfm0 has good result
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
		Whatsover	Whatsover	Whatsover	Whatsover	5/20/2021 1:00:00 PM	5/20/2021 3:00:00 PM
May 20, 2021 8:35 AM

I would appreciate if you guys could help me with the VBA Code.

Cheers!
Boris

 

[severynm]

Where is your data coming from? This is possible to do with VBA, but the steps you outlined seems to be MUCH easier to do with a few steps with Power Query.
1. Remove duplicates from 'Adjust and Filter' column
2. Filter only cells that contain the specified strings from 'Adjust and Filter' column
3. Fill down (on all the columns it's needed on)

With Power Query it is that it's literally a few button presses to get what you need and I'll argue it's less error-prone than a script in the long run. However, you will not be able to get the exact formatting you wanted (i.e. merging the cells like that).

 

[BorisTheCat84]

Where is your data coming from? This is possible to do with VBA, but the steps you outlined seems to be MUCH easier to do with a few steps with Power Query.
1. Remove duplicates from 'Adjust and Filter' column
2. Filter only cells that contain the specified strings from 'Adjust and Filter' column
3. Fill down (on all the columns it's needed on)

With Power Query it is that it's literally a few button presses to get what you need and I'll argue it's less error-prone than a script in the long run. However, you will not be able to get the exact formatting you wanted (i.e. merging the cells like that).

I do know that it can be done in VBA - it's not complicated // more complexe tasks were already built with VBA...

 

[severynm]

If there's a specific reason you want/need to use VBA, by all means go for it; I was merely trying to propose an alternative already built into excel you might find useful that has also helped me out immensely when cleaning and transforming lots of data.

If you have some specific things in your VBA script you need help with I'd be happy to help you out.

 

[BorisTheCat84]

If there's a specific reason you want/need to use VBA, by all means go for it; I was merely trying to propose an alternative already built into excel you might find useful that has also helped me out immensely when cleaning and transforming lots of data.

If you have some specific things in your VBA script you need help with I'd be happy to help you out.

Thank you! - i'm aware of the Power Queries but we need the script to run with other macros as well...
Mainly what's needed is the code to the described scenario

 

[BorisTheCat84]

Is there someone that can help me in here? @kweaver ?

 

[BorisTheCat84]

Small mistake // main table:

Mr.Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
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 has good result
7											Orange is really good
8											OrangeVMGT0 are everywhere
9											Orange is in my house
10											Blueberry is really good
11											Blueberry is really good.Blueberry is delicous
12											BlueberryVMGT0 is really good.Blueberry is delicous1
13											Blueberry is really good.Blueberry is delicous2
14											Blueberry is reallyVMGT0 good.Blueberry is delicous3
15											Blueberry is really good.Blueberry is delicous4
16											Blueberry is really good.Blueberry is delicous5
17	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
18											Orangeiwfm0 has good result
19											Orange are everywhere
20	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
21											Orange has good result
22											Orangeiwfm0 is really good
23	May 20, 2021 8:35 AM
Sheet1

Expected output:

UUID	Whatsover1	Whatsover2	Whatsover3	Whatsover4	Whatsover5	Whatsover6	Whatsover7	Whatsover8	Whatsover9	Adjust and filter	Whatsover10
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
		Whatsover	Whatsover	Whatsover	Whatsover	5/21/2021 10:00:00 AM	5/21/2021 10:00:00 AM			Orangevrqm is really good
		Whatsover	Whatsover	Whatsover	Whatsover					Orange is really goodwfm0
		Whatsover	Whatsover	Whatsover	Whatsover					OrangeVMGT0 are everywhere
										BlueberryVMGT0 is really good.Blueberry is delicous
										Blueberry is reallyVMGT0 good.Blueberry is delicous3
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
										Orangeiwfm0 has good result
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
		Whatsover	Whatsover	Whatsover	Whatsover	5/20/2021 1:00:00 PM	5/20/2021 3:00:00 PM
May 20, 2021 8:35 AM

Can someone help please?

 

[Peter_SSs]

Are the expected results accurate?

If so, I don't understand how they are decided. For example, for UUID 140542, in your expected results you have these two results. I cannot see where they come from in your original data.
Orange is really goodwfm0
BlueberryVMGT0 is really good.Blueberry is delicous

Should the expected results replace the original data or should they appear elsewhere in the worksheet or in another worksheet?

 

[BorisTheCat84]

Are the expected results accurate?

If so, I don't understand how they are decided. For example, for UUID 140542, in your expected results you have these two results. I cannot see where they come from in your original data.
Orange is really goodwfm0
BlueberryVMGT0 is really good.Blueberry is delicous

Should the expected results replace the original data or should they appear elsewhere in the worksheet or in another worksheet?

Hi Peter,
I would like to take the opportunity to thank you as you are right, i forgot to add them in the main report as they show in the expected results, that was my fault, apologies to that...
So here it is again, main report:

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

Expected output:

UUID	Whatsover1	Whatsover2	Whatsover3	Whatsover4	Whatsover5	Whatsover6	Whatsover7	Whatsover8	Whatsover9	Adjust and filter	Whatsover10
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
		Whatsover	Whatsover	Whatsover	Whatsover	5/21/2021 10:00:00 AM	5/21/2021 10:00:00 AM			Orangevrqm is really good
		Whatsover	Whatsover	Whatsover	Whatsover					Orange is really goodwfm0
		Whatsover	Whatsover	Whatsover	Whatsover					OrangeVMGT0 are everywhere
										BlueberryVMGT0 is really good.Blueberry is delicous
										Blueberry is reallyVMGT0 good.Blueberry is delicous3
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
										Orangeiwfm0 has good result
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
		Whatsover	Whatsover	Whatsover	Whatsover	5/20/2021 1:00:00 PM	5/20/2021 3:00:00 PM
May 20, 2021 8:35 AM

"Should the expected results replace the original data or should they appear elsewhere in the worksheet or in another worksheet?" => No, nothing has to be replaced, the main focus remains on cleaning Column K respecting each UUID first as mentioned in the main description:
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

That means each color to be cleaned seperately from other colors:

Mr.Excel.xlsx
	K
1
2	Adjust and filter
3
4	Orangevrqm is great for taste
5	Orangevrqm is really good
6	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	Orange is really good
20	Orange is reallywfm0 good
21	Orangeiwfm0 has good result
22	Orange are everywhere
23	Orangeiwfm0 is really good
24	Orange has good result
25	Orangeiwfm0 is really good
26
Sheet1

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.

Appreciate your help on it, please note the code should detect all UUIDs as in this sample i only placed 3 UUIDs but usually they are more.
Looking forward to hearing back from you, hope all is clear now.
Thanks!

 

[Peter_SSs]

=> No, nothing has to be replaced, the main focus remains on cleaning Column K

I think that you mis-understood what I was asking. Your original data in post #9 is shown in the range A2:L26.
Your expected results do not show rows or columns. Are they in A2:L13 (approx) in the same worksheet or in, say Y2:AJ13 (approx) in the same worksheet or even on a different worksheet?"