Excel sort lists with missing values

[cgray]

I need to sort columns of text (with some missing words) to line u
against a complete column of text. This would thus leave a blank ro
for the missing words in the incomplete column. Suggestions? Thanks

 

[Dave Peterson]

I guessed that you'd have headers in Row 1. And your data started in Row 2. I
also guessed that your masterlist was in column A.

This worked ok for me:

Option Explicit
Sub testme01()

Dim MstrArray As Variant
Dim colArray As Variant
Dim iCol As Long
Dim ictr As Long
Dim FirstErrorRow As Long
Dim ErrorRow As Long
Dim res As Variant

Dim wks As Worksheet
Dim newWks As Worksheet

Set wks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With wks
FirstErrorRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

.Range("a:a").Copy _
Destination:=newWks.Range("a1")

MstrArray = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value

For iCol = 2 To .Cells.SpecialCells(xlCellTypeLastCell).Column
ErrorRow = FirstErrorRow
colArray = .Range(.Cells(2, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp)).Value
For ictr = LBound(colArray, 1) To UBound(colArray, 1)
res = Application.Match(colArray(ictr, 1), MstrArray, 0)
If IsError(res) Then
newWks.Cells(ErrorRow, iCol).Value = colArray(ictr, 1)
ErrorRow = ErrorRow + 1
Else
newWks.Cells(res + 1, iCol).Value = colArray(ictr, 1)
End If
Next ictr
Next iCol

End With


End Sub