| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching Data
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching Data
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching Data
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" wrote: 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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching Data
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" wrote: 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" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching Data
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" wrote: 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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching Data
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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Matching Data
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 formulas 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! -------------------- (Ms-Exl-Learner) -------------------- "Rob" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Enter Data on one tab, then find matching data on another tab | Excel Worksheet Functions | |||
| Matching a column of new data to existing larger data set. | Excel Worksheet Functions | |||
| Matching identical data using data only once in the matching proce | Excel Discussion (Misc queries) | |||
| Can I merge data in 2 sheets matching rows of data by last name? | Excel Discussion (Misc queries) | |||
| Matching data and linking it to the matching cell | Links and Linking in Excel | |||
Hybrid Mode
