Data Cleaning

[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.

Just for your information, the real data contains more than 20,000 records and they are sticky confidential.

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

Thank you and have a nice day.

[jindon]

Try

Please Login or Register  to view this content.

[Olly]

An alternative approach:

Please Login or Register  to view this content.

[cmokasak]

Hi Olly,

it is not work.

[cmokasak]

Hi jindon,

But, it is not work with the data similar to 'ABCD-1234-0001'.
Please take a look at the file attached for the revised raw data.

[Olly]

Hi Olly,

it is not work.

In what way does it "Not work" ?

It works for me. See attachment.

[cmokasak]

Hi Olly,

Please see reply # 5
In addition: when I run your code, I get the results as attached.

[Olly]

Okay, so you've changed the structure of your data. The original requirement had values seapareted by ", " in each cell. Now they are separated by just "," (see the missing space?!)

So you need to modify the code to accommodate the different data structure.

Change this line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Attention to detail is REALLY important. Values separated by ", " are not the same as values separated by ","

It's also worth mentioning that the "Expected Output" values in your latest file upload don't actually match the "Input" values...

[cmokasak]

Thank you for your reply.

When I run your code, I just got a blank page.

However, when I run jindon's code, I got the results.
But when I run with the real data, it is not work with the data similar to 'ABCD-1234-0001'.

Please see reply # 5 for the new data samples.

[Olly]

This is getting to be hard work....

See attachment - it's your SECOND attachment, with my code added. Click the big button. Watch it sort the data as requested.

[cmokasak]

That's fantastic.

However, when you remove the number from the data to 'ABCD'.
This is not gone according to the second requirements.

Anyway, Thank you so much for your great work.

[Olly]

However, when you remove the number from the data to 'ABCD'.
This is not gone according to the second requirements.

Change the code to:

Please Login or Register  to view this content.

Better?

[cmokasak]

That's great.

Thank you for all the responses / answers that you give me.
It makes I feel so much appreciated.

Have a nice day

N.B. # 1: I have added a reputation to your profile.
N.B. # 2: The topic has been marked resolved.

[jindon]

Hi jindon,

But, it is not work with the data similar to 'ABCD-1234-0001'.

Change pattern to

Please Login or Register  to view this content.

[chayanonm]

That's great jindon.
I feel so much appreciated your responses / answers that you give me.
Have a nice day

N.B.: I have added a reputation to your profile.