|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
August 27th, 2008, 12:35 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple IF , maybe, for names in columns
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 
|
|
#2
August 27th, 2008, 01:02 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simple IF , maybe, for names in columns
=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
|
|
#3
August 27th, 2008, 01:02 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simple IF , maybe, for names in columns
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  3 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
|
|
#4
August 27th, 2008, 01:15 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simple IF , maybe, for names in columns
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 3 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
|
|
#5
August 28th, 2008, 02:10 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simple IF , maybe, for names in columns
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 3 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
|
|
#6
August 28th, 2008, 03:25 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simple IF , maybe, for names in columns
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,
|
|
#7
August 28th, 2008, 05:12 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simple IF , maybe, for names in columns
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,
|
|
#8
August 28th, 2008, 06:51 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simple IF , maybe, for names in columns
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
