Matching data from 2 columns

[doohen]

I have 2 columns of data. Both should ultimately have identical data,
but both have extranious data I need to separate. These lists will be
300+ long, so by hand is taking forever. Example:
Column A Column B
12345 12344
12346 12345
12347 12346
12348 12347
12349 12349

I need a way to extract the 12344 from column B since it is not in A,
and 12348 from A since it is not in B. They do not have the same number
of entries. Any ideas? Thanks sooooo much in advance!

 

[Sloth]

I don't know how to automatically do it, but you can use conditional
formatting to highlight the ones you need to delete. It might speed things
up a bit until a smarter person tells you how to do it right.

1. Highlight Column A
2. Select Format->Conditional Formating
3. Select "Formula Is"
4. Insert
=COUNTIF($B:$B,A1)=0
as your formula
5. Select an obvious formatting like a yellow background color
6. Click Okay
7. Click Okay

All cells that don't have a matching value in column B should now be
highlighted.
Repeat the procedure on column B but use this formula instead
=COUNTIF($A:$A,B1)=0
to highlight extraneous cells in column B.

 

[Brad]

How about this
Take all the rows from Column B and add them to Column A

Sort on A

Insert a column before column A

assuming that your data starts in b6
in the new column =--(b6<>b5) - in row 5
Copy this equation all the way down

Copy - paste special (values) new column

Sort on new colum and get rid of the zeros

 

[Herbert Seidenberg]

Similar to Sloth's, but using Advanced Filter.
Let's assume your data looks like this:
Database Database
List1 List2
45 45
45 46
46 50
48 50
50 50
51 52
54 53
54 53
55 53
54
55

Criteria Criteria
Test Test
FALSE FALSE

List1 List2
45 45
45 46
46 50
50 50
54 50
54 54
55 55

1. Select the header Database, List1 and its data entries and
Insert > Name > Create > Top Row
2. Select the header List1 and its data entries and
Insert > Name > Create > Top Row
3. Select the header Criteria and the two cells below it and
Insert > Name > Create > Top Row
4. In the cell in the first column that says FALSE, enter
=COUNTIF(List2,List1)>0
5. In the adjacent FALSE cell in the second column, enter
=COUNTIF(List1,List2)>0
6. Data > Filter > Advanced Filter > Copy to another location
7. Type into List Range:
Database
8. Type into Criteria:
Criteria
9. Select a cell of your choice to Copy To:

A seven entry list with the header List1 should appear as shown above.

Repeat steps 1, 2, 3, 6, 7, 8 and 9 for List2 in the second column.

 

[doohen]

I tried your highlighting method and it ended up just highlighting the
entire column after applying. I will play around with that idea and see
if I can figure something out. Thanks so much for your input, I
appreciate the help!

 

[Sloth]

The only I can think that would cause that is if you inserted this as your
formula
=COUNTIF($B:$B,$A$1)=0
if you did, remove the $'s from the A1 reference. Other than that I don't
know what you did wrong because I used your example and got the desired
results.

 

[Herbert Seidenberg]

Once you get Sloth's highlighting method down,
you can automatically delete those cells
by copying the array to Word and bringing it back.
The conditional format will have changed to regular format.
Now you can Find that format, Replace it with a blank,
Goto > Special > Blanks
and Delete > Shift cells up

 

[doohen]

Sorry I forgot to mention some of these numbers have letters as well.
Thanks!

 

[doohen]

I am extremely green using Excel. I am unsure what the "database"
header refers to. Is that assuming the data is coming from different
workbooks, and those are the titles? (I'm too much of a knucklehead to
get the friendly Sloth highlighting method to work...) Thank you for
your input.

 

[Brad]

The method I gave you would work for alpha as well as numeric

 

[Herbert Seidenberg]

Type in the words Database and Criteria just the way they are spelled.
These are reserved words used only in Advanced Filter and when used
as shown will automatically fill in the blanks in the Filter window.
The word Test is an arbitrary filler word.
Advanced Filter is much harder to use than Sloth's method, but once
you learn it, it will come in handy in many situations.

 

[Herbert Seidenberg]

Just to make things a little harder, here is a way to do it
with letters in the data and array formulas.
This is even harder than Advanced Filter, but it is in real time.
Assuming your data is arranged like this:

List3 List4

45C 45E
45A 46C
46C 50B
48F 50F
50F 50F
51A 52C
54F 53C
54F 53F
55B 53C
54F
55B

Name List3, List4, including the blank row.
Name the following:
Set1 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,COUNTA(List3)+1))
Set2 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,COUNTA(List4)+1))
The Result1 and Result2 array formulas (CSE) are respectively:
=INDEX(List3,LARGE(IF((COUNTIF(List4,List3)>0)*Set1=0,1,Set1),Set1))
=INDEX(List4,LARGE(IF((COUNTIF(List3,List4)>0)*Set2=0,1,Set2),Set2))

Result1 Result2
55B 55B
54F 54F
54F 50F
50F 50F
46C 46C

 

[doohen]

I finally figured out what the issue was. Somehow I had a space in
front of every value in my second column of data. NOW, everything is
working fabulously! Thanks again to all who gave help. It is much
appreciated!!!