Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching 2 columns
Sorry folks but I am having a brain dead moment because I know I have done
this before. I have three columns of data, in column A I have a list of names, in column B I have another list of names that are matched up with the dates in column C. I want to be able to match up the names in column B with the names in column A by forcing the names in column B down until they find a match in column A. Column A has more names than column B and not all names have a match. The names in column A that dont have a match will be deleted. A B C Ashton, Bobbie Brown, Joe 1/2/1967 Brown, Joe Jones, Penny 8/7/1972 Crisp, Jason Smith, John 12/6/1981 Smith, John Hope this makes sense. Thanks in advance. Cheers Lynda |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching 2 columns
You don't need the temporary Col C. If you are looking to get data as shown
below. ColC retrieves the 1st matching record of John ColD retreives the second and so on. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Try this formula in C1 and copy across to D1, E1 etc; =INDEX($A:$A,SMALL(IF($B$1:$B$1000<$B1,"",($B$1:$ B$1000=$B1)*ROW($B$1:$B$1000)),COLUMN(A1))) Col A Col B Col C Col D Col E A John A B C E Mary F Serra B John G Phil H Ben C John If this post helps click Yes --------------- Jacob Skaria "Lynda" wrote: Sorry folks but I am having a brain dead moment because I know I have done this before. I have three columns of data, in column A I have a list of names, in column B I have another list of names that are matched up with the dates in column C. I want to be able to match up the names in column B with the names in column A by forcing the names in column B down until they find a match in column A. Column A has more names than column B and not all names have a match. The names in column A that dont have a match will be deleted. A B C Ashton, Bobbie Brown, Joe 1/2/1967 Brown, Joe Jones, Penny 8/7/1972 Crisp, Jason Smith, John 12/6/1981 Smith, John Hope this makes sense. Thanks in advance. Cheers Lynda |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching 2 columns
Hi Lynda
Please ignore the previous post.....For your query please try the below macro which use ColD as a temporary column and try to find a match in ColA. Once a match is done for all entries in ColB ColA is replaced with ColD. Does that suit your requirement? Sub MatchAndDelete() Dim lngRow As Long, rngTemp As Range Set rngTemp = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Trim(Range("B" & lngRow)) < "" Then If WorksheetFunction.CountIf(rngTemp, Range("B" & lngRow)) 0 Then Range("D" & lngRow) = Range("B" & lngRow).Text End If End If Next Range("A:A") = Range("D:D").Value Range("D:D").ClearContents End Sub If this post helps click Yes --------------- Jacob Skaria "Lynda" wrote: Sorry folks but I am having a brain dead moment because I know I have done this before. I have three columns of data, in column A I have a list of names, in column B I have another list of names that are matched up with the dates in column C. I want to be able to match up the names in column B with the names in column A by forcing the names in column B down until they find a match in column A. Column A has more names than column B and not all names have a match. The names in column A that dont have a match will be deleted. A B C Ashton, Bobbie Brown, Joe 1/2/1967 Brown, Joe Jones, Penny 8/7/1972 Crisp, Jason Smith, John 12/6/1981 Smith, John Hope this makes sense. Thanks in advance. Cheers Lynda |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching 2 columns
Thank you Jacob, it works a treat.
Cheers Lynda "Jacob Skaria" wrote: Hi Lynda Please ignore the previous post.....For your query please try the below macro which use ColD as a temporary column and try to find a match in ColA. Once a match is done for all entries in ColB ColA is replaced with ColD. Does that suit your requirement? Sub MatchAndDelete() Dim lngRow As Long, rngTemp As Range Set rngTemp = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Trim(Range("B" & lngRow)) < "" Then If WorksheetFunction.CountIf(rngTemp, Range("B" & lngRow)) 0 Then Range("D" & lngRow) = Range("B" & lngRow).Text End If End If Next Range("A:A") = Range("D:D").Value Range("D:D").ClearContents End Sub If this post helps click Yes --------------- Jacob Skaria "Lynda" wrote: Sorry folks but I am having a brain dead moment because I know I have done this before. I have three columns of data, in column A I have a list of names, in column B I have another list of names that are matched up with the dates in column C. I want to be able to match up the names in column B with the names in column A by forcing the names in column B down until they find a match in column A. Column A has more names than column B and not all names have a match. The names in column A that dont have a match will be deleted. A B C Ashton, Bobbie Brown, Joe 1/2/1967 Brown, Joe Jones, Penny 8/7/1972 Crisp, Jason Smith, John 12/6/1981 Smith, John Hope this makes sense. Thanks in advance. Cheers Lynda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching 2 columns | Excel Discussion (Misc queries) | |||
matching columns | Excel Worksheet Functions | |||
Matching 2 columns | Excel Worksheet Functions | |||
matching on columns | Excel Discussion (Misc queries) | |||
MATCHING COLUMNS | Excel Discussion (Misc queries) |