How to Return Differences between Two Columns?

[LilyN]

Hello,

What I'm trying to do is to return differences between two IDs. In the file, column A contains new IDs, and column B contains names associated with it. There's also a column with Old IDs.
I need:
1. to pull IDs from column A that aren't in an old column (pull the differences)
2. to pull list of names from column B that will correspond to the IDs from column A

I hope someone can help here. I tried different formulas, and conditional formatting, but for some reason it doesn't pull all the different IDs.

Thanks.

[Ochimus]

Select CF, set New Rule as
=ISERROR(MATCH(A2,$D$2:$D$10,0)) and set "Fill" as whatever colour you want.
Apply to A$2:A$10

Values in Col A not in Col D will be shaded.

Ochimus

[LilyN]

Thanks Ochimus, I guess if I use CF, then I'm having trouble deleting the CF cells/rows. I have a very large file - 50k rows and not sure how to delete these CF rows.

[FlameRetired]

I inserted an extra column between Name and Old ID

For your profiled version of Excel in cell C2 you will need to array enter this formula, fill down and across column D until you get blanks.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

=IFERROR(INDEX(A$2:A$16,SMALL(IF(ISNUMBER(MATCH($A$2:$A$16,$E$2:$E$10,0)),MATCH($A$2:$A$16,$E$2:$E$10,0)),ROWS(A$2:A2))),"")

Looks like this at my end.

	A	B	C	D	E
1	ID	Name			Old ID
2	1234567	Apples	1234567	Apples	9876543
3	2456778	Oranges	2456778	Oranges	43219056
4	4567890	Bananas	4567890	Bananas	6594032
5	345321	Pears	43219056	Avocados	1987234
6	9876543	Nectarines			11122233
7	9873456	Plums			99884455
8	2345678	Cherries			22335577
9	4567934	Pineapples			7644311
10	43219056	Avocados			10293485
11	5646870	Grapes
12	43192355	Strawberries
13	4563845	Blueberries
14	6594032	Mangos
15	3456092	Blackberries
16	10293485	Raspberries

[FlameRetired]

Please ignore my previous post. I interpreted wrong.

[wk9128]

Try Cell G2 array formula , Drag down

[CAABYYC]

G2=IFERROR(INDEX(A:A,SMALL(IF(ISERROR(MATCH($A$2:$A$16,$D:$D,)),ROW($A$2:$A$16),"/"),ROW(A1))),"") control+shift+enter, then copy to right and down