Excel: Unmatched Lists 1

[scousethemoose]

Hi I've been looking around the forum and cant seem to get my head around this even after trying some of the solutions.

What I want to be able to do is compare two lists and then show me the actual values not contained in the first list.

eg:

Column A
1 Apple
2 Pear
3 Peach
3 Banana

Column B
1 Apple
2 Orange
3 Peach
4 Banana
5 Pear
6 Grapfruit

Column C
1 Orange
2 Grapefruit

I can do this easily in Access with SQL but cant seem to get my head around it to do it in excel.

Any help would be greatly appreciated.

 

[GlennUK]

You can't do it with one column of formulae, you are going to need two, one helper column, and a results column.

Have your data start on row 2, so that all the formula I give you are the same for every row ... in cell C2, use this formula:

=IF(ISNA(MATCH(B2,$A$2:$A$5,0)),SUM(C1:$C$1)+1,0)

and in cell D2 use this formula:

=IF(ISNA(MATCH(ROW()-1,$C$2:$C$7,0)),"",INDEX($B$2:$B$7,MATCH(ROW()-1,$C$2:$C$7,0)))

Select C2 and D2 and copy down as far as your full list goes.

That's it.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[scousethemoose]

Thanks I'll give it a shot, and post how I get on.

 

[scousethemoose]

It works great - thankyou for your help