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

Adding between two dates to the equation

H

Hany ali

Active Member
Hell My Master I Want Your help to Add Two Dates For This Equation which in G1&H1 Cell form fm 01.12 till 31.12 Sheet ,and if result in Column I From Appollo For Natural Oils Sheet=0 ,Don't Get Any Data
Code: 
=IFERROR(INDEX('Appollo For Natural Oils'!$A$249:$A$777,AGGREGATE(15,6,ROW($A$1:$A$7166)/(MATCH('Appollo For Natural Oils'!$A$249:$A$777&'Appollo For Natural Oils'!$D$249:$D$777,'Appollo For Natural Oils'!$A$249:$A$777&'Appollo For Natural Oils'!$D$249:$D$777,0)=ROW($A$1:$A$7166)),ROWS($2:2))),"")
in A , B & C Column ..To Get Data From Appollo For Natural Oils Sheet To fm 01.12 Till 31.12 Sheet
and If Possible this function work to get Data From Appollo For Natural Oils Sheet First and when Data Finish to Get Data from City Tour Sheet
Note : this equation to Get Unquie Data
 

Attachments

  • Unquie Data.xlsx
    134.1 KB · Views: 11
Last edited:
Hello Hany
Add two dates in Google Sheets using cells G1 and H1 and use the resulting date

Code: 
=IFERROR(INDEX('Appollo For Natural Oils'!$A$249:$A$777, AGGREGATE(15, 6, ROW($A$1:$A$7166)/(MATCH('Appollo For Natural Oils'!$A$249:$A$777&'Appollo For Natural Oils'!$D$249:$D$777, 'Appollo For Natural Oils'!$A$249:$A$777&'Appollo For Natural Oils'!$D$249:$D$777, 0)=ROW($A$1:$A$7166)), ROWS($2:2))), "")

For your additional request of switching to the 'City Tour' Sheet if the 'Appollo For Natural Oils' data is exhausted, you can use an IF statement along with the same INDEX and AGGREGATE functions:

