One column into multiple columns and losing multiple entries

Here is a code for the elimination of the duplicates.

Sub DeleteDuplicateRows()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim r As Long
Dim n As Long
Dim V As Variant
Dim rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Set rng = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column))

Application.StatusBar = "Processing Row: " & Format(rng.Row, "#,##0")

n = 0
For r = rng.Rows.Count To 2 Step -1
If r Mod 500 = 0 Then
    Application.StatusBar = "Processing Row: " & Format(r, "#,##0")
End If

V = rng.Cells(r, 1).Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If V = vbNullString Then
    If Application.WorksheetFunction.CountIf(rng.Columns(1), vbNullString) > 1 Then
        rng.Rows(r).EntireRow.Delete
        n = n + 1
    End If
Else
    If Application.WorksheetFunction.CountIf(rng.Columns(1), V) > 1 Then
        rng.Rows(r).EntireRow.Delete
        n = n + 1
    End If
End If
Next r

EndMacro:

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(n)


End Sub

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Here is the second part of splitting the columns. Needed to develop this one. Already had the first part done from another project.

Option Explicit

Sub SplitC()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Dim x As Long
x = lr / 9

Range("A" & x + 1 & ":A" & x * 2).Copy Range("B1")
Range("A" & x * 2 + 1 & ":A" & x * 3).Copy Range("C1")
Range("A" & x * 3 + 1 & ":A" & x * 4).Copy Range("D1")
Range("A" & x * 4 + 1 & ":A" & x * 5).Copy Range("E1")
Range("A" & x * 5 + 1 & ":A" & x * 6).Copy Range("F1")
Range("A" & x * 6 + 1 & ":A" & x * 7).Copy Range("G1")
Range("A" & x * 7 + 1 & ":A" & x * 8).Copy Range("H1")
Range("A" & x * 8 + 1 & ":A" & lr + 1).Copy Range("I1")
Range("A" & x + 1 & ":A" & lr).ClearContents


End Sub

Hello, thank you alansidman!

Now it's time to try it out

edit: works like a miracle! thank you very much!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.