Matching Data

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

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
 
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
 
You can make use of the Autofilter function which minimize the code..

Sub AutoFilterSheet()
Dim lngARow As Long, lngBRow As Long
lngARow = Cells(Rows.Count, "A").End(xlUp).Row
Range("C1") = "Answer"
Range("A2:A" & lngARow).Copy Range("C2")
lngBRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("B2:B" & lngBRow).Copy Range("C" & lngARow + 1)
Columns("C:C").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("D1"), Unique:=True
Columns(3).Delete
End Sub

If this post helps click Yes
---------------
Jacob Skaria


JLatham said:
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
Click to expand...
Click to expand...
 
Works brilliantly up until it adds the data from the "Current" list. Here it
misses a row, but adds that data to the end of the list, also where does 1094
- 1099 come from ?

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
1090 1086
1093 1087
1088
1089
1090
1093
1094
1095
1096
1097
1098
1099
1085


JLatham said:
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
Click to expand...
Click to expand...
 
An alternative way, try this simple formulas play
Do a 5 second copy n paste to stack up the numeric data from cols A and B
into col C (the pastes can be in any order in col C)
Then place
In D1: =IF(C1="","",IF(COUNTIF(C$1:C1,C1)>1,"",C1))
In E1: =IF(ROWS($1:1)>COUNT(D:D),"",SMALL(D:D,ROWS($1:1)))
Copy D1:E1 down to the last row of data in col C. Col E returns the expected
results - ie unique nums, sorted in ascending order - all neatly bunched at
the top. Success? punch the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Rob, forgot to mention that you will have to have headers for the 2 columns
(in cell A1 and B1) as shown in your sample

If this post helps click Yes
---------------
Jacob Skaria


Jacob Skaria said:
You can make use of the Autofilter function which minimize the code..

Sub AutoFilterSheet()
Dim lngARow As Long, lngBRow As Long
lngARow = Cells(Rows.Count, "A").End(xlUp).Row
Range("C1") = "Answer"
Range("A2:A" & lngARow).Copy Range("C2")
lngBRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("B2:B" & lngBRow).Copy Range("C" & lngARow + 1)
Columns("C:C").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("D1"), Unique:=True
Columns(3).Delete
End Sub

If this post helps click Yes
---------------
Jacob Skaria


JLatham said:
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
Click to expand...
Click to expand...
Click to expand...
 
The same can be achieved without using the Macro…

A Col B Col C Col
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

I assume that you are having the previous values in Column A and Current
Values in Column B, and in C2 onwards you require the values of Col A & Col B
in Ascending Order without any duplications.

Just paste the below formula’s in that particular cells to derive the result.

In Cell C2
=SMALL(A:B,1)

In Cell D2
=COUNTIF(A:B,C2)

In Cell C3
=SMALL(A:B,SUM($D$2:D2)+1)

In Cell D3
=COUNTIF(A:B,C3)

Now Place the cursor in C3 cell and hold shift key and extend your selection
to D3 (i.e.C3 to D3) hold the shift key and Down Arrow and extend the
selection upto C2 to D40 and press Cntrl+D.

The Result #NUM! in C Column and the value “0†in D Column is the end of the
Result.

Hope this may be helpful to you…

If this post helps, Click Yes!
 
Back
Top