Code: 
=IFERROR(IF(ROWS($2:2) <= COUNT('Appollo For Natural Oils'!$A$249:$A$777),
INDEX('Appollo For Natural Oils'!$A$249:$A$777, AGGREGATE(15, 6, ROW($A$1:$A$7166)/(MATCH('Appollo For Natural Oils'!$A$249:$A$777&'Appollo For Natural Oils'!$D$249:$D$777, 'Appollo For Natural Oils'!$A$249:$A$777&'Appollo For Natural Oils'!$D$249:$D$777, 0)=ROW($A$1:$A$7166)), ROWS($2:2))),
INDEX('City Tour'!$A$1:$A$100, AGGREGATE(15, 6, ROW('City Tour'!$A$1:$A$100)/(MATCH('City Tour'!$A$1:$A$100&'City Tour'!$D$1:$D$100, 'City Tour'!$A$1:$A$100&'City Tour'!$D$1:$D$100, 0)=ROW('City Tour'!$A$1:$A$100)), ROWS($2:2)-COUNT('Appollo For Natural Oils'!$A$249:$A$777))))


This formula checks if the row number is within the range of the 'Appollo For Natural Oils' data, and if not, it switches to the 'City Tour' sheet.

Please let know after testing.
 
Thanks Alot For Your Answer ,but You Can To See the result after put your function,This is not the desired result
 

Attachments

  • Unquie Data.xlsx
    144.6 KB · Views: 3
Last edited:
Hany ali said:
Thanks Alot For Your Answer ,but You Can To See the result after put your function,This is not
Click to expand...
Try this!

Code: 
=IFERROR(IF(ROWS($2:2) <= COUNT('Appollo For Natural Oils'!$A$249:$A$777),
    INDEX('Appollo For Natural Oils'!$A$249:$A$777, AGGREGATE(15, 6,
        ROW($A$249:$A$777)/(MATCH('Appollo For Natural Oils'!$A$249:$A$777&'Appollo For Natural Oils'!$D$249:$D$777,
        'Appollo For Natural Oils'!$A$249:$A$777&'Appollo For Natural Oils'!$D$249:$D$777, 0)=ROW($A$249:$A$777)),
        ROWS($2:2))),
    INDEX('City Tour'!$A$1:$A$100, AGGREGATE(15, 6,
        ROW('City Tour'!$A$1:$A$100)/(MATCH('City Tour'!$A$1:$A$100&'City Tour'!$D$1:$D$100,
        'City Tour'!$A$1:$A$100&'City Tour'!$D$1:$D$100, 0)=ROW('City Tour'!$A$1:$A$100)),
        ROWS($2:2)-COUNT('Appollo For Natural Oils'!$A$249:$A$777))
    )

please make sur that the ranges and column references are correct according to your actual data. If the result is still not as expected, please let me know.Am not looking at your Excel file as unable to open.
 
Thank you for your kind response, but when setting the equation, no results are given
 

Attachments

  • Screenshot 2023-12-25 210603.png
    Screenshot 2023-12-25 210603.png
    103.1 KB · Views: 5

So it needs to work on both versions?​

Please Yes, If Possibe
Also, does it have to be a formula rather than say Power Query?
Yes,I Hope To be a Formula Not Power Query
 
Last edited:
I can't see a practical way to do that with a formula in versions pre 365 if you need a unique list from two separate sheets.
 
Yes I need a unique list from two separate sheets
.This is Of Course the desired results
if Possibe 2019 version
 

Attachments

  • Unquie Data.xlsx
    124.7 KB · Views: 8
Hany ali said:
Yes I need a unique list from two separate sheets
.This is Of Course the desired results
if Possibe 2019 version
Click to expand...
A long formula for the old Excel version.

1] In A8, formula copied down:
=IFERROR(IFERROR(INDEX('Appollo For Natural Oils'!$A$1:$A$1000,AGGREGATE(15,6,ROW($A$1:$A$1000)/(MATCH('Appollo For Natural Oils'!$A$1:$A$1000&'Appollo For Natural Oils'!$D$1:$D$1000,'Appollo For Natural Oils'!$A$1:$A$1000&'Appollo For Natural Oils'!$D$1:$D$1000,0)=ROW($A$1:$A$1000))/('Appollo For Natural Oils'!$A$1:$A$1000>=$G$1)/('Appollo For Natural Oils'!$A$1:$A$1000<=$H$1),ROWS($2:2))),INDEX('City Tour'!$A$1:$A$1000,AGGREGATE(15,6,ROW($A$1:$A$1000)/(MATCH('City Tour'!$A$1:$A$1000&'City Tour'!$D$1:$D$1000,'City Tour'!$A$1:$A$1000&'City Tour'!$D$1:$D$1000,0)=ROW($A$1:$A$1000))/('City Tour'!$A$1:$A$1000>=$G$1)/('City Tour'!$A$1:$A$1000<=$H$1),ROWS($2:2)-ROUND(SUMPRODUCT((('Appollo For Natural Oils'!$A$1:$A$1000>=$G$1)*('Appollo For Natural Oils'!$A$1:$A$1000<=$H$1)*('Appollo For Natural Oils'!$A$1:$A$1000<>""))/COUNTIFS('Appollo For Natural Oils'!$A$1:$A$1000,'Appollo For Natural Oils'!$A$1:$A$1000&"",'Appollo For Natural Oils'!$D$1:$D$1000,'Appollo For Natural Oils'!$D$1:$D$1000&"")),0)))),"")

2] The other formulas please check attachment.

1703863777486.png
 

Attachments

  • Unquie Data (BY) (R1).xlsx
    145.1 KB · Views: 7
Last edited:
Thank you very much. You are truly a genius. This is exactly what is needed

Is it possible not to get Data whose value is equal to zero?
 
If an equation could be easier than this one because it is very long and makes the file heavy, Because will Open New Sheet each month in the file
 
Last edited:
Unique dates:

=UNIQUE(VSTACK(FILTER('Appollo For Natural Oils'!$A$3:$A$422,('Appollo For Natural Oils'!$A$3:$A$422>=G1)*('Appollo For Natural Oils'!$A$3:$A$422<='fm 01.12 Till 31.12'!H1)),FILTER('City Tour'!A3:A246,('City Tour'!A3:A246>=G1)*('City Tour'!A3:A246<=H1))))
 
Sorry - logic error. If you can rearrange the two output columns so that the date and name are next to each other and then add the cum column to the right, you can get both the first two columns with:

=LET(data,VSTACK('Appollo For Natural Oils'!$A$3:$D$422,'City Tour'!A3:D246),filterdata,FILTER(data,(INDEX(data,0,1)>=G1)*(INDEX(data,0,1)<=H1)),UNIQUE(CHOOSECOLS(filterdata,1,4)))
 
You must log in or register to reply here.
Back
Top