Simple Match - Need help

[sscornav]

Not sure why I can't get this to work.
I have 2 lists:
1) A list of all racers
2) A list of the racers that finished

I need to compare the 2 lists to produce a list of racers that did not finish. I am trying an array formula, but can't get it to work.

Any suggestions? Thanks!

Excel Workbook
	A	B	C
1	All Racers	Finished	Did Not Finish
2	Sue	Mary	Sue
3	Mary	Fred	Sally
4	Fred	Martha	Lauren
5	Sally	Jim	Grace
6	Martha	Steve
7	Jim	Harold
8	Steve
9	Harold
10	Lauren
11	Grace
Sheet1

 

[Jonmo1]

This will give the list, but not exactly as you posted it above...There will be blanks..

In C2 and filled down

=IF(COUNTIF(B$2:B$11,A2)=0,A2,"")


Hope that helps.

 

[T. Valko]

Not sure why I can't get this to work.
I have 2 lists:
1) A list of all racers
2) A list of the racers that finished

I need to compare the 2 lists to produce a list of racers that did not finish. I am trying an array formula, but can't get it to work.

Any suggestions? Thanks!

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 131px"><COL style="WIDTH: 103px"><COL style="WIDTH: 100px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">All Racers</TD><TD style="FONT-WEIGHT: bold">Finished</TD><TD style="FONT-WEIGHT: bold">Did Not Finish</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Sue</TD><TD>Mary</TD><TD>Sue</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Mary</TD><TD>Fred</TD><TD>Sally</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Fred</TD><TD>Martha</TD><TD>Lauren</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Sally</TD><TD>Jim</TD><TD>Grace</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Martha</TD><TD>Steve</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Jim</TD><TD>Harold</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Steve</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Harold</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>Lauren</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>Grace</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

What version of Excel are you using?

 

[sscornav]

Sorry, 2003. Is there a way to get it without blanks?

It's a long list. I suppose I could sort it.

 

[T. Valko]

Sorry, 2003. Is there a way to get it without blanks?

It's a long list. I suppose I could sort it.

Try this...

Book1
	A	B	C	D
1	All	Finished	Not Finished	4
2	Sue	Mary	Sue	_
3	Mary	Fred	Sally	_
4	Fred	Martha	Lauren	_
5	Sally	Jim	Grace	_
6	Martha	Steve	_	_
7	Jim	Harold	_	_
8	Steve	_	_	_
9	Harold	_	_	_
10	Lauren	_	_	_
11	Grace	_	_	_
Sheet2

Enter this formula in D1. This will return the count of non-finishers.

=SUMPRODUCT(--(ISNA(MATCH(A2:A11,B2:B7,0))))

Enter this array formula** in C2. This will return the names of the non-finishers.

=IF(ROWS(C$2:C2)>D$1,"",INDEX(A$2:A$11,SMALL(IF(ISNA(MATCH(A$2:A$11,B$2:B$7,0)),ROW(A$2:A$11)),ROWS(C$2:C2))-MIN(ROW(A$2:A$11))+1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Copy down until you get blanks

 

[sscornav]

Thanks. That seemed to work

 

[T. Valko]

Thanks. That seemed to work

You're welcome. Thanks for the feedback!