You are here:

Excel/Macro to Split raw data,remove duplicates and paste in 2 different sheets


Sort Names
Sort Names  

I'm not a coder. Just an entry level guy.

Humbly request your assistance in any way possible.

As the subject states, I receive raw data in excel from which I need the macro to split 4 particular items, remove duplicates and sort according to certain criteria and paste the data in specific columns in sheet2 and sheet3.

Have attached snapshot of raw data with few explanations.

Brief explanation -

Raw data will be pasted in Sheet1 Cell 5 in Column F. Data in first 3 cells will be deleted.

What the macro should split from the raw data in excel -

- All the numbers immediately before the names, even numbers containing * symbol.
- All the names after the numbers
- 6 digit code after the names
- Single alphabets after the 6 digit code

Look for columns H,I,J,K in the snapshot which will give you an idea
about above points

How macro should paste the split data ===> very important

Macro should leave 1 column blank and paste data in adjacent columns as below -

6 digit code comes first, then the numbers, then the names and finally the single alphabets. (Refer columns H,I,J,K in the snapshot)

After this macro should remove duplicates as per data in first column ie 6 digit code. Then macro should sort the data as per last column ie single alphabets. Sorting criteria is F,A,Z,J,C,D,R,I,U,W,E,T,P,Y,B,H,K,M,L,V,S,N,O,Q,G,X

This final data is pasted as below -

Data in first 2 columns will be pasted in F2 and G2 in Sheet2, and
Data in all 4 columns will be pasted in A2,B2,C2,D2 in Sheet3


Hi Bimmy,

If you have a look at my article about importing text:
you will see that you can achieve the first couple of steps just by using the text import wizard: SPlitting columns and omitting columns from the import.

The remaining steps should be relatively easy to record a macro for: Removing duplicates and copying information to another sheet is not very hard to record a macro for.

I suggest you to take these steps and then come back with the recorded code indicating what the macro does correct and (more important) what you want it to do different.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jan Karel Pieterse


Excel and Excel/VBA questions


Excel MVP

Self employed Excel developer

Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 All rights reserved.