I have a column of 40 names (A3:A43)
In column B, I have only 10 of those names
I'd like column C to show what names are not listed in column B ( the other
30 )

Much thanks,

Steve

=IF(ISERR(SMALL(IF(COUNTIF(rngB,rngA)=0,ROW(INDIRE CT("1:"&ROWS(rngA)))),ROWS($1:1))),"",INDEX(rngA,S MALL(IF(COUNTIF(rngB,rngA)=0,ROW(INDIRECT("1:"&ROW S(rngA)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed


"Steve" wrote:

I have a column of 40 names (A3:A43)
In column B, I have only 10 of those names
I'd like column C to show what names are not listed in column B ( the other
30 )

Much thanks,

Steve

One way

Assume the 10 names in col B is in B3:B12
In C3:
=IF(COUNTIF(B$3:B$12,A3),"",ROWS($1:1))

In D3:
=IF(ROWS($1:1)COUNT(C$3:C$43),"",INDEX(A$3:A$43,S MALL(C$3:C$43,ROWS($1:1))))
Copy C3:D3 down to the last row of data in col A, ie down to D43. Col D
returns the required list of names in col A not found in col B, all neatly
packed at the top

P/s: A3:A43 is 41 cells, not 40
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"Steve" wrote:
I have a column of 40 names (A3:A43)
In column B, I have only 10 of those names
I'd like column C to show what names are not listed in column B ( the other
30 )

Much thanks,

Steve

Thanks guys. Gotta go to work, so I'll get back to ya this afternoon hopefully.

Thanks again,

"Max" wrote:

One way

Assume the 10 names in col B is in B3:B12
In C3:
=IF(COUNTIF(B$3:B$12,A3),"",ROWS($1:1))

In D3:
=IF(ROWS($1:1)COUNT(C$3:C$43),"",INDEX(A$3:A$43,S MALL(C$3:C$43,ROWS($1:1))))
Copy C3:D3 down to the last row of data in col A, ie down to D43. Col D
returns the required list of names in col A not found in col B, all neatly
packed at the top

P/s: A3:A43 is 41 cells, not 40
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"Steve" wrote:
I have a column of 40 names (A3:A43)
In column B, I have only 10 of those names
I'd like column C to show what names are not listed in column B ( the other
30 )

Much thanks,

Steve

Thanks, It worked great.
One question though. I'm getting some #'s in the first column, and zeros in
the other. See below. Just asthetically curious.

Scott
Bill S
Kurt
John H
Ronnie
6 Sam
Tony
Antho
Pinder
10 Rick S
11 Randy
Maria
13 Rick T
Joe K
Cyndee
16 Tore
17 0
18 0
19 0
20 0
0

Thanks again,


"Max" wrote:

One way

Assume the 10 names in col B is in B3:B12
In C3:
=IF(COUNTIF(B$3:B$12,A3),"",ROWS($1:1))

In D3:
=IF(ROWS($1:1)COUNT(C$3:C$43),"",INDEX(A$3:A$43,S MALL(C$3:C$43,ROWS($1:1))))
Copy C3:D3 down to the last row of data in col A, ie down to D43. Col D
returns the required list of names in col A not found in col B, all neatly
packed at the top

P/s: A3:A43 is 41 cells, not 40
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"Steve" wrote:
I have a column of 40 names (A3:A43)
In column B, I have only 10 of those names
I'd like column C to show what names are not listed in column B ( the other
30 )

Much thanks,

Steve

Think you might have some blank cells within col A's data

Try this revised criteria formula for col C

In C3:
=IF(A3="","",IF(COUNTIF(B$3:B$12,A3),"",ROWS($1:1) ))
Copy C3 down to C43
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400, Files:358, Subscribers:55
xdemechanik
---
"Steve" wrote in message
...
Thanks, It worked great.
One question though. I'm getting some #'s in the first column, and zeros
in
the other. See below. Just asthetically curious.

Scott
Bill S
Kurt
John H
Ronnie
6 Sam
Tony
Antho
Pinder
10 Rick S
11 Randy
Maria
13 Rick T
Joe K
Cyndee
16 Tore
17 0
18 0
19 0
20 0
0

Thanks again,

Yep, I did. And that was a nice fix.

Thanks again so much,

Steve

"Max" wrote:

Think you might have some blank cells within col A's data

Try this revised criteria formula for col C

In C3:
=IF(A3="","",IF(COUNTIF(B$3:B$12,A3),"",ROWS($1:1) ))
Copy C3 down to C43
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400, Files:358, Subscribers:55
xdemechanik
---
"Steve" wrote in message
...
Thanks, It worked great.
One question though. I'm getting some #'s in the first column, and zeros
in
the other. See below. Just asthetically curious.

Scott
Bill S
Kurt
John H
Ronnie
6 Sam
Tony
Antho
Pinder
10 Rick S
11 Randy
Maria
13 Rick T
Joe K
Cyndee
16 Tore
17 0
18 0
19 0
20 0
0

Thanks again,

Welcome, Steve
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500, Files:358, Subscribers:55
xdemechanik
---
"Steve" wrote in message
...
Yep, I did. And that was a nice fix.

Thanks again so much,

Steve