Simple IF , maybe, for names in columns

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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(INDIRECT("1:"&ROWS(rngA)))),ROWS($1:1))),"",INDEX(rngA,SMALL(IF(COUNTIF(rngB,rngA)=0,ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed
 
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,SMALL(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
 
Thanks guys. Gotta go to work, so I'll get back to ya this afternoon hopefully.

Thanks again,
 
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,
 
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
 
Back
Top