Macro to sort, split 6 digit code and numbers and remove duplicates

[Bimmy]

Requesting All Excel Gurus for your assistance.

Let me start by explaining what I have tried.

I tried recording a macro but its only helpful till few steps. After that I have to do
everything else manually.

For better understanding each step mentioned below will be pasted in different sheet
Refer Attachment - Sort

Currently using Excel 2007

Steps used for recording a macro -

1) Sheet1 - How the data is pasted
2) Sheet2 - How the data looks after it is sorted.
This is done by Selecting Column A, Goto Data, Sort.
In the next window all 3 tabs are already highlighted as Column A, Values, A to Z
click ok
3) Sheet2 - All data from Cell 1 to Cell 63 and data from cell 127 to cell 144 is manually
deleted. Dont know how to record this part as data keeps fluctuating and the cell
number keeps changing.
4) Sheet3 - How the data looks after following step 3.
Few things to note -
a) Data is sorted from smallest to largest numbers
b) Each name starts with a number and is followed by a 6 six digit alpha numeric code
c) In the next step this numbers and codes are separated
5) Sheet4 - How the data looks after the numbers and codes are separated
This is done by selecting column A, Goto Data, Text to Columns
In the next window option Fixed width is selected, click next.
In the next window 2 lines are fixed before and after the 6 digit code and
2 lines before and after the numbers - before the names.
A single space is left before the number. Refer Snapshot - Sort. Click Finish
Note - Column B has numbers and column D has codes
6) Sheet5 - From Sheet4 data in column B and D are only required so data in remaining
columns will be deleted.
Note - Codes will always be pasted first followed by numbers in the adjacent column.
7) Sheet5 - Final step is to remove duplicates. Duplicates will be removed on the basis
codes.
This is done by selecting both the columns. Goto Data, remove duplicates. Since the
codes are in Column A, in the next window we will untick Column B and click ok.

Now, I'm looking for a macro that can do all the above steps with a few additions

What the macro should do -

Data will be pasted in Sheet2 Column A.

In sheet1 in Cell M1 I will insert number as 10. This number will tell the macro to copy
data from Cell 10 in Column A. Macro should look for the keyword "Max" and copy data till cell number before the keyword Max.
Explanation -
Keyword Max is in cell 120. When macro runs, it should copy data from cell 10 to cell 119.
Macro should not copy cell number containing the keyword Max.

This data should be pasted in Column A in Sheet1. After this all the above mentioned steps from 1 to 7 should be performed. After the duplicates are removed the Codes and Numbers should be pasted in Column E and F.

Note -

Data in Sheet1 does not contain the keyword Max. You can insert the keyword in the
gaps in the data.

The data is flexible, currently data starts from 080 and ends at 142. This changes on
daily basis. It can start from 001 to 200 and so on.

Humbly request for everyone's expert assistance.

 

[Nebu]

Hi:

Could you please tell how your raw data looks like and what is your expected result, rather than explaining what you have done.Anyways I have written a macro assuming that your raw data is in tab one and your expected results is in tab 5 and the length and the layout of your data will be exactly the same.

Copy your raw data to column A of the tab where you have the run button , click on the run button you will get your outputs in column B and C.

Sub test()
Application.ScreenUpdating = False

Dim i As Long, j As Long

i = Sheet4.Cells(Rows.Count, "A").End(xlUp).Row

For j = i To 1 Step -1
    If Len(Range("A" & j)) <> 50 Then
        Rows(j).EntireRow.Delete
    End If
Next
i = Sheet4.Cells(Rows.Count, "A").End(xlUp).Row

For j = 1 To i
    Sheet4.Range("B" & j) = Mid(Range("a" & j), WorksheetFunction.Search("MR", Range("a" & j), 1) + 4, 6)
    Sheet4.Range("C" & j) = Mid(Range("a" & j), WorksheetFunction.Search(" ", Range("a" & j), 1) + 2, 2) + 0
Next

Application.ScreenUpdating = True

End Sub

Thanks

 

[Bimmy]

Hi Nebu,

Thank you for responding with the code.

The code does everything that is mentioned in points 2 to 6. Except for point 7 where it does not remove duplicates. Duplicates will be removed based on data in Column B.

I will be greatful if you can help me out with few more additions as stated earlier.

What exactly I'm looking for -

Few things to note -

a) Raw data will always be pasted in Sheet2
b) Macro activity will always be performed in Sheet1
c) Final result should reflect in Sheet2

Raw data will be pasted in Column A in Sheet2. Column may change so option should be provided in macro to adjust columns. 2 keywords Min and Max will be inserted in the raw data.

Macro should copy data from Sheet2 to Sheet1. Explanation as below -

In Sheet1 Cell D2 a number will be inserted. Lets say the number is 10. Macro should copy data from Sheet2, Cell 10 in Column A and copy data till cell not containing the keyword max. (Option should be provided to adjust column in macro)

Ex - I insert the number as 10 and my keyword max is in cell 144. When the macro is run it should copy data from cell 10 to cell 143. Macro should not copy cell containing the keyword max. This data should be pasted in Sheet1, Column A on Cell 5

Every time when the macro is run it should -
Copy data from - As per number entered in Sheet1
Copy data till - Cell number not containing keyword max

Once the data is pasted in Sheet1 keyword Min should be removed.

After the data is pasted in Sheet1, the macro activity will be performed in Sheet1. The final result should reflect in Sheet2, Columns B and C.

Very Important

As stated earlier the current code does not remove duplicates. Macro should remove duplicates based on data in Column B in Sheet1.

Have attached sheet with explanation

 

[Nebu]

Hi:

Please find the attached. Enter the start row click on the run button, you will get your output in column B & C of sheet 2.

Thanks

 

[Bimmy]

Hi Nebu,

Thank you for replying.

Let me check and get back to you.

 

[Bimmy]

Hi Nebu,

As per my understanding, macro counts length of all the characters including spaces and then do the sorting, splitting and removing the duplicates.

In dummy data provided by me earlier the length is 50 characters. Example as below

BA8754 MADLCY 1650 OUTBOUND BA0458 1050
081 01JACKY/THOMAS MR 5AIPQS B HK1 27OCT HDQ1P ===> 50 Characters
BA8754 G 04NOV MADLCY HK1
082 02JACK/HOLLAND MR ZA4VQT Y HK1 03NOV SWI1G ===> 50 Characters
IB3164 Y 04NOV MADLHR HK1
083 02WILLY/THOMAS MR ZA4VQT B HK1 27OCT HDQ1P ===> 50 Characters
BA8754 G 04NOV MADLCY HK1

However, this length keeps changing. It can be less than 50, more than 50 or exatly 50 characters.

I'm attaching previous sheet containing macro with a completely new raw data.

Humbly request for your assistance.

 

[Nebu]

Hi:

Find the attached. I assume that there will be a "/" in all your row items from where you want to extract data from. I have coded based on that, and I have also removed the extra spaces in between words to make the data consistent.

Thanks

 

[Bimmy]

This looks promising... Let me get back to you.

Just one more thing.

I would like to change Columns B and C to F2 and G2. What part of the code needs to be changed, I'm not able to figure it out.

Since the Column and Cell may change a remark in the code will be helpful.