Data Cleaning (Revised)

[cmokasak]

Hi everyone,

Good evening. Could anyone provide me VBA code for the following requirements?

Requirement 1: Separate data to different row if there are symbol ',':-

• Example 1: there are "BB1, BB2, BB3" for set 2. I need one row for BB1, one row for BB2 and one row for BB3 with set 2 at column A
• Example 2: there are "BB1, BB2" for set 4. I need one row for BB1 and one row for BB2 with set 4 at column A
• Example 3: there are "AA, BB1" for set 5. I need one row for AA and one row for BB1 with set 5 at column A

Requirement 2: Remove cell (shift up) if there is no number in the cell:-

• For example, AA for set 5 should be excluded from the list because it doesn't contain number.

Would it be possible to provide me one VBA code for requirement 1 and another one requirement for requirement 2?
Just for your information, the real data contains more than 20,000 records.

Please take a look at the file attached for my expected output with samples.

Thank you and have a nice day

N.B. I try to use my old topic's solution but the requirements are slightly difference.

[JieJenn]

What have you tried so far?

[cmokasak]

I try to use the same code (by millz) as my old topic: http://www.excelforum.com/showthread...t=#post4428512.
But, it is not working as the requirement is different.

Please Login or Register  to view this content.

Then, I try to use function to separate the data and arrange them manually.
But, it is not suitable for the table of 20,000 records.

This is very importance data. Therefore, I try to find the best way to separate it.

[cmokasak]

Can anyone help me please?

[cmokasak]

There is no solution yet.
Could anyone help me please?