Eliminate non-matching cells

[GARY]

Col A has 4,481 cells
Col B has 18,513 cells

How do I eliminate the cells in Col B whose 19 left-most characters do
NOT match the 19 left-most characters in any of the cells in Col A?

 

[Jim Cone]

Column C: =LEFT(A1,19)
Column D: =LEFT(B1,19)
Column E: =COUNTIF($C$1:$C$4481,D1)
Filter on Column E for 0
Select Column B
Edit | GoTo | Special (button) | Visible cells only (check mark it)
Press delete
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)



"GARY" <[email protected]>
wrote in message
news:e3eb5b73-e57f-4eab-a4bb-eeac534a20df@o18g2000prh.googlegroups.com...

 

[GARY]

Oops! Col A has 4,841 cells so I changed your formula for Col D
accordingly.

In Col C, I pasted =LEFT(A1,19) in C1 thru C4841
In Col D, I pasted =LEFT(B1,19) in D1 thru D18513
In Col E, I pasted =COUNTIF($C$1:$C$4841,D1) in E1 thru E18513

After completing your instructions, how do I interpret the results?

 

[GARY]

Column C:  =LEFT(A1,19)
Column D:  =LEFT(B1,19)
Column E:  =COUNTIF($C$1:$C$4481,D1)
Filter on Column E for 0
Select Column B
Edit | GoTo | Special (button) | Visible cells only (check mark it)
Press delete
'---
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in:  compares, matches, counts, lists, finds, deletes....)

"GARY" <[email protected]>
wrote in message




- Show quoted text -

Oops! Col A has 4,841 cells so I changed your formula for Col D
accordingly.

In Col C, I pasted =LEFT(A1,19) in C1 thru C4841
In Col D, I pasted =LEFT(B1,19) in D1 thru D18513
In Col E, I pasted =COUNTIF($C$1:$C$4841,D1) in E1 thru E18513

After completing your instructions, how do I interpret the results?

Also, only the first 19 characters of each cell is displayed; I need
all of the characters displayed.

 

[Jim Cone]

The Column E formula counts the number of times each cell in Column D (b) appears in
Column C (a).
A count of 0 indicates no match. So you have...
ColumnA and ColumnB unchanged and a new column that flags mismatches with a zero.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Formats & Styles: lists or removes unused styles & number formats - in the free folder)





"GARY" <[email protected]>
wrote in message
Oops! Col A has 4,841 cells so I changed your formula for Col D
accordingly.

In Col C, I pasted =LEFT(A1,19) in C1 thru C4841
In Col D, I pasted =LEFT(B1,19) in D1 thru D18513
In Col E, I pasted =COUNTIF($C$1:$C$4841,D1) in E1 thru E18513

After completing your instructions, how do I interpret the results?

Also, only the first 19 characters of each cell is displayed; I need
all of the characters displayed.

 

[GARY]

A2 and B3 contain the same 19 leftmost characters so the count should
be 1 (match) not 0 (no-match).

 

[Jim Cone]

Well either A2 and B3 don't agree or the formula is wrong.
I don't have access to either.
Check that the formula contains absolute references: "$" for the range being searched.
Also, enter the formula "=C2=D3" off the side someplace and see what you get.



"GARY" <[email protected]>
wrote in message
news:[email protected]...

 

[GS]

Jim Cone submitted this idea :

Well either A2 and B3 don't agree or the formula is wrong.
I don't have access to either.
Check that the formula contains absolute references: "$" for the range being
searched.
Also, enter the formula "=C2=D3" off the side someplace and see what you
get.



"GARY" <[email protected]>
wrote in message

Jim,
Since A2 does not match B3 then both SHOULD return zero using your
formula. What Gary wants is to use a lookup function to see if the
values in one column are found in the other. Flagging them with CF
using COUNTIF() doesn't require any in cell formulas (this is how I
found them). So then...

Select ColA, enter the following formula in the CF dialog:
=COUNTIF($B:$B,$A1)>0
Format with a box border with or without fill.

