need help with Creating a List based on Multiple criterias

[Stardown]

Hi everyone!

I have a series of data in columns A:C
- Column A: reference to 1 of 2 companies (cie1, cie2)
- Column B: the ID for list of supplier accounts, all entries are unique (supplier 1, supplier 2, etc...)
- Column C: the balance for each supplier's account (an amount in $)

The table looks like this:
A1=cie1; B1=supplier 1; C1=500$
A2=cie2; B2=supplier 2; C2=-500$
A3=cie1; B3=supplier 3; C3=0$
A4=cie2; B4=supplier4; C4=250$

I would like to build a dynamic list based on 2 criterias:
Criteria 1: Account is above 0
Criteria 2: Account is supplying cie1

So far I've managed to build dynamic lists that show all the suppliers with either a positive or negative account... using an array formula and a countif() like this:

D1=COUNTIF(table1[BALANCE],">0"); result is 2

=IF(ROWS(D$2:D2)>A$1,"",INDEX(A:A,SMALL(IF(table1[BALANCE]>0,ROW(table1)),ROWS(D$2:D2)))); result is an array of supplier 1 and supplier 4 (supplying different companies.)

but I don't know how to sort it so it only shows the suppliers for a specific company (using a second criteria.) I've tried nesting IF() formulas and AND() formulas to get the SMALL() portion to select only the data that meets both criterias, to no avail.

Can someone help?

 

[kchiba]

Did you try to filter the data?

 

[Somendra Misra]

@Stardown
Hi...

Try considering Kchiba method. But if you seriously need formula output, have a look of uploaded file.

See if this is of any help. Do come back if you have any issue.

Regards!

 

[Somendra Misra]

=IF(ROWS(D$2:D2)>A$1,"",INDEX(A:A,SMALL(IF(table1[BALANCE]>0,ROW(table1)),ROWS(D$2:D2)))); result is an array of supplier 1 and supplier 4 (supplying different companies.)

The Index portion wll return value from colunm A. Row number will be {1,4} as per the IF condition. So you will get first small as 1 so it is returning supplier 1 and than second small i.e. 4 so supplier 4.

Kindly go through my file, in that you will see how to use INDEX and SMALL to retrieve data.

Regards!

 

[Stardown]

@kchiba: I am not sure what you are suggesting. I know how to filter data based on my criterias in an excel table using the header filters, but I do not know how to reference the information found in the table to create a new table with only the filtered results. How would that work?

@Somendra Misra: I've taken a quick peak at your solution. It is very elegantly presented and easy to understand. It also seems to be perfect for my uses. I'll take a few hours tomorrow to implement it in my database and try to "break it" any way I can with the data i've got. I'll post another reply with my results. I'll also work backwards and analyze the IFERROR function that you used... first time I've seen it!

Thanks a lot!

 

[kchiba]

Hi,

You just copy/paste the range after you have filtered it. Only the visible data will get copied

 

[Abhijeet R. Joshi]

Use the Advanced filter option:

Copy your headers to some other place on the worksheet, now below the recently copied headers update the criteria as per the headers(Example: filtering the company as cie1 and account <>0 mention the same in the row below the new headers) and select copy to another location and select the desired location..
Hope this helps..