Eliminate non-matching cells

  • Thread starter Thread starter GARY
  • Start date Start date
G

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?
 
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...
 
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?
 
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 -
Click to expand...

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.
 
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.
 
A2 and B3 contain the same 19 leftmost characters so the count should
be 1 (match) not 0 (no-match).
 
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]...
 
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
Click to expand...

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
 
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
 
Back
Top