Here's a macro that will do the job for you. Just be sure the sheet with the
data on it is the selected sheet when you run it.
To put it into place, open the workbook and press [Alt]+[F11] to open the VB
Editor and then choose Insert --> Module. Copy the code below and paste it
into the code module shown. Change the definitions of the Const values to
match the layout of your worksheet and then close the VB Editor. Run it from
Tools --> Macros --> Macro or from the [Developer] ribbon in Excel 2007.
Sub CreateAnswerList()
'change these as required
Const prevCol = "A"
Const currCol = "B"
Const answCol = "C"
Const firstDataRow = 2
'variables used
Dim sourceList As Range
Dim anySourceEntry As Range
Dim destList As Range
Dim anyDestEntry As Range
Dim lastRow As Long
Dim foundFlag As Boolean
Application.ScreenUpdating = False ' for performance
'begin by moving the Previous List
lastRow = Range(prevCol & Rows.Count).End(xlUp).Row
If lastRow < firstDataRow Then
lastRow = firstDataRow
End If
Set sourceList = Range(prevCol & firstDataRow & _
":" & prevCol & lastRow)
lastRow = Range(answCol & Rows.Count).End(xlUp).Row
Set destList = Range(answCol & firstDataRow & _
":" & answCol & lastRow)
For Each anySourceEntry In sourceList
foundFlag = False
For Each anyDestEntry In destList
If anySourceEntry = anyDestEntry Then
foundFlag = True
Exit For
End If
Next
If Not foundFlag Then
'a new entry, add to Answer list
lastRow = lastRow + 1
Range(answCol & lastRow) = anySourceEntry
'redefine destList
Set destList = Range(answCol & firstDataRow & _
":" & answCol & lastRow)
End If
Next
'add the Current list contents
lastRow = Range(currCol & Rows.Count).End(xlUp).Row
If lastRow < firstDataRow Then
lastRow = firstDataRow
End If
Set sourceList = Range(currCol & firstDataRow & _
":" & currCol & lastRow)
lastRow = Range(answCol & Rows.Count).End(xlUp).Row
Set destList = Range(answCol & firstDataRow & _
":" & answCol & lastRow)
For Each anySourceEntry In sourceList
foundFlag = False
For Each anyDestEntry In destList
If anySourceEntry = anyDestEntry Then
foundFlag = True
Exit For
End If
Next
If Not foundFlag Then
'a new entry, add to Answer list
lastRow = lastRow + 1
Range(answCol & lastRow) = anySourceEntry
'redefine destList
Set destList = Range(answCol & firstDataRow & _
":" & answCol & lastRow)
End If
Next
'housekeeping
Set sourceList = Nothing
Set destList = Nothing
End Sub
Rob said:
I have two Columns of data "Previous" and "Current" and I want to combine
them in to a column "Answer" that has both columns combined and only a single
unique entry. I tried advanced filter but this destroyed the format I require
by hiding the rows. Is their a formula you can use instead ?
Previous Current Answer
1058 1063 1058
1059 1065 1059
1060 1066 1060
1061 1067 1061
1063 1069 1063
1065 1071 1065
1066 1072 1066
1067 1073 1067
1069 1074 1069
1071 1075 1071
1072 1076 1072
1073 1077 1073
1074 1078 1074
1075 1080 1075
1076 1081 1076
1077 1082 1077
1078 1084 1078
1080 1085 1080
1081 1086 1081
1082 1087 1082
1084 1088 1084
1089 1085
1090 1086
1093 1087
1088
1089
1090
1093
),"",SMALL(D