• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Correction in Array formula

jack999

Member
Sir Good Day.
Can you please help me for the following
Sir yesterday I noticed that a small error in the report (in the report2 green highlighted items in the attached file). Its summing sales with scheme while invoice number and item is same.
 

Attachments

navic

Active Member
Try new ARRAY Unique formula in the cell C4
Code:
=IFERROR(INDEX(DATA2!$B$2:$B$4000,MATCH(0,IF($B$2=DATA2!$H$2:$H$4000,COUNTIF($C$3:$C3,DATA2!$B$2:$B$4000),""),0)),"")
Another formulas
Code:
In the cell A4 ARRAY formula
=IF(C4="","",IF(ROWS(B$3:B3)>COUNTIF(DATA2!$H$2:$H$4000,$B$2),"",INDEX(DATA2!$A:$L,SMALL(IF(DATA2!$H$2:$H$4000=$B$2,ROW(DATA2!$B$2:$B$4000)),ROW(1:1)),COLUMN($C$1))))

In the cell B4 ARRAY formula
=IF(C4="","",IF(ROWS(B$3:B3)>COUNTIF(DATA2!$H$2:$H$4000,$B$2),"",INDEX(DATA2!$A:$H,SMALL(IF(DATA2!$H$2:$H$4000=$B$2,ROW(DATA2!$B$2:$B$4000)),ROW(1:1)),COLUMN($H$1))))

In the cell D4 ARRAY formula
=IF(C4="","",IF(ROWS(B$3:B3)>COUNTIF(DATA2!$H$2:$H$4000,$B$2),"",INDEX(DATA2!$A:$L,SMALL(IF(DATA2!$H$2:$H$4000=$B$2,ROW(DATA2!$B$2:$B$4000)),ROW(1:1)),COLUMN($L$1))))
 

Attachments

jack999

Member
Try new ARRAY Unique formula in the cell C4
Code:
=IFERROR(INDEX(DATA2!$B$2:$B$4000,MATCH(0,IF($B$2=DATA2!$H$2:$H$4000,COUNTIF($C$3:$C3,DATA2!$B$2:$B$4000),""),0)),"")
Another formulas
Code:
In the cell A4 ARRAY formula
=IF(C4="","",IF(ROWS(B$3:B3)>COUNTIF(DATA2!$H$2:$H$4000,$B$2),"",INDEX(DATA2!$A:$L,SMALL(IF(DATA2!$H$2:$H$4000=$B$2,ROW(DATA2!$B$2:$B$4000)),ROW(1:1)),COLUMN($C$1))))

In the cell B4 ARRAY formula
=IF(C4="","",IF(ROWS(B$3:B3)>COUNTIF(DATA2!$H$2:$H$4000,$B$2),"",INDEX(DATA2!$A:$H,SMALL(IF(DATA2!$H$2:$H$4000=$B$2,ROW(DATA2!$B$2:$B$4000)),ROW(1:1)),COLUMN($H$1))))

In the cell D4 ARRAY formula
=IF(C4="","",IF(ROWS(B$3:B3)>COUNTIF(DATA2!$H$2:$H$4000,$B$2),"",INDEX(DATA2!$A:$L,SMALL(IF(DATA2!$H$2:$H$4000=$B$2,ROW(DATA2!$B$2:$B$4000)),ROW(1:1)),COLUMN($L$1))))
Sir Good Day
Thank you for Reply
Sir one request, I have sorted my original Data2 sheet's Invoice Date as (A to Z) ascending order so how to change this formula now. Sir please help. Sorry for the trouble. I really did not think that will make trouble.
 

Attachments

navic

Active Member
If the problem is a formula in the Balance column, put in the cell G5 formula below
Code:
=G4+E5-F5
 

navic

