uniqe list with zero balance condition

[faizee]

Dear all
i have a following data,

S No	Date	Supplier	Debit / Credit	BILL NO	Description	Folio	Debit	Credit	each bill balance
1	25-Jun-14	Asif Traders	Credit	33	received bill	11		50000
2	25-Jun-14	Asif Traders	Debit	33	payment againts chq no. 1122	12	40000
3	05-Jul-14	ABC TRADERS	Credit	55	received bill	22		40000
4	05-Jul-14	ABC TRADERS	Debit	56	payment againts chq no. 1124	4	40000
5	06-Aug-13	Asif Brother	Credit	345	received bill	56		50000
	06-Aug-13	Asif Brother	Debit	33	payment againts chq no. 1128	22	25000

<colgroup><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

I want the unique list of suppliers (column c), where balance is zero,
means. only need unique list of suppliers where sum of credit-sum of debit of this supplier is zero

the result of this data should be
Asif brother
Asif traders

Please help
thank you

 

[OTOTO]

ABC Traders are the only supplier in this information that has a balance of zero. Right?

 

[faizee]

YES, You are right,
but I am sorry. i need where balance is not zero..

so the result would be
Asif Brother
Asif Traders

 

[OTOTO]

If I'm right, then there's a solution for you. I would set up another table. Copy and paste all your suppliers into the second column of your new table. Use the "Data Tools" to "remove duplicates" in the column.
in column 3 of your new table set up a Debit list: use =Sumif(C:C, -here name the cell that the first supplier is in in your new table- , H:H)
in column 4 of your new table set up a credit list: use the same formula as above but sum range I:I instead of H:H
in column 1 of your new table set up a balance list: each cell = the corresponding Credits - Debits.
Now in another column set up a =VLOOKUP(0, Reference your total new table cells area here, 2, FALSE)

 

[OTOTO]

Change the VLOOKUP suggestion. Rather, use another column to the right of the Credit column in the new table. Call this Debtor Creditors. formula =if(Column 1 value > 0 , Column 2 value, "")
This will list the debtors

 

[faizee]

thank you for you reply,
but i cant remove duplicates, because i need real-time result.
if there is any supplier.whose balance is not zero.. it should be in the list.. if it has zero balance it should not come in the unique list

 

[OTOTO]

Is your supplier list stagnant?

How about list the suppliers in column K
in column L drop this formula. It will show the balance for the Suppliers.

=(SUMIF(C:C,K2,H:H)-(SUMIF(C:C,K2,I:I)))

 

[OTOTO]

Set up the table reversing column L and Column K as I mentioned above so that balance is in K and supplier in L.

next you will want to apply the formula discussed on this site:

How to look up a value in a list and return multiple corresponding values - Excel

 

[OTOTO]

Set up the table reversing column L and Column K as I mentioned above so that balance is in K and supplier in L.

next you will want to apply the formula discussed on this site:

How to look up a value in a list and return multiple corresponding values - Excel

I put a "0" in cell O1 Then in column P I have pasted:

<tbody>
</tbody>

=IF(ISERROR(INDEX($L$1:$M$4,SMALL(IF($L$1:$L$4<0,ROW($L$1:$L$4)),ROW(10:10)),2)),"",INDEX($L$1:$M$4,SMALL(IF($L$1:$L$4<0,ROW($L$1:$L$4)),ROW(10:10)),2))


Works like a charm

IF the list is constantly updating and possibly adding new suppliers then you can create a list of suppliers using a formula like the one discussed on this website:

http://www.get-digital-help.com/200...-and-the-duplicates-in-excel-from-one-column/

for me, I have balance in column L and supplier in column M. In M2 and below I have: =INDEX($C$2:$C$1000, MATCH(0, COUNTIF($M$1:M1, $C$2:$C$1000), 0)) <--Array formula, and it's giving me the unique list.

 

[faizee]

thank you for your reply,
it is bit confusing for me. is it possible i sent you my excel file. and you set this formula in it
i will be thankful to you