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!
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.
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
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.
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