Active Member
Try new formulas
in the cell 'A4', copy down
Code:
=IF(INDEX(DATA2!$C$2:$C$300,MATCH(1,INDEX((DATA2!$H$2:$H$300=B4)*(DATA2!$B$2:$B$300=C4),0),0))=0,"",INDEX(DATA2!$C$2:$C$300,MATCH(1,INDEX((DATA2!$H$2:$H$300=B4)*(DATA2!$B$2:$B$300=C4),0),0))
in the cell 'D4', copy down
Code:
=IF(INDEX(DATA2!$L$2:$L$300,MATCH(1,INDEX((DATA2!$H$2:$H$300=B4)*(DATA2!$B$2:$B$300=C4),0),0))=0,"",INDEX(DATA2!$L$2:$L$300,MATCH(1,INDEX((DATA2!$H$2:$H$300=B4)*(DATA2!$B$2:$B$300=C4),0),0)))
btw: My advice, implement the "InOut" helper column inside Table2, to be an integral part.
 

Attachments

jack999

Member
Now also I am getting error with my original Data. Really I am tired of. In the first line #N/A value not found. I don't no why ?
ITEM NAME
LED ADORE 9W B22 CDL 4 STAR LAMP
Op. Balance
0
DateItem NameInvoice No.DescriptionInwardOutwardBalance
#N/ATRIM CLIP ON PANEL 24W 6500K5936536523#N/A#N/A#N/A#N/A
14/07/19LED ADORE 9W B22 CDL 4 STAR LAMPHAV/1819/105SALE040
15/07/19LED ADORE 9W B22 CDL 4 STAR LAMPHAV/1819/104SALE040
15/07/19LED ADORE 9W B22 CDL 4 STAR LAMP5936542976PURCHASE3000
17/07/19LED ADORE 9W B22 CDL 4 STAR LAMP5936543661PURCHASE2000
18/07/19LED ADORE 9W B22 CDL 4 STAR LAMPHAV/1819/109SALE020
 

navic

Active Member
1st
I'm totally confused. Why didn't you attach the original file so I could see what the problem was? Give yourself some effort, don't just wait for the solution. I can not read your thoughts. Please, upload complete file with original data and delete sensitive information, (of course, if you can).
2nd
I don't have this item on the DATA2 sheet in my file, (you show in the post above).
3rd
I tried adding this item and have no problems.
 

Attachments

jack999

Member
Sir Good Day

As per your report jack999-navic-42129-7 it was good. There was only one error as follows

There was only one error that Sum the Sales figure with Scheme if invoice number or Ch.number are same (eg.Ch.9898).
* Report2 Sheet Green Highlighted item

Other part of the Report and Formula is Good

*In the Report2 Sheet, in Inward - Outward column if you can add an AND command to sum if Item name = same and Type= same may be solve the problem.


(Type is in the Data2 L column (Sale, Sales Return, Purchase, Scheme, Replacement etc..)

Sir Error in the Inward - Outward Column only, please check the pivot table

30/12/18LED ADORE NIGHT LAMP 0.5W 6500K BEARch.9898SALE
0.00​
11.00​
30/12/18LED ADORE NIGHT LAMP 0.5W 6500K BEARch.9898SCHEME
0.00​
11.00​

it should come as below

30/12/18LED ADORE NIGHT LAMP 0.5W 6500K BEARch.9898SALE
0.00​
1.00​
30/12/18LED ADORE NIGHT LAMP 0.5W 6500K BEARch.9898SCHEME
0.00​
10.00​

I try to alter the formula but I can't . I have to learn make formula.


In the attached sheet, I attached pivot tables in pivot table2 yellow highlighted part for your reference.

Sir I can't upload my original data in public because I have to change lot of customer name and address.

Hope pivot table could help you to help me
 

Attachments

Last edited:

navic

Active Member
At the moment, I don't have much time to research the problem (especially since English is not my native language).
Just briefly, does you help these two formulas below.
In the cell 'E4' and copy down
Code:
=SUMPRODUCT(DATA2!$K$2:$K$4025,($A4=DATA2!$C$2:$C$4025)*($B4=DATA2!$H$2:$H$4025)*($C4=DATA2!$B$2:$B$4025)*($D4=DATA2!$L$2:$L$4025)*($B$2=DATA2!$H$2:$H$4025)*(E$3=DATA2!$M$2:$M$4025))
In the cell 'F4' and copy down
Code:
=SUMPRODUCT(DATA2!$K$2:$K$4025,($A4=DATA2!$C$2:$C$4025)*($B4=DATA2!$H$2:$H$4025)*($C4=DATA2!$B$2:$B$4025)*($D4=DATA2!$L$2:$L$4025)*($B$2=DATA2!$H$2:$H$4025)*(F$3=DATA2!$M$2:$M$4025))
 

jack999

Member
At the moment, I don't have much time to research the problem (especially since English is not my native language).
Just briefly, does you help these two formulas below.
In the cell 'E4' and copy down
Code:
=SUMPRODUCT(DATA2!$K$2:$K$4025,($A4=DATA2!$C$2:$C$4025)*($B4=DATA2!$H$2:$H$4025)*($C4=DATA2!$B$2:$B$4025)*($D4=DATA2!$L$2:$L$4025)*($B$2=DATA2!$H$2:$H$4025)*(E$3=DATA2!$M$2:$M$4025))
In the cell 'F4' and copy down
Code:
=SUMPRODUCT(DATA2!$K$2:$K$4025,($A4=DATA2!$C$2:$C$4025)*($B4=DATA2!$H$2:$H$4025)*($C4=DATA2!$B$2:$B$4025)*($D4=DATA2!$L$2:$L$4025)*($B$2=DATA2!$H$2:$H$4025)*(F$3=DATA2!$M$2:$M$4025))
Thank you sir , Now its working good. Thank you so much for your kind help.
I spend lots of time try to correct the formula,
Warm regards.
 

jack999

Member
You're welcome.
I'm glad you solved your problem.
Sir one help can you do for me if you have time. Can we use the formula without index small as in the attached file. Without Index Small how we can use it.

=IF(ROWS(B$3:B3)>COUNTIF(DATA2!$H$2:$H$3989,$B$2),"",INDEX(DATA2!$A:$H,SMALL(IF(DATA2!$H$2:$H$3989=$B$2,ROW(DATA2!$B$2:$B$3989)),ROW(1:1)),COLUMN($H$1)))

=IF(ROWS(B$3:B3)>COUNTIF(DATA2!$H$2:$H$3989,$B$2),"",INDEX(DATA2!$A:$L,SMALL(IF(DATA2!$H$2:$H$3989=$B$2,ROW(DATA2!$B$2:$B$3989)),ROW(1:1)),COLUMN($B$1)))

=IF(ROWS(B$3:B3)>COUNTIF(DATA2!$H$2:$H$3989,$B$2),"",INDEX(DATA2!$A:$L,SMALL(IF(DATA2!$H$2:$H$3989=$B$2,ROW(DATA2!$B$2:$B$3989)),ROW(1:1)),COLUMN($L$1)))
 

Attachments

jack999

Member
Sir Good Day

If you get the time please check the following.

Thank you for your reply effort and help for me. Sir I think your these formula working good in the unsorted file. In the sorted file report showing error in the result.

Sir I have sorted my Original Data. So the report showing error as in jack999-navic-42129-71 - Copy

Both file I attached.

jack999-navic-42129-71 - Copy sorted by Invoice Date and Another jack999-navic-42129-71 is without sorted


Problems in the Report.

1. In jack999-navic-42129-71 file Report2

its summing 2 + 2 =4 in both 2 sale for the following

Please look the report2 of jack999-navic-42129-71

Jackson ElectricalsPKDLED ADORE NIGHT LAMP 0.5W 6500K BEARLHEHDDP7IN1B01085392SALEOutward

Jackson ElectricalsPKDLED ADORE NIGHT LAMP 0.5W 6500K BEARLHEHDDP7IN1B01085392SALEOutward


30/12/18LED ADORE NIGHT LAMP 0.5W 6500K BEARch.9898SALE04
-4.00​
30/12/18LED ADORE NIGHT LAMP 0.5W 6500K BEARch.9898SCHEME010
-14.00​
30/12/18LED ADORE NIGHT LAMP 0.5W 6500K BEARch.9898SALE04
-18.00​


jack999-navic-42129-71 copy after sorting Its showing totally different

Sir I don't know how to modify this formula, If you get the time please look into this.
 

Attachments

Last edited:

navic

Active Member
You seem to have a lot of conditions and I don't understand you well.
For three days I will go on my vacation.
Please don't explain much. Post a file attachment and enter the expected results. Do not enter formulas. See my file attachment.
So, do not enter formulas. Just enter the expected results based on Sheet DATA2.
This is the last chance to help you if I will know the solution.

btw: I hope that other members be included to help you (especially Excel Ninja).
 

Attachments

jack999

Member
Sorry Sir You miss understood me. I did not put any condition I just try to show my requirement. Your help is just a favour for me. If you would like to help then help me. If any of my post make you feel bad, I am so sorry. Pls. find the report without formula
 

Attachments

navic

Active Member
On the Master Sheet you have the InOut Terms database.
SALE = OUTWARD
Can you explain why one item in the same account is different?
Is this a formula error?
61859
 

navic

Active Member
I advise one worksheet for two reports.
You do the 'Party Name & Address' report by filtering.
See my idea, attached to a file.
That's from me, good luck.

REPORT 1
61868

REPORT 2
61869
 

Attachments

Peter Bartholomew

Well-Known Member
The formulas currently used within the workbook are something of a nightmare. I expect that one could develop a Power Query / Power Pivot solution but if one sticks with formula-based solutions it might be worth licensing a copy of Office 365 for this workbook.

The image below is a table that starts by filtering the data by party name and address and then creates a list of unique products featuring in the transaction with the selected party. The difference is that dynamic array versions of Excel have custom functions for filtering, sorting and selecting distinct entities whereas traditional Excel requires solutions cobbled together using a more limited set of functions.

= UNIQUE( FILTER( Transactions[ITEM NAME], (Transactions[PARTY NAME]=PartyName) * (Transactions[ADDRESS]=PartyAddress) ) )

61872
 

Attachments

navic

Active Member
Try new ARRAY formula in the cell 'A4'
Code:
=IF(COUNTIFS(DATA2!$E:$E;$B4;DATA2!$H:$H;$C4;DATA2!$B:$B;$D4)<1;"";INDEX(DATA2!C:C;SMALL(IF((DATA2!$E$2:$E$340=$B4)+(DATA2!$H$2:$H$340=$C4)+(DATA2!$B$2:$B$340=$D4)=3;ROW(DATA2!$B$2:$B$340));1)))
 

jack999

Member
Try new ARRAY formula in the cell 'A4'
Code:
=IF(COUNTIFS(DATA2!$E:$E;$B4;DATA2!$H:$H;$C4;DATA2!$B:$B;$D4)<1;"";INDEX(DATA2!C:C;SMALL(IF((DATA2!$E$2:$E$340=$B4)+(DATA2!$H$2:$H$340=$C4)+(DATA2!$B$2:$B$340=$D4)=3;ROW(DATA2!$B$2:$B$340));1)))
61954


1565111841769.png
 

navic

Active Member
Maybe it's a delimiter problem. You probably have US settings. I use Non-US settings.
My delimiter is a semicolon (if that's the problem, sorry, it's my fault I didn't change the delimiter).
Please see the attached file.
 

Attachments

Top