Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate entries
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. :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate entries
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. "Secret Squirrel" wrote: 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. :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate entries
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. "Secret Squirrel" wrote: 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. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Entries | Excel Discussion (Misc queries) | |||
removing duplicate entries | Excel Discussion (Misc queries) | |||
How can you identify duplicate entries in a singe column? | Excel Worksheet Functions | |||
Duplicate Entries | Excel Discussion (Misc queries) | |||
count duplicate (or, inversely, unique) entries, but based on a condition | Excel Worksheet Functions |