Repeat for ColB with the following formula:
=COUNTIF($A:$A,$B1)>0

 

[Jim Cone]

I ruled out CF due to my estimate of poster knowledge level.
Thought filling in formulas in successive columns would help build knowledge.
Also, the stated goal was to just find items in column B missing from column A.
In any case, I won't be doing any more work on this one.
'--
Jim Cone



"GS" <[email protected]>
wrote in message

 

[Ron Rosenfeld]

Col A has 4,481 cells
Col B has 18,513 cells

How do I eliminate the cells in Col B whose 19 left-most characters do
NOT match the 19 left-most characters in any of the cells in Col A?

This can also be done, fairly easily, with a VBA Macro. Don't be put off -- detailed directions below.

Instead of eliminating the data in column B, I have chosen to write the results into a third column, for easier troubleshooting. This third column is completely cleared before the data is written, so when you test, be sure there is nothing you require in Column C, and also do this work on a copy of your data, not the original.

If the algorithm seems to work OK, it will be relatively simple to modify the code to delete the relevant cells in Col B, if that is what you want. Or you can just delete the entire column B.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

=======================================
Option Explicit
Sub DeDup()
Dim r1 As Range, r2 As Range, c As Range
Dim FirstRow As Long
Dim rDest As Range
Dim i As Long
Dim dataColl As Collection

'Set up ranges
FirstRow = 1 'or whichever row your data starts
Set r1 = Range(Cells(FirstRow, "A"), Cells(Cells.Rows.Count, "A").End(xlUp))
Set r2 = Range(Cells(FirstRow, "B"), Cells(Cells.Rows.Count, "B").End(xlUp))
Set rDest = r2.Offset(columnoffset:=1).Resize(rowsize:=1)
rDest.EntireColumn.ClearContents

Set dataColl = New Collection

'Generate unique list of contents of Col A
On Error Resume Next
For Each c In r1
dataColl.Add Item:=c.Value, Key:=Left(CStr(c.Value), 19)
Next c
On Error GoTo 0
i = 1

'if colB does NOT match, enter in Destination range
On Error GoTo ErrorHandler
For Each c In r2
dataColl.Add Item:=c.Value, Key:=Left(CStr(c.Value), 19)
rDest(i, 1).Value = c.Value
i = i + 1
dataColl.Remove (Left(CStr(c.Value), 19))
Skip: Next c

Exit Sub
ErrorHandler: Resume Skip

End Sub
====================================

 

[Ron Rosenfeld]

Col A has 4,481 cells
Col B has 18,513 cells

How do I eliminate the cells in Col B whose 19 left-most characters do
NOT match the 19 left-most characters in any of the cells in Col A?

FWIW, here's a bit shorter routine that uses the dictionary object which would make it case sensitive. The Collection object used in the previous post is case insensitive.

===========================
Option Explicit
Sub DeDup()
Dim r1 As Range, r2 As Range, c As Range
Dim FirstRow As Long
Dim rDest As Range
Dim i As Long
Dim dict As Object, dictKey As String

'Set up ranges
FirstRow = 1 'or whichever row your data starts
Set r1 = Range(Cells(FirstRow, "A"), Cells(Cells.Rows.Count, "A").End(xlUp))
Set r2 = Range(Cells(FirstRow, "B"), Cells(Cells.Rows.Count, "B").End(xlUp))
Set rDest = r2.Offset(columnoffset:=1).Resize(rowsize:=1)
rDest.EntireColumn.ClearContents

Set dict = CreateObject("scripting.dictionary")

'Generate unique list of contents of Col A
For Each c In r1
dictKey = Left(c.Text, 19)
If Not dict.Exists(dictKey) Then dict.Add Key:=dictKey, Item:=c.Value
Next c
i = 1

'if colB does NOT match, enter in Destination range
For Each c In r2
If Not dict.Exists(Left(c.Text, 19)) Then
rDest(i, 1).Value = c.Value
i = i + 1
End If
Next c
End Sub
=============================================