Index Match Max IsNumber...I think I need all these functions to get what I need?

[thomasart23]

HI,

I have a a data set that appears as so:

Site	Event Number	Date
lm	1	1/18/2017
lm	1	1/18/2017
lm	1	1/18/2017
lg	2	1/18/2017
lg	2	1/18/2017
lg	2	1/18/2017
sd	1	1/30/2017
sd	1	1/30/2017
sd	1	1/30/2017
zs	1	1/30/2017
zs	1	1/30/2017
zs	1	1/30/2017
lg	x	1/30/2017
lg	x	1/30/2017
lg	x	1/30/2017
lm	2	2/15/2017
lm	2	2/15/2017
lm	2	2/15/2017
lg		2/15/2017
lg		2/15/2017
lg		2/15/2017

<colgroup><col><col><col></colgroup><tbody>
</tbody>

The data set is mega long, so I am looking for a formula to continue down the bottom 3 cells in column B that will find the last event number assigned for the associated site on the previous date. So far I have used this array formula: =IF(AND(A20<>A19,C20<>C19),1+INDEX($B$2:$B19,MAX(($A20=$A$2:$A19)*MATCH(ROW($A$2:$A19),ROW($A$2:$A19)))),INDEX($B$2:$B19,MAX(($A20=$A$2:$A19)*MATCH(ROW($A$2:$A19),ROW($A$2:$A19)))))

It works great until I reach an "x" value (The "x" value are already assigned so the formula will be omitted from those cells as I add new data to the data set).

I am trying to figure out a way for the above formula to ignore the X values and assign the next number (2 to 3, 3 to 4, etc.)

Any advice would be greatly appreciated! Thanks!

Tom

 

[DILIPandey]

Hi Tom,

Can you share your expected result as well ?
last row is lg, blank, 2/15/2017... now what you need do you need last event number, which is 2 and date, which is 1/18/2017 for site "lg" ??


Regards,
DILIPandey

 

[thomasart23]

Hi, and thanks for the response. I am looking for a formula to copy down in column B, that will find the previous event number from the same site name (i.e. lg) from previous date (while ignoring the X), and add 1 to it. So in the case of lg with the first blank cell in column B, I need the formula to reference the above data and give me the last event number (which is 2) and add 1 to it, so it should display 3. The formula I provided works if there were no Xs in column B. But since there are, I'm looking for a way to have the formulas ignore those rows of data and provide the last numeric value. Thoughts? My apologies if I am not explaining too clearly. Thanks again for the help!

Tom

 

[Peter_SSs]

Assuming that first blank cell in column B is B20, try this in that cell and copy down.

=AGGREGATE(14,6,B$2:B19/((A$2:A19=A20)*(C$2:C19<C20)),1)+1


If it is possible that there is no earlier number for that Site, try

=IFERROR(AGGREGATE(14,6,B$2:B19/((A$2:A19=A20)*(C$2:C19<C20)),1),0)+1

 

[thomasart23]

Assuming that first blank cell in column B is B20, try this in that cell and copy down.

=AGGREGATE(14,6,B$2:B19/((A$2:A19=A20)*(C$2:C19<C20)),1)+1


If it is possible that there is no earlier number for that Site, try

=IFERROR(AGGREGATE(14,6,B$2:B19/((A$2:A19=A20)*(C$2:C19<C20)),1),0)+1

Hi Peter,

Thank you very much! This definitely helped me out. I am trying to understand your formula. what is the 14,6 function in the first part of your formula?

Again, thank you for the help. This has been very useful.

Tom

 

[Peter_SSs]

what is the 14,6 function in the first part of your formula?

The AGGREGATE function can be used to apply other functions but also do extra things like ignore errors or hidden rows etc.
Here is one reference to learn more.

14 means that we are effectively using the LARGE function. Here we will be looking for the LARGEst existing event number with conditions relating to Site & Date.
6 means to ignore errors.

Next we take all the event numbers and divide by (site = our site of interest)*(date < our date of interest)
For any site that is not the one we are looking for, or any date not < our date of interest, that multiplication will produce zero so when we try to do the division it produces a #DIV/0! error
In addition, rows with an "x" will produce a #VALUE! error since you cannot do a division with a text value.
The upshot is, all the rows that are of no interest will produce errors and the AGGREGATE function will return the largest remaining Event number, to which we add 1 for our result.

Hope that was some use & glad the formula helped.

 

[thomasart23]

The AGGREGATE function can be used to apply other functions but also do extra things like ignore errors or hidden rows etc.
Here is one reference to learn more.

14 means that we are effectively using the LARGE function. Here we will be looking for the LARGEst existing event number with conditions relating to Site & Date.
6 means to ignore errors.

Next we take all the event numbers and divide by (site = our site of interest)*(date < our date of interest)
For any site that is not the one we are looking for, or any date not < our date of interest, that multiplication will produce zero so when we try to do the division it produces a #DIV/0! error
In addition, rows with an "x" will produce a #VALUE! error since you cannot do a division with a text value.
The upshot is, all the rows that are of no interest will produce errors and the AGGREGATE function will return the largest remaining Event number, to which we add 1 for our result.

Hope that was some use & glad the formula helped.

Hi Peter! Thank you so much for taking the time to explain how these functions resolved my questions. I really appreciate it and look forward to applying this knowledge in the future!

Tom

 

[Peter_SSs]

Hi Peter! Thank you so much for taking the time to explain how these functions resolved my questions. I really appreciate it and look forward to applying this knowledge in the future!

Tom

You are very welcome.