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

Attachments

navic

Member
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

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

Attachments

navic

Member
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

Member
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

Member
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
 

Attachments

navic

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

Attachments

navic

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

Attachments

jack999

Member
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.
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 NAMELED ADORE NIGHT LAMP 0.5W 6500K RHINO
Op. Balance
0
DateItem NameInvoice No.DescriptionInwardsOutwardsBalance
16/11/18LED ADORE NIGHT LAMP 0.5W 6500K RABBIT5622218218PURCHASE
12.00​
0.00​
12.00
10/12/19LED ADORE NIGHT LAMP 0.5W 6500K BEARCH.1738SALE
0.00​
1.00​
11.00
04/01/19LED ADORE NIGHT LAMP 0.5W 6500K BEARCH.1743SALE
0.00​
1.00​
10.00
12/12/18LED ADORE NIGHT LAMP 0.5W 6500K RHINOCH.6001SALE
0.00​
1.00​
9.00
21/12/18LED ADORE NIGHT LAMP 0.5W 6500K BEARCH.1903SALE
0.00​
2.00​
7.00
04/01/19LED ADORE NIGHT LAMP 0.5W 6500K BEARCH.1910SALE
0.00​
1.00​
6.00
27/01/19LED ADORE NIGHT LAMP 0.5W 6500K RHINOCH.6005SALE
0.00​
2.00​
4.00
04/01/19LED ADORE NIGHT LAMP 0.5W 6500K PENGUINHIL/1819/38SALE
0.00​
1.00​
3.00
24/01/19LED ADORE NIGHT LAMP 0.5W 6500K TIGERHIL/1819/61SALE
0.00​
2.00​
1.00
28/06/19LED ADORE NIGHT LAMP 0.5W 6500K TENNISHAV/1819/85SALE
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
 
Last edited:

jack999

Member
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 RHINOch.9898SALE
2.00​
7.00​
16.00​
 

Attachments

Last edited:

jack999

Member
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

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

Attachments

jack999

Member
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
 

jack999

Member
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

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

Attachments

navic

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

Attachments

jack999

Member
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
 
Last edited:

navic

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

Attachments

Top