• 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

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

  • jack999-navic-42129-71.xlsx
    98.1 KB · Views: 6
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-navic-42129-8.xlsx
    92.2 KB · Views: 3
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

  • jack999-navic-42129-8.xlsx
    92 KB · Views: 4
If the problem is a formula in the Balance column, put in the cell G5 formula below
Code:
=G4+E5-F5
 
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-navic-42129-9.xlsx
    97.6 KB · Views: 5
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
 
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-navic-42129-9a.xlsx
    93.8 KB · Views: 3
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

  • jack999-navic-42129-71.xlsx
    117.7 KB · Views: 5
Last edited:
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))
 
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.
 
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-navic-42129-71.xlsx
    118.3 KB · Views: 2
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

  • jack999-navic-42129-71.xlsx
    117.5 KB · Views: 2
  • jack999-navic-42129-71 copy.xlsx
    118.1 KB · Views: 4
Last edited:
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-navic-42129-10.xlsx
    94 KB · Views: 2
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

  • jack999-navic-42129-10.xlsx
    95.4 KB · Views: 2
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
 
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

  • jack999-navic-42129-11.xlsx
    95.9 KB · Views: 1
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

  • jack999-navic-42129-8 (PB).xlsx
    76.3 KB · Views: 6
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)))
 
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
 
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

  • jack999-navic-42129-11a.xlsx
    96.7 KB · Views: 8
Back
Top