Column A is 73 characters wide and it has 23,628 rows.
Coumn B is 44 characters wide and it has 2,733 rows.

How can I eliminate the cells in Column A whose 16 left-most characters
do NOT match the 16 left-most characters in Column B?

You could do it with an array formula, but I think it would be quicker to use a
couple of helper columns.

In c1, put the truncated stuff from column B:
=left(b1,16)
and drag down

Now in d1, put this formula:
=isnumber(match(left(a1,16),c:c,0))

Then filter on on column D to show the Falses. Delete those visible rows and
delete the helper columns.



GARY wrote:

Column A is 73 characters wide and it has 23,628 rows.
Coumn B is 44 characters wide and it has 2,733 rows.

How can I eliminate the cells in Column A whose 16 left-most characters
do NOT match the 16 left-most characters in Column B?

--

Dave Peterson

Hi Dave,

In c1, how far down does =left(b1,16) get dragged? (23,628 rows or
2,733 rows)
In d1, how far down does =isnumber(match(left(a1,16),c:c,0)) get
dragged? ( (23,628 rows or 2,733 rows)

=left(b1,16)
needs to match the number of rows in column B.

=isnumber(match(left(a1,16),c:c,0))
needs to match the number of rows in column A.

GARY wrote:

Hi Dave,

In c1, how far down does =left(b1,16) get dragged? (23,628 rows or
2,733 rows)
In d1, how far down does =isnumber(match(left(a1,16),c:c,0)) get
dragged? ( (23,628 rows or 2,733 rows)

--

Dave Peterson

Dave,

I didn't get the results I expected.

Here's what I did:

In C1, I entered: =left(b1,16)
and copied that formula and pasted it in C1 thru C2733.

In D1, I entered: =isnumber(match(left(a1,16),c:c,0))
and copied that formula and pasted it in D1 thru D23628.

I clicked on column D's heading, clicked on DATA then on AUTOFILTER,
clicked on the arrow in D1 and selected "False" from the drop-down
list.

I clicked Column D, clicked on EDIT/GO TO/SPECIAL/VISIBLE CELLS ONLY
and deleted the "helper" columns (C and D).

But column A still contains all 23,628 cells and column B still
contains all 2,733 cells.

What did I do wrong?

Did you copy and paste those visible cells to a new location?

After you do that, you can delete columns A:D.

Actually, I'd paste to a new worksheet and keep my old stuff there--just in case
I needed it later. It's always easier to delete than recreate. (Well,
usually!)



GARY wrote:

Dave,

I didn't get the results I expected.

Here's what I did:

In C1, I entered: =left(b1,16)
and copied that formula and pasted it in C1 thru C2733.

In D1, I entered: =isnumber(match(left(a1,16),c:c,0))
and copied that formula and pasted it in D1 thru D23628.

I clicked on column D's heading, clicked on DATA then on AUTOFILTER,
clicked on the arrow in D1 and selected "False" from the drop-down
list.

I clicked Column D, clicked on EDIT/GO TO/SPECIAL/VISIBLE CELLS ONLY
and deleted the "helper" columns (C and D).

But column A still contains all 23,628 cells and column B still
contains all 2,733 cells.

What did I do wrong?

--

Dave Peterson

Dave,

Shouldn't I be click "TRUE"? (Then, the entries in column B seem to be
the ones that match those in column A).

Show True if you want to copy and paste the visible cells.

Show False if you want to delete those visible cells.

GARY wrote:

Dave,

Shouldn't I be click "TRUE"? (Then, the entries in column B seem to be
the ones that match those in column A).

--

Dave Peterson

Hi Gary,

How often do you have to do this?

Regards,
Bernd

I have three speadsheets. In each spreadsheet:

Col A has about 25,000 cells (len = 73).
Col B has about 3,000 cells (len = 44).

I need to determine, using the 16 left-most characters in Col A and Col
B, which of the cells in Col A "match" the cells in Col B.

Maybe I should re-state the situation.

I have three spreadsheets.

In each spreadsheet:

Col A has about 25,000 cells (len = 73).
Col B has about 3,000 cells (len = 44).

How can I determine, using the 16 leftmost characters in Col A and Col
B, which of the cells in Col A "match" the cells in Col B?

Didn't the follow up to your click the True question help?

GARY wrote:

Maybe I should re-state the situation.

I have three spreadsheets.

In each spreadsheet:

Col A has about 25,000 cells (len = 73).
Col B has about 3,000 cells (len = 44).

How can I determine, using the 16 leftmost characters in Col A and Col
B, which of the cells in Col A "match" the cells in Col B?

--

Dave Peterson

Hi Dave,

I still must be doing something wrong.

Maybe if you will re-give me exact, step-by-step instructions I can
follow them more carefully.

Thanks,

Gary

You have this:

Col A has about 25,000 cells (len = 73).
Col B has about 3,000 cells (len = 44).

In column C (25000 cells), put the first 16 characters of column A:
=left(a1,16)
and drag down 25000 cells

In column D (3000) cells, put the first 16 characters of column B.
=left(b1,16)
and drag down 3000 cells

In column E (25000) cells
put this formula
=isnumber(match(c1,d:d,0))

Now filter and show only the trues or falses.

You can delete the mismatches by showing falses
you can keep the matches by copying the visible cells (in column A) and pasting
them to a new sheet.

GARY wrote:

Hi Dave,

I still must be doing something wrong.

Maybe if you will re-give me exact, step-by-step instructions I can
follow them more carefully.

Thanks,

Gary

--

Dave Peterson

Hi Gary,

I suggest to take this macro:
Option Explicit

Sub compare_substrings()
Dim coll As New Collection
Dim lr As Long, lidx As Long

On Error Resume Next
'first collect comparison values from column B
lr = 1
Do While Not IsEmpty(Cells(lr, 2))
coll.Add 0, "X" & Left(Cells(lr, 2).Text, 16)
lr = lr + 1
Loop

'then test all values in column A
lr = 1
Do While Not IsEmpty(Cells(lr, 1))
Err.Clear
lidx = coll("X" & Left(Cells(lr, 1).Text, 16))
If Err.Number < 0 Then
Cells(lr, 1).Interior.ColorIndex = 3 'red
'Cells(lr, 1).Delete Shift:=xlUp 'delete cell and shift others
up
Else
Cells(lr, 1).Interior.ColorIndex = 43 'lime
End If
lr = lr + 1
Loop

End Sub

This test version highlights your cells. Uncomment the delete command
to eliminate cells which do not match (and comment the line above that
one...)

HTH,
Bernd