Correction in Array formula

jack999 · Jul 15, 2019

as per this forum rule

Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.

This is not a duplicate post

HI
Sirs, I am getting error in Report1, Report2 sheets when I am deleting some rows from in Data2 data sheet. How to solve this problem. Second in the report file Sumifs calculate purchase 2 times when the invoice number is same. (Highlighted rows in the Report2 sheet). Third in the balance column quantity last balance quantity is showing until down.How can we hide balance quantity if there is no value in E and F. Can someone help me .

navic · Jul 15, 2019

Hi jack999
I wanted to answer your previous thread, but he was locked. I do not know why (probably the moderator decided it was inappropriate).
In this thread you have set the *.xlsm format attachment. Why did you lock access to VBE?
It would be nice if in your questions, set the format *.xlsb (if the file contains VBA macros).
Of course, it should allow access to VBE (it is not advisable to hide VBA macros).

jack999 · Jul 15, 2019

navic said:
Hi jack999
I wanted to answer your previous thread, but he was locked. I do not know why (probably the moderator decided it was inappropriate).
In this thread you have set the *.xlsm format attachment. Why did you lock access to VBE?
It would be nice if in your questions, set the format *.xlsb (if the file contains VBA macros).
Of course, it should allow access to VBE (it is not advisable to hide VBA macros).

First of all thank you for your reply. Sorry sir, I did not lock purposely the question was based on formula so that I did not remember to open it. I would be happy if you will. Here I am attached open xlsb file. Hoping you will give a reply. Data2 sheet contains Macro for drop down list.

navic · Jul 15, 2019

Try on "Report2" Sheet

Code:

Cell A4
=IFERROR(INDEX(DATA2!$C$2:$C$100,MATCH(C4,DATA2!$B$2:$B$100,0)),"")

Cell B4
=IFERROR(INDEX(DATA2!$H$2:$H$100,MATCH(C4,DATA2!$B$2:$B$100,0)),"")

ARRAY formula in Cell C4
=IFERROR(INDEX(DATA2!$B$2:$B$100,MATCH(0,IF($B$2=DATA2!$H$2:$H$100,COUNTIF($C$3:$C3,DATA2!$B$2:$B$100),""),0)),"")

Cell D4
=IFERROR(INDEX(DATA2!$L$2:$L$100,MATCH(C4,DATA2!$B$2:$B$100,0)),"")

jack999 · Jul 16, 2019

navic said:

Try on "Report2" Sheet

Code:

Cell A4
=IFERROR(INDEX(DATA2!$C$2:$C$100,MATCH(C4,DATA2!$B$2:$B$100,0)),"")

Cell B4
=IFERROR(INDEX(DATA2!$H$2:$H$100,MATCH(C4,DATA2!$B$2:$B$100,0)),"")

ARRAY formula in Cell C4
=IFERROR(INDEX(DATA2!$B$2:$B$100,MATCH(0,IF($B$2=DATA2!$H$2:$H$100,COUNTIF($C$3:$C3,DATA2!$B$2:$B$100),""),0)),"")

Cell D4
=IFERROR(INDEX(DATA2!$L$2:$L$100,MATCH(C4,DATA2!$B$2:$B$100,0)),"")

Thanks you so much for your help. Sir the Balance column can you provide a formula to (-) for Balance column if there is no value in E and F.

Regards

jack999 · Jul 16, 2019

Sir there is getting error when I adding more data on the worksheet different items shows in the report sheet. Can you please check. Sir please check the new attached workbook. Actually when I apply the formula to my original data i got error message so tested with this attached workbook also. Please help me

Thanks and regards

navic · Jul 16, 2019

Balance column can you provide a formula to (-)

If I understood you well, try it

Code:

Cell E4 copy down
=IF(OR(B4="",C4=""),"",SUMIFS(DATA2!K:K,DATA2!H:H,REPORT2!B4,DATA2!B:B,REPORT2!C4,DATA2!S:S,"Inward"))

Cell F4 copy down
=IF(OR(B4="",C4=""),"",SUMIFS(DATA2!K:K,DATA2!H:H,REPORT2!B4,DATA2!B:B,REPORT2!C4,DATA2!S:S,"Outward"))

Cell G4 only
=IF(($D$2+E4-F4)<0,E4+F4,$D$2+E4-F4)

Cell G5 copy down
=IFERROR(IF((G4+E5-F5)=G4,"",G4+E5-F5),"")

Hope it helped.

navic · Jul 16, 2019

jack999 said:
there is getting error when I adding more data on the worksheet different items shows in the report sheet.

I do not understand where the error. Which cell, column or row?

navic · Jul 16, 2019

As far as I can see the main criterion is in cell B2.
You have to decide which data you want to return as a result.
Please manually enter two result variants (sheets Report2 and Report3) into the attached workbook.

jack999 · Jul 16, 2019

navic said:
As far as I can see the main criterion is in cell B2. You have to decide which data you want to return as a result. Please manually enter two result variants (sheets Report2 and Report3) into the attached workbook.

navic said:
As far as I can see the main criterion is in cell B2.
You have to decide which data you want to return as a result.
Please manually enter two result variants (sheets Report2 and Report3) into the attached workbook.

Sir sorry for the disturbance. Did you check the TESTED.xlsm,

(In Tested.xlsm) in which I put all your given formula in Report2 as you said and I Select LED ADORE NIGHT LAMP 0.5W 6500K RHINO in B2. But in the result come as below. Its showing Rhino,RABBIT,PENGUIN,TIGER ETC..

ITEM NAME

LED ADORE NIGHT LAMP 0.5W 6500K RHINO

Op. Balance

0

Date

Item Name

Invoice No.

Description

Inwards

Outwards

Balance

16/11/18

LED ADORE NIGHT LAMP 0.5W 6500K RABBIT

5622218218

PURCHASE

12.00

0.00

12.00

10/12/19

LED ADORE NIGHT LAMP 0.5W 6500K BEAR

CH.1738

SALE

0.00

1.00

11.00

04/01/19

LED ADORE NIGHT LAMP 0.5W 6500K BEAR

CH.1743

SALE

0.00

1.00

10.00

12/12/18

LED ADORE NIGHT LAMP 0.5W 6500K RHINO

CH.6001

SALE

0.00

1.00

9.00

21/12/18

LED ADORE NIGHT LAMP 0.5W 6500K BEAR

CH.1903

SALE

0.00

2.00

7.00

04/01/19

LED ADORE NIGHT LAMP 0.5W 6500K BEAR

CH.1910

SALE

0.00

1.00

6.00

27/01/19

LED ADORE NIGHT LAMP 0.5W 6500K RHINO

CH.6005

SALE

0.00

2.00

4.00

04/01/19

LED ADORE NIGHT LAMP 0.5W 6500K PENGUIN

HIL/1819/38

SALE

0.00

1.00

3.00

24/01/19

LED ADORE NIGHT LAMP 0.5W 6500K TIGER

HIL/1819/61

SALE

0.00

2.00

1.00

28/06/19

LED ADORE NIGHT LAMP 0.5W 6500K TENNIS

HAV/1819/85

SALE

0.00

1.00

-

Sir what is the problem. I thought you may check the TESTED.xlsm, report sheet2. Is this only I am getting this? Where is the problem. I can't understand. Sir will you please check

Thankyou for your help and your time

navic · Jul 16, 2019

This is because I have based other formulas on column C. Column C returns the Invoice number.
Now I've changed the formula in column B.
Hope it helped.

jack999 · Jul 17, 2019

navic said:
This is because I have based other formulas on column C. Column C returns the Invoice number.
Now I've changed the formula in column B.
Hope it helped.

Sir Good Day

Are you there sir,

Sorry for the disturbance.

There is one more problem I found in the Report2, Sir please look into the Report2 last item which I highlighted.
I added some date Row99 to 107 and in the Data2 sheet and Report2 its calculate 7 qty of other item for outward.
As per Ch.9898 there is one Rhino sale. but its sum 2nos.

Sir Kindly look one more time for me.

Sir I got it your formula from your 3rd reply
is working out there. Even though one more time sir please check again. Thank you for your valuable time spend for me. Very much thank you. Only you give me a answer for my question.
God Bless.

=IF(OR(B4="",C4=""),"",SUMIFS(DATA2!K:K,DATA2!H:H,REPORT2!B4,DATA2!B:B,REPORT2!C4,DATA2!S:S,"Inward"))

Cell F4 copy down
=IF(OR(B4="",C4=""),"",SUMIFS(DATA2!K:K,DATA2!H:H,REPORT2!B4,DATA2!B:B,REPORT2!C4,DATA2!S:S,"Outward"))

30/12/18

LED ADORE NIGHT LAMP 0.5W 6500K RHINO

ch.9898

SALE

2.00

7.00

16.00

jack999 · Jul 17, 2019

navic said:
This is because I have based other formulas on column C. Column C returns the Invoice number.
Now I've changed the formula in column B.
Hope it helped.

Sir report1 need with a particular customer details. So in report1 B2 customer name should come and details should shown below. So what changes i have to do for report1. And one more question can we get date asending order for both report.

navic · Jul 17, 2019

OK, try the following formulas

Code:

Cell E4
=SUMPRODUCT(DATA2!$K$2:$K$200,($A4=DATA2!$C$2:$C$200)*($B4=DATA2!$H$2:$H$200)*($C4=DATA2!$B$2:$B$200)*($B$2=DATA2!$H$2:$H$200)*(E$3=DATA2!$S$2:$S$200))

Cell F4
=SUMPRODUCT(DATA2!$K$2:$K$200,($A4=DATA2!$C$2:$C$200)*($B4=DATA2!$H$2:$H$200)*($C4=DATA2!$B$2:$B$200)*($B$2=DATA2!$H$2:$H$200)*(F$3=DATA2!$S$2:$S$200))

If you want to hide zero as a result then uses the IF function.

jack999 · Jul 17, 2019

Sir if you have time then one more help please. In the Report1 column (need with customer name. How to change the formula for that. Sir please check the sheet attached Report.
Thanks

jack999 · Jul 18, 2019

navic said:

OK, try the following formulas

Code:

Cell E4
=SUMPRODUCT(DATA2!$K$2:$K$200,($A4=DATA2!$C$2:$C$200)*($B4=DATA2!$H$2:$H$200)*($C4=DATA2!$B$2:$B$200)*($B$2=DATA2!$H$2:$H$200)*(E$3=DATA2!$S$2:$S$200))

Cell F4
=SUMPRODUCT(DATA2!$K$2:$K$200,($A4=DATA2!$C$2:$C$200)*($B4=DATA2!$H$2:$H$200)*($C4=DATA2!$B$2:$B$200)*($B$2=DATA2!$H$2:$H$200)*(F$3=DATA2!$S$2:$S$200))

If you want to hide zero as a result then uses the IF function.

Sir in report1 would like to change column B2 with (Customer Name) and that particular customer transactions need to come below. I apply the same formula as the Report2 not showing all transactions. Which formula need to change

Thanks and regards

navic · Jul 18, 2019

See the suggestion attached.

jack999 · Jul 18, 2019

Thank you sir. Thank you for your help. There is one correction.

Sir, in report1,from B4 to last record the particular customer each transactions (Item Names) should come. Customer Name only at the column B2 for customer selection.

Thanks and regards

navic · Jul 18, 2019

jack999 said:
Sir, in report1,from B4 to last record the particular customer each transactions (Item Names) should come

Does this Report1 different from Report2 in B4:B100 range.
I realized that all items must be the same. (ie withdrawn from DATA2!E2:E200)

I am now confused. Your text on me does not mean much. Please, set an example with the expected results (without formula), enter a manual example for Report1.
Look at how I got the expected results.

jack999 · Jul 19, 2019

Good day sir
Yes sir this is what I am expecting but there is no formula in sheet

navic · Jul 19, 2019

jack999 said:
Yes sir this is what I am expecting

I wanted to show you an example that you should set in post # 18.
It does not need much to explain, it is enough to enter the expected results. You have to understand that it is difficult for me to understand you and your imaginations.
The formulas are in the attached book of this post.

jack999 · Jul 19, 2019

Sir, Thank you for you Reply. Sir, when we delete or remove one row (suppose unnecessary record) from the Data2 sheet, all the data in the report1 go to disappeared. What is the reason. How can we overcome this problem. If we remove one unnecessary record form the data2 then all the report will not be changed. Is there any option to overcome this

navic · Jul 20, 2019

Then try just one ARRAY formula. Change certain parts of the formula that is referenced on certain columns. Please see attached book.

Code:

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

jack999 · Jul 20, 2019

Good Sir. Thank you so much.

navic · Jul 21, 2019

You are welcome

Correction in Array formula

Member

Attachments

Active Member

Member

Attachments

Active Member

Member

Member

Attachments

Active Member

Attachments

Active Member

Active Member

Attachments

Member

Active Member

Attachments

Member

Attachments

Member

Active Member

Attachments

Member

Attachments

Member

Active Member

Attachments

Member

Active Member

Attachments

Member

Active Member

Attachments

Member

Active Member

Attachments

Member

Active Member