Correction in Array formula

jack999 · Jul 29, 2019

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.

navic · Jul 29, 2019

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

jack999 · Jul 30, 2019

navic said:

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.

navic · Jul 30, 2019

If the problem is a formula in the Balance column, put in the cell G5 formula below

Code:

=G4+E5-F5

jack999 · Jul 30, 2019

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

jack999 · Jul 30, 2019

Sir Please look into the matter

navic · Jul 30, 2019

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.

jack999 · Jul 30, 2019

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

Date

Item Name

Invoice No.

Description

Inward

Outward

Balance

#N/A

TRIM CLIP ON PANEL 24W 6500K

5936536523

#N/A

14/07/19

LED ADORE 9W B22 CDL 4 STAR LAMP

HAV/1819/105

SALE

0

40

15/07/19

LED ADORE 9W B22 CDL 4 STAR LAMP

HAV/1819/104

SALE

0

40

15/07/19

LED ADORE 9W B22 CDL 4 STAR LAMP

5936542976

PURCHASE

300

0

17/07/19

LED ADORE 9W B22 CDL 4 STAR LAMP

5936543661

PURCHASE

200

0

18/07/19

LED ADORE 9W B22 CDL 4 STAR LAMP

HAV/1819/109

SALE

0

20

navic · Jul 30, 2019

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.

jack999 · Jul 31, 2019

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

navic · Jul 31, 2019

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 · Jul 31, 2019

navic said:
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 · Aug 1, 2019

jack999 said:
Thank you so much for your kind help.

You're welcome.
I'm glad you solved your problem.

jack999 · Aug 2, 2019

navic said:
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)))

navic · Aug 2, 2019

jack999 said:
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 · Aug 3, 2019

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

navic · Aug 3, 2019

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

jack999 · Aug 3, 2019

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

navic · Aug 4, 2019

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 · Aug 4, 2019

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

Peter Bartholomew · Aug 4, 2019

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

jack999 · Aug 6, 2019

navic said:
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 · Aug 6, 2019

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 · Aug 6, 2019

navic said:

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 · Aug 6, 2019

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.

Correction in Array formula

Member

Attachments

Active Member

Attachments

Member

Attachments

Active Member

Member

Member

Active Member

Attachments

Member

Active Member

Attachments

Member

Attachments

Active Member

Member

Active Member

Member

Attachments

Active Member

Member

Attachments

Active Member

Attachments

Member

Attachments

Active Member

Active Member

Attachments

Well-Known Member

Attachments

Member

Active Member

Member

Active Member

Attachments