Forum Discussion

Colinahn's avatar
Colinahn
Copper Contributor
Dec 17, 2020

I really need a help with a formula!

Hi, I've been simply copying and pasting the value of date from sheet 1 (highlighted in green) to sheet 2 (highlighted in yellow) whenever the value of qty shifts to a different date. What I want t...
excel
Formulas and Functions
Macros and VBA
office 365
mtarler's avatar
mtarler
Silver Contributor
Dec 18, 2020

Colinahn   I agree with mathetes that there are still some questions and that it would be much more helpful if you uploaded a sample sheet.  My interpretation of the info is that the invoice numbers are A001 for the first invoice for product A1 and then A002, A003, ... for that product and similar for B1, C1, and D1, but what if you have product A2?  I also have same question about dates as mathletes since the dates shown on sheet 2 don't line up with dates on sheet 1.  That said based on what I believe you ASKED for, here is an equation that will work:

=INDEX(FILTER(Sheet1!$B$3:$G$3,INDEX(Sheet1!$B$4:$G$7,MATCH(C2,Sheet1!$A$4:$A$7,0),0)>0),COUNTIF($C$2:C2,C2))

you could even use a variation of this equation (or a more simplified lookup based on this date) to get the corresponding QTY just in case that might change on occasion.

 

Colinahn's avatar
Colinahn
Copper Contributor
Dec 21, 2020

Hi,

Thanks for the formula.

It works great!

Could you tell me the logic of this formula?

 

Since I only can use Excel on my work computer, I can't really upload a sample.

My personal laptop does not have Excel, and I have to purchase the subscription.

So, the only way I can think of is to take a screenshot.

 

 

  • mtarler's avatar
    mtarler
    Silver Contributor
    Dec 21, 2020

    Colinahn Sure. To start, the basic concept is that it filters the dates to only dates that have a quantity and then pick out those dates in order 1 by 1.  So, I reformatted the formula into multiple lines to ref the lines in the explanation

    =INDEX(
 FILTER(Sheet1!$B$3:$G$3,
   INDEX(Sheet1!$B$4:$G$7,
     MATCH(C2,Sheet1!$A$4:$A$7,0)
     ,0)
   >0),
 COUNTIF($C$2:C2,C2))

    so this starts with an INDEX (ln 1) we will skip for now and then a FILTER (ln 2), which is filtering the row of dates based on the criteria on lines 3-6.  This criteria is based on an INDEX of the whole data range (ln3).  The criteria for the index is a MATCH for the row (ln 4) and a 0 for the column (ln 5), which will return the whole row.  The MATCH (ln 4) uses the code in C2 to choose which row in the original data table to look at. So now that the INDEX (ln 3-5) returns the proper row it then applies the >0 criteria (ln 6) so that an array of true/false values based which columns have values and this is used to FILTER the list of Dates.  So now it selects which date using that first INDEX (ln 1) based on the this filtered list and choosing one based on the COUNTIF (ln 7).  The COUNTIF counts how many occurrences of the code in C2 is in the list up to this line (i.e. first occurrence=1=first date, second occurrence=2=second date, etc...)

    I hope that help break it down.  

Resources