Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching cells
Col A has 200 cells (i.e., A1.A200)
Col B has 1500 cells (i.e., B1.B1500) How can I find the cells in Col A whose contents match the contents in the cells in Col B? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching cells
Hi Gary,
Easy enough to do but need more information on what you want to do when the match is found. Do you need to know if there is more than one match or if one match found is that sufficient? (If one match is sufficient the VLOOKUP might so what you want.) What do you want to write against the cell where a match is found? (could write the matching cell address/s if required. Regards, OssieMac "gary" wrote: Col A has 200 cells (i.e., A1.A200) Col B has 1500 cells (i.e., B1.B1500) How can I find the cells in Col A whose contents match the contents in the cells in Col B? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching cells
Use Conditional Formatting is one-way.
While in Call A1 (activecell) Go to Format, Condtional Formatting Choose FormulaIs and in Box put =COUNTIF(B:B,A1)0 Use your Format Painter to Paint A1 to A2:A200 Jim May "gary" wrote: Col A has 200 cells (i.e., A1.A200) Col B has 1500 cells (i.e., B1.B1500) How can I find the cells in Col A whose contents match the contents in the cells in Col B? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching cells
In Col A, the data occurs once In Col B, the data may occur multiple times. I need to know about ALL matches. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching cells
Hi again Gary,
The following macro will set the interior color of the cells in column A to yellow if they are found in column B. It then inserts the address of the column A value in column C adjacent to the found value (there could be multiple occurrences of this address). Sub Match_Values() Dim rngA As Range Dim rngB As Range Dim CellA As Range Dim foundCell As Range Dim firstAddress As String 'Following assigns ranges of unknown length to 'variables starting from first cell of range. With Sheets("Sheet1") Set rngA = Range(.Cells(1, 1), _ .Cells(Rows.Count, 1).End(xlUp)) Set rngB = Range(.Cells(1, 2), _ .Cells(Rows.Count, 2).End(xlUp)) End With 'Alternative method of assigning ranges to a 'variable where the range is fixed and known. 'Set rngA = Sheets("Sheet1").Range("A1:A200") 'Set rngB = Sheets("Sheet1").Range("B1:B1500") For Each CellA In rngA With rngB Set foundCell = .Find(What:=CellA, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not foundCell Is Nothing Then firstAddress = foundCell.Address Do 'Set interior color of found cell to yellow CellA.Interior.ColorIndex = 6 'Insert cell address from column A foundCell.Offset(0, 1) = CellA.Address Set foundCell = .FindNext(foundCell) Loop While Not foundCell Is Nothing And _ foundCell.Address < firstAddress End If End With Next CellA End Sub Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
Help with Matching Text Fields - Then Moving the Matching Cells Side by Side | Excel Discussion (Misc queries) | |||
Matching two cells | Excel Worksheet Functions | |||
Matching cells | Excel Discussion (Misc queries) | |||
Matching cells | Excel Discussion (Misc queries) |