uniqe list with zero balance condition

[faizee]

Dear all
i have a following data,


[TABLE="width: 765"]
<colgroup><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]S No[/TD]
[TD]Date[/TD]
[TD]Supplier[/TD]
[TD]Debit / Credit[/TD]
[TD]BILL NO[/TD]
[TD]Description[/TD]
[TD]Folio[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]each bill balance[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]25-Jun-14[/TD]
[TD]Asif Traders[/TD]
[TD]Credit[/TD]
[TD]33[/TD]
[TD]received bill[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]25-Jun-14[/TD]
[TD]Asif Traders[/TD]
[TD]Debit[/TD]
[TD]33[/TD]
[TD]payment againts chq no. 1122[/TD]
[TD]12[/TD]
[TD]40000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]05-Jul-14[/TD]
[TD]ABC TRADERS[/TD]
[TD]Credit[/TD]
[TD]55[/TD]
[TD]received bill[/TD]
[TD]22[/TD]
[TD][/TD]
[TD]40000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]05-Jul-14[/TD]
[TD]ABC TRADERS[/TD]
[TD]Debit[/TD]
[TD]56[/TD]
[TD]payment againts chq no. 1124[/TD]
[TD]4[/TD]
[TD]40000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]06-Aug-13[/TD]
[TD]Asif Brother[/TD]
[TD]Credit[/TD]
[TD]345[/TD]
[TD]received bill[/TD]
[TD]56[/TD]
[TD][/TD]
[TD]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]06-Aug-13[/TD]
[TD]Asif Brother[/TD]
[TD]Debit[/TD]
[TD]33[/TD]
[TD]payment againts chq no. 1128[/TD]
[TD]22[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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]

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

Sure.

Email me @ steventotoole@gmail.com

 

[Aladin Akyurek]

Why is column J empty? Can we have there the calculation 'sum of credit-sum of debit'?

 

[OTOTO]

Good Q

 

[OTOTO]

Good Q

Why is it that there are multiple suppliers for a bill number?

 

[OTOTO]

Faize, I shot you the workbook back with my solution workbook on sheet 2. Let me know if you have any questions.

 

[Aladin Akyurek]

Dear all
i have a following data,


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



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

Let A:J house the above exbibit, with J empty.

K:L houses the required processing...

[TABLE="width: 224"]
<COLGROUP><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4750" width=134><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 5859" width=165><TBODY>[TR]
[TD="class: xl65, width: 134, bgcolor: transparent"]Supplier Balance[/TD]
[TD="class: xl65, width: 165, bgcolor: transparent"]Unique supplier list[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10000[/TD]
[TD="class: xl65, bgcolor: transparent"]Asif Traders[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10000[/TD]
[TD="class: xl65, bgcolor: transparent"]Asif Brother[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]25000[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]25000[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

K2, just enter and copy down:

=SUMIFS($I$2:$I$7,$C$2:$C$7,C2,$D$2:$D$7,"credit")-SUMIFS($H$2:$H$7,$C$2:$C$7,C2,$D$2:$D$7,"debit")

L2, control+shift+enter, not just enter:

=IFERROR(INDEX($C$2:$C$7,SMALL(IF(FREQUENCY(IF($C$2:$C$7<>"",IF(1-($K$2:$K$7=0),
  MATCH($C$2:$C$7,$C$2:$C$7,0))),ROW($C$2:$C$7)-ROW($C$2)+1),
  ROW($C$2:$C$7)-ROW($C$2)+1),ROWS(L$1:L1))),"")

 

[shahfahad]

Just Enter:

=IF(SUMIF($C$2:$C$7,C2,$H$2:$H$7)-SUMIF($C$2:$C$7,C2,$I$2:$I$7)=0,"",INDEX($C$2:$C$7,SMALL(IF(SUMIF($C$2:$C$7,C2,$H$2:$H$7)-SUMIF($C$2:$C$7,C2,$I$2:$I$7)=0,ROW($C$2:$C$7)-ROW($C$2)+1),ROWS(M2:M2))))

This one works fine for me. The names shown are of suppliers for which balance is remaining.

 

[Oeldere]

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 50000
2 25-jun-14 Asif Traders Debit 33 payment againts chq no. 1122 12 40000 -40000
3 5-jul-14 ABC TRADERS Credit 55 received bill 22 40000 40000
4 5-jul-14 ABC TRADERS Debit 56 payment againts chq no. 1124 4 40000 -40000
5 6-aug-13 Asif Brother Credit 345 received bill 56 50000 50000
6-aug-13 Asif Brother Debit 33 payment againts chq no. 1128 22 25000 -25000

G2

=if(H2="",I2,-H2)

and drag down.

after that make a pivot table of this data and use column B (name) as row and column G as value (sum)

 

[Fazza]

hi, faizee

Here is a non-formula (and non-code) solution: a query table. It will readily handle huge amounts of data. It just needs a refresh to update. This can be set to be automatic.

Save your file. ALT-D-D-N to start the wizard, Excel files, OK, browse & select your Excel file, OK. If you get a message about no visible tables OK to that and then via options select 'system tables'. OK. See your worksheet name/s. Select the one with data and follow the wizard to the end selecting the option to edit in MS Query. Via the SQL icon replace the text you see by that below, changing YourWorksheetName to suit your actual worksheet name. OK to enter the SQL (and also OK to any message about not being able to graphically show ...), see the results, via the 'open door' icon return the results to a worksheet.

regards, Fazza

SELECT DISTINCT Supplier
FROM (
SELECT Supplier, SUM(MyValue) AS [Balance]
FROM (
SELECT Supplier, CDbl(Credit) AS [MyValue]
FROM [YourSheetName$]
WHERE Credit Is Not Null
UNION ALL
SELECT Supplier, -CDbl(Debit) AS [MyValue]
FROM [YourSheetName$]
WHERE Debit Is Not Null)
GROUP BY Supplier
HAVING SUM(MyValue) = 0)

PS, With a similar approach a pivot table solution is available -without adding helper columns to the source data. It can be a fraction simpler even.

 

[Fazza]

A slight variation that is a little simpler.

SELECT Supplier
FROM (
SELECT Supplier, -Debit AS [Value]
FROM [YourSheetName$]
UNION ALL
SELECT Supplier, Credit
FROM [YourSheetName$])
WHERE Value Is Not Null
GROUP BY Supplier
HAVING SUM(Value)=0