You are here:

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

Advertisement


Question
Sort Names
Sort Names  
Hello,

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

Regards,
Bimmy

Answer
Hi Bimmy,

If you have a look at my article about importing text: www.jkp-ads.com/articles/importtext.asp
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jan Karel Pieterse

Expertise

Excel and Excel/VBA questions

Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

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

©2016 About.com. All rights reserved.