Excel/Macro to Split raw data,remove duplicates and paste in 2 different sheets
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
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.