Okay, I was too quick there. See corrections below. 2013/2/2 Johnny Rosenberg <[email protected]>: > 2013/2/2 John Meyer <[email protected]>: >> I have two columns. Column A displays the logon names being used and >> Column B contains all possible logons (technically it's on another sheet, >> but I'll simplify). What I want to appear on column C is a list of all >> logons that are not being used. For instance: >> >> COLUMNA: >> >> Frank >> Harry >> George >> >> COLUMNB >> Frank >> Harry >> Jason >> George >> Steven >> >> >> So what I want to appear in column c is: >> >> Jason >> Steven > > I did some further testing and came up with an answer that requires > several columns (you can always hide those you don't want to see): > > So in column A, enter those three names, starting at A1. > > In column B, enter those five names, starting at B1. > > In C1: > =IF(B1="";"";IF(ISNA(MATCH(B1;$A$1:$A$10000;0));1;0)) > > In D1: > =MATCH(1;$C$1:$C$40) > In D2: > =MATCH(1;INDIRECT(ADDRESS(D1+1;3)):$C$40)+D1
Won't work. Correction: D1: =MATCH(1;$C$1:$C$40;0) D2: =MATCH(1;INDIRECT(ADDRESS(D1+1;3)):$C$40;0)+D1 In both cases, I somehow forgot the last parameter in MATCH: 0. MATCH(What;Where;Type) Omitting Type → Type=1 which is not good in this case. Type=0 is what's needed. I hope I didn't confuse you too much… Johnny Rosenberg > > > In E1: > =IF(ISNA(D1);"";INDIRECT(ADDRESS(D1;2))) > > Now copy C1 down as far as you want (max C10000, or you need to edit > the formula slightly). > Copy D2 down as far as you want (max C10000 if you don't to the > appropriate editing if C1). > Copy E1 down, the same way as C1. > Hide columns C and D. > Done. Clumsy, but done… > > > Johnny Rosenberg -- For unsubscribe instructions e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
