Duplicate entries

[Guest]

I'm trying to compare to columns to see if they have any of the same entries
but the data is not totally exact in both columns.

For example I have in column A a value of "211000" and in column B I have a
value of "211000-1". How can I have it compare the two and only look at the
first 6 characters of column B? I want to put a "1" in next to the column B
value to show that it exists in column A. Now Column A has 538 entries in it
and Column B has 24 in it. So I want it to search the entire listing in
column A and have it put a "1" next to the value in column B to show that it
does exist in column A. Hope this all makes sense.

 

[Guest]

There's two ways you can do this.
1) Select column A then select Find in the Edit menu. Type in the first 6
digits of a number in column B and click the Find Next button. The first
cell with a matching number in column A will be highlighted. If a match is
found, close the Find dialogue box and edit the number in column B. If no
match is found, a message is displayed. Close the message and change the
'Find' value to next column B number to be checked.

2) Select column A then select Filter > Auto Filter in the Data menu. This
puts a filter button (down-arrow) at the top of the column which, when
clicked, lists the contents of the column in numerical and alphabetical order
in a drop-down box which can be scrolled. This makes it easy to manually
search for a match of the numbers in column B. When you click on another cell
(e.g. to edit a matching number in column B), the drop-down box will close.
So you'll have to click the filter button again to search for the next number.

 

[Guest]

I put a list in column A from A1 to A21 containing various numbers, I Then
had a list in column B from B1 to B7 with some cells containing a number in
column A with extra digits after, for example I had 111112 in column A so I
entered 111112-568 in B1, I also entered some numbers that didn't appear
in column A, just for checking.

In C1 I entered;
=IF(COUNTIF($A$1:$A$21,(LEFT(B1,6))),1,"")

Then copy this formula down the length of your column B list, if the first 6
digits of the number in column B appear in column A then a '1' will be
displayed in column C, if not nothing will be displayed in column C.