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

 

[BorisTheCat84]

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?"

Oh apologies to that, the result will be in A2:L13 (approx) in the same worksheet.
The main report can disappear, won't need it after the cleanup runs through the code

 

[Peter_SSs]

Thanks for the additional clarification. I'm not sure that I will be able to help with this in the end but it still isn't clear anyway.

Why isn't BlueberryVMGT0 is really good.Blueberry is delicous1 in the expected results for UUID 140542?

How do UUIDs 140555 and 140888 get values in columns C:F in the expected results when they have no values in those columns in the original data?

 

[BorisTheCat84]

Thanks for the additional clarification. I'm not sure that I will be able to help with this in the end but it still isn't clear anyway.

Why isn't BlueberryVMGT0 is really good.Blueberry is delicous1 in the expected results for UUID 140542?

How do UUIDs 140555 and 140888 get values in columns C:F in the expected results when they have no values in those columns in the original data?

Hi Peter,
BlueberryVMGT0 is really good.Blueberry is delicous1 is in the expected results for UUID 140542 (i've checked it in the expected output and it's there)
How do UUIDs 140555 and 140888 get values in columns C:F in the expected results when they have no values in those columns in the original data? =>Whatsoever values we have for C:F we won't touch it, the objective is to merge the blank cells in the column, you can consider each "whatsoever" to be "whatsoeverX" with X a variable...

If you can't help, thank you for having tried && i would appreciate if you can assign it to someone that would be able to help finding out the code (VBA).

 

[BorisTheCat84]

Hi Peter,
BlueberryVMGT0 is really good.Blueberry is delicous1 is in the expected results for UUID 140542 (i've checked it in the expected output and it's there)
How do UUIDs 140555 and 140888 get values in columns C:F in the expected results when they have no values in those columns in the original data? =>Whatsoever values we have for C:F we won't touch it, the objective is to merge the blank cells in the column, you can consider each "whatsoever" to be "whatsoeverX" with X a variable...

If you can't help, thank you for having tried && i would appreciate if you can assign it to someone that would be able to help finding out the code (VBA).

After a double check i've noticed the BlueberryVMGT0 is really good.Blueberry is delicous3 is there but not the BlueberryVMGT0 is really good.Blueberry is delicous1 // i've added it
Thank you for your patience , here we go again for the expected output:

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

 

[Peter_SSs]

Whatsoever values we have for C:F we won't touch it, the objective is to merge the blank cells in the column, you can consider each "whatsoever" to be "whatsoeverX" with X a variable...

I didn't understand that. Don't we only merge rows within each UUID range of rows?
I would have thought that since the green and blue areas below have no data, then the results for those sections would have no data. Are you specifically saying that here cells C6:C22 (from UUID 140542 and 140555 and 140888) all get merged into one single merge area and similar for the other columns?

BorisTheCat84.xlsm
	A	B	C	D	E	F	G
1
2	UUID	Whatsover1	Whatsover2	Whatsover3	Whatsover4	Whatsover5	Whatsover6
3	140542	Whatsover	Whatsover	Whatsover	Whatsover	Whatsover	5/20/2021 10:00:00 AM
4			Whatsover	Whatsover	Whatsover	Whatsover
5			Whatsover	Whatsover	Whatsover	Whatsover
6			Whatsover	Whatsover	Whatsover	Whatsover
7
8
9
10
11
12
13
14
15
16
17	140555	Whatsover					5/20/2021 1:00:00 PM
18
19
20	140888	Whatsover					5/20/2021 1:00:00 PM
21
22
23	May 20, 2021 8:35 AM
Sheet1

i would appreciate if you can assign it to someone

Nobody "works" for the forum so there is no assigning of threads. It is a public forum and if anybody wants to help with a thread they can but there is no compulsion to do so. Still, you never know, somebody else might step in at any time.