# Correction in Array formula

#### jack999

##### Member
Sir Good Day.
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

• 98.1 KB Views: 6

#### 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

• 92.2 KB Views: 3

#### 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

• 92 KB Views: 4

#### navic

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

#### jack999

##### Member
Sir not in the balance column. It is in the inward outward column. Sir please see the attached file.

#### jack999

##### Member
Sir Please look into the matter

#### 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

• 97.6 KB Views: 5

#### 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

• 93.8 KB Views: 3

#### 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/18 LED ADORE NIGHT LAMP 0.5W 6500K BEAR ch.9898 SALE 0.00​ 11.00​ 30/12/18 LED ADORE NIGHT LAMP 0.5W 6500K BEAR ch.9898 SCHEME 0.00​ 11.00​

it should come as below

 30/12/18 LED ADORE NIGHT LAMP 0.5W 6500K BEAR ch.9898 SALE 0.00​ 1.00​ 30/12/18 LED ADORE NIGHT LAMP 0.5W 6500K BEAR ch.9898 SCHEME 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

• 117.7 KB Views: 5
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.

#### navic

##### Active Member
Thank you so much for your kind help.
You're welcome.

#### jack999

##### Member
You're welcome.
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

• 118.3 KB Views: 2

#### navic

##### Active Member
Without Index Small how we can use it.
Can you tell me the reason why you don't want to use the formula I gave you?
If you still want to avoid this formula, then try the INDEX/AGGREGATE formula.
You have an example here and here.

#### 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 Electricals PKD LED ADORE NIGHT LAMP 0.5W 6500K BEAR LHEHDDP7IN1B010 8539 2 SALE Outward

 Jackson Electricals PKD LED ADORE NIGHT LAMP 0.5W 6500K BEAR LHEHDDP7IN1B010 8539 2 SALE Outward

 30/12/18 LED ADORE NIGHT LAMP 0.5W 6500K BEAR ch.9898 SALE 0 4 -4.00​ 30/12/18 LED ADORE NIGHT LAMP 0.5W 6500K BEAR ch.9898 SCHEME 0 10 -14.00​ 30/12/18 LED ADORE NIGHT LAMP 0.5W 6500K BEAR ch.9898 SALE 0 4 -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

• 117.5 KB Views: 2
• 118.1 KB Views: 4
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

• 94 KB Views: 2

#### 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

• 95.4 KB Views: 2

#### 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?

#### 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

REPORT 2

#### Attachments

• 95.9 KB Views: 1

#### 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) ) )

#### Attachments

• 76.3 KB Views: 4

#### jack999

##### 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
View attachment 61868

REPORT 2
View attachment 61869
why the date coming 30-12-2137 in the above report. Somebody can tell me?

#### 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)))``

#### 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

• 96.7 KB Views: 2