Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How to create a search that excludes results?

I am currently working on creating an invoice spreadsheet to automatically bill clients. The problem is, some clients are part of a "family". I want to create a formula that references a cell's contents (which would indicate if they were a part of a family and if so, which one) and then searches a sheet for other members of that family, excluding the original client.


So I suppose my question is, how can I create a formula to look up the contents of a cell but exclude any results that would be the contents of another cell?


Thanks!

Posted on Jul 6, 2015 2:53 PM

Reply
4 replies

Jul 6, 2015 10:27 PM in response to anothrgrnworld

Hi agw,


Seven hours without a reply in these conferences is unusual.


I get the impression that your question is unclear to others, as well as to me.


In general terms, you could use an IF statement with AND included in the conditions to return only members of a named group who were not the 'original client.'


Example: Group ('family') identification is in column C, client (individual) id is in column B, both in the "Clients" table. "Original client" is in cell B2 of the "Invoice" table. Client's Group ('family') ID is in cell B3 of the "invoice" table. The formula below. placed in every row of the "Clients" table, would flag all members of the same 'family EXCEPT the 'original client' with a Y in the column containing the formula.


=IF(AND(C=invoice::$B$3,B<>Invoice::$B$2),"Y","")


Although that answers your basic question, it does not address the unasked question of what you want to happen because of these results.


more detailed description of a sample case, and the results you expect might be of assistance.


Regards,

Barry

Jul 7, 2015 5:08 AM in response to Barry

Barry,


Thanks for your reply.


Here is a better explanation of what I'm trying to do.


I have three sheets. One is the invoice sheet, one is a client roster, and one is the schedule from which the client's event count is taken from.


-In the invoice sheet, I manually input the name of a client in A2.


-That triggers B2 on the same sheet's formula , IFERROR(COUNTIF(Table 1::A,A2),0) which populates B2 with the number of events the client is to be billed for located on the schedule sheet.


-The combination of the A2 and B2 trigger C on the same sheet's formula, IFERROR(LOOKUP(A2,ActiveStudents::$A,Rate),0) which pulls the client's billable rate located on the client roster.


-D2's formula is a simple multiplication of B2 and C2 to get the total for that client's month's bill IF(B2>0,PRODUCT(B2,C2),0)


Now it is worth mentioning that up to here, I have no problems whatsoever. The part that is tricky is this next bit....


-As I had stated previously, some of my clients are in groups or "families" and I would like to make it so then when I manually input the name of a client in A2 that is part of one of these families, A3, A4, A5, etc automatically populate with the other members of the family without obviously repeating the same clients twice. In the client roster table, I have the cells of the column family as either being the family's name, or a 0.


-In E2 of the invoice table, I have the following formula: IF(LOOKUP(A2,ActiveStudents::$A,ActiveStudents::$Family)=0,0,LOOKUP(A2,ActiveSt udents::$A,ActiveStudents::$Family))


-This is where I seem to have trouble. I want A3 to populate automatically based on the contents of cell E2 UNLESS it will populate A3 with the same contents as A2 (thus repeating the same client already billed).


So, I suppose the problem is more of what to put in A3 than anything else.


Thank you.

Jul 7, 2015 9:20 AM in response to anothrgrnworld

Hello


If I understand it correctly, you may try something like the following tables.


User uploaded file



User uploaded file



Invoice A1 Name A2 D A3 =IF(E$2=0,"",IFERROR(INDEX(Names::A,MATCH(E$2&"|"&ROW()-2+IF(ROW()-2<F$2,0,1),Names::D,0),1),"")) A4 =IF(E$2=0,"",IFERROR(INDEX(Names::A,MATCH(E$2&"|"&ROW()-2+IF(ROW()-2<F$2,0,1),Names::D,0),1),"")) A5 =IF(E$2=0,"",IFERROR(INDEX(Names::A,MATCH(E$2&"|"&ROW()-2+IF(ROW()-2<F$2,0,1),Names::D,0),1),"")) A6 =IF(E$2=0,"",IFERROR(INDEX(Names::A,MATCH(E$2&"|"&ROW()-2+IF(ROW()-2<F$2,0,1),Names::D,0),1),"")) A7 =IF(E$2=0,"",IFERROR(INDEX(Names::A,MATCH(E$2&"|"&ROW()-2+IF(ROW()-2<F$2,0,1),Names::D,0),1),"")) A8 B1 # Events B2 =IF(LEN(A2)>0,COUNTIF(Events::A,A2),"") B3 =IF(LEN(A3)>0,COUNTIF(Events::A,A3),"") B4 =IF(LEN(A4)>0,COUNTIF(Events::A,A4),"") B5 =IF(LEN(A5)>0,COUNTIF(Events::A,A5),"") B6 =IF(LEN(A6)>0,COUNTIF(Events::A,A6),"") B7 =IF(LEN(A7)>0,COUNTIF(Events::A,A7),"") B8 C1 Rate C2 =IFERROR(VLOOKUP(A2,Names::A:B,2,0),"") C3 =IFERROR(VLOOKUP(A3,Names::A:B,2,0),"") C4 =IFERROR(VLOOKUP(A4,Names::A:B,2,0),"") C5 =IFERROR(VLOOKUP(A5,Names::A:B,2,0),"") C6 =IFERROR(VLOOKUP(A6,Names::A:B,2,0),"") C7 =IFERROR(VLOOKUP(A7,Names::A:B,2,0),"") C8 D1 B*C D2 =IF(LEN(B2)>0,B2*C2,"") D3 =IF(LEN(B3)>0,B3*C3,"") D4 =IF(LEN(B4)>0,B4*C4,"") D5 =IF(LEN(B5)>0,B5*C5,"") D6 =IF(LEN(B6)>0,B6*C6,"") D7 =IF(LEN(B7)>0,B7*C7,"") D8 =SUM(D) E1 Family E2 =VLOOKUP(A2,Names::A:C,3,0) E3 E4 E5 E6 E7 E8 F1 F2 =0+SUBSTITUTE(VLOOKUP(A2,Names::A:D,4,0),E2&"|","") F3 F4 F5 F6 F7 F8



Names A1 Name A2 A A3 B A4 C A5 D A6 E A7 F A8 G B1 Rate B2 10 B3 10 B4 10 B5 15 B6 15 B7 20 B8 10 C1 Family C2 FA C3 0 C4 FC C5 FA C6 0 C7 FC C8 FA D1 D2 =C2&"|"&COUNTIF(C$2:C2,C2) D3 =C3&"|"&COUNTIF(C$2:C3,C3) D4 =C4&"|"&COUNTIF(C$2:C4,C4) D5 =C5&"|"&COUNTIF(C$2:C5,C5) D6 =C6&"|"&COUNTIF(C$2:C6,C6) D7 =C7&"|"&COUNTIF(C$2:C7,C7) D8 =C8&"|"&COUNTIF(C$2:C8,C8)



Tables are built with Numbers v2.


Good luck,

H


EDIT: added another screenshot

How to create a search that excludes results?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.