# Find the 2nd last date

#### xcruc1at3r

##### Member
this is the array formula am using to find the last date

=INDEX(Date,SUMPRODUCT(MAX((item=\$A4)*ROW(item)))-ROW(Date)+1)

how can i find the 2nd last date!

#### Mike86

##### Active Member
If you mean second earliest date this would work:

=Small(DateVal,2)

If you mean the next to the most recent date:

=large(DateVal,2)

With DateVal being your defined range for dates.

#### xcruc1at3r

##### Member
well mike with the Item reference !...that xyz item was sold at 2nd earliest dated xyz date and last dated

the items are in the A

#### Mike86

##### Active Member
Something like this will take care of the individual item restriction:

=SMALL(--(Items=Selection)*DateVal,COUNTIF(Items,"<>"&"")-COUNTIF(Items,"="&Selection)+2)

Use Cntrl-Shift-Enter for the array function

The item descriptions are in Items.

Selection is the item you're looking for.

DateVal is the range of Date Values and should match up with the Items range.

The first CountIF counts the number of entries in Items.

The second CountIF counts the number of Selected Items in Items.

The "+2" gives you the second smallest.

Last edited by a moderator:

#### xcruc1at3r

##### Member
Category...1st Items.....2ndLastIteams...Last Iteams

hats......3-Feb-11........Value........ 8-Feb-11

Shoes.....6-Feb-11........Value.........20-Feb-11

socks.....13-Feb-11.......Value ........13-Feb-11

Sequential List

Item Dates

Hats 3-Feb-11

Shoes 6-Feb-11

Hats 8-Feb-11

Socks 13-Feb-11

Shoes 19-Feb-11

Shoes 20-Feb-11

Hats 5-Feb-11

Socks 21-Feb-11

where items are as items... dates are as dates

this the the example of my actual looking data!...

#### NARAYANK991

##### Excel Ninja
Hi ,

Try the following formula :

=INDEX(\$B\$5:\$B\$12,LARGE(ROW(\$A\$5:\$A\$12)*(\$A\$5:\$A\$12="Hats"),1)-ROW(\$A\$5)+1)

B5:B12 is the range for the Dates ; A5:A12 is the range for the Items.

Changing the 1 to 2 or 3 within the LARGE function , will give you the largest or second largest or third largest value.

Change the "Hats" to any of the other items for their corresponding dates.

If you want to get the least / first date , the following formula can be used :

=INDEX(\$B\$5:\$B\$12,LARGE(ROW(\$A\$5:\$A\$12)*(\$A\$5:\$A\$12="Hats"),COUNTIF(\$A\$5:\$A\$12,"Hats"))-ROW(\$A\$5)+1)

Narayan

#### xcruc1at3r

##### Member
its given #value error....

=INDEX(\$B\$8:\$B\$15,LARGE(ROW(\$A\$8:\$A\$15)*(\$A\$8:\$A\$15="Hats"),1)-ROW(\$A\$4)+1)

added date as B8:b15 & A8:A15 as Item

The Product list start from A4 till A6

=INDEX(Date,LARGE(ROW(item)*(item="Hats"),1)-ROW(\$A\$4)+1)

#### NARAYANK991

##### Excel Ninja
Hi ,

Actually it is my mistake ; the formula is to be entered using CTRL SHIFT ENTER. Sorry.

Using ROW(\$A\$4) instead of ROW(\$A\$5) will not give the correct result.

Narayan

#### xcruc1at3r

##### Member
Its ok no need of saying sorry bro ...Using CTRL SHIFT ENTER is given #ref Error

#### NARAYANK991

##### Excel Ninja
Hi ,

I'll just post whatever I've done to get the results :

The data is in the range A5:B12 as follows :

Hats .... 3-Feb-11

Hats .... 5-Feb-11

Shoes ... 6-Feb-11

Hats .... 8-Feb-11

Socks ... 13-Feb-11

Shoes ... 19-Feb-11

Shoes ... 20-Feb-11

Socks ... 21-Feb-11

The result table is in the range E14:H17 ; the item names "Hats" , "Shoes" and "Socks" are in the range E15:E17 ; the text values "Earliest" , "Second" , "Latest" are in the range F14:H14.

The formulae are as follows :

F15 : =INDEX(\$B\$5:\$B\$12,LARGE(ROW(\$A\$5:\$A\$12)*(\$A\$5:\$A\$12="Hats"),COUNTIF(\$A\$5:\$A\$12,"Hats"))-ROW(\$A\$5)+1)

G15 : =INDEX(\$B\$5:\$B\$12,LARGE(ROW(\$A\$5:\$A\$12)*(\$A\$5:\$A\$12="Hats"),2)-ROW(\$A\$5)+1)

H15 : =INDEX(\$B\$5:\$B\$12,LARGE(ROW(\$A\$5:\$A\$12)*(\$A\$5:\$A\$12="Hats"),1)-ROW(\$A\$5)+1)

For "Shoes" and "Socks" , repeat the above formulae with the appropriate changes.

The results are as follows :

3-Feb-11 ..... 5-Feb-11 ..... 8-Feb-11

6-Feb-11 ..... 19-Feb-11 ..... 20-Feb-11

13-Feb-11 ..... 13-Feb-11 ..... 21-Feb-11

Narayan

#### xcruc1at3r

##### Member
its working!... but when i change the date values in the data range after placing the formula as told....

the data resulted as you data..

.........Earliest........Second.........Latest

Hats.....3-Feb-11 ..... 5-Feb-11 ..... 8-Feb-11

and when i change.. Hats value in B5 to 5-feb-11, B6 to 4-feb-11 & B8 to 2-Feb-11

so it results into :

.........Earliest........Second.........Latest

Hats.....05-Feb-11......04-Feb-11......02-Feb-11

well it should reflect the correct entry!! but its not!!

#### NARAYANK991

##### Excel Ninja
Hi ,

OK. What I meant by earliest was just by way of which one is the earliest entry , in row order , not in date order. If you want in date order , then obviously using the ROW function will not be right.

Is it possible you can sort the data range ?

Narayan

#### xcruc1at3r

##### Member
well Cannot sort! well this is the sample data that is related to the main database!... and there are 900 products!...and have to sort by the product Name!..order! not by date!.. ((

#### NARAYANK991

##### Excel Ninja
Hi ,

Sorry for the delayed reply.

Try the following formula for the various results , with the appropriate changes :

=LARGE(((\$B\$5:\$B\$12)*(--(\$A\$5:\$A\$12="Hats"))),1)

will give you the latest date for the entry "Hats" ; changing the 1 to 2 should give you the second date , while putting 3 will give you the third latest date and so on.

Remember to enter this with CTRL SHIFT ENTER.

Narayan

#### xcruc1at3r

##### Member
Can I USe this array for Dynamic Range such as C:C & A:A??

Shows an error of #N/A as i have changed the formula to

{=LARGE(((DATABASE!\$C\$3:\$C\$800)*(--(\$B\$3:\$B\$749=B3))),3)}

AND SAME GOES FOR

{=LARGE(((DATABASE!\$C\$2:\$C\$800)*(--(\$B\$3:\$B\$749="AMLG122351"))),3)}

If the range is not Same for both the Column based rows so it shows error....

the date range will increase will not be the same as the main inventory of products...cause the product range is still B749 and C will increase as the data is entered....

#### xcruc1at3r

##### Member
should i attach the worksheet?!!

#### NARAYANK991

##### Excel Ninja
Hi ,

Thanks. That would help.

Narayan

#### NARAYANK991

##### Excel Ninja
Hi ,

Sorry about the delay. Can you check out the uploaded worksheet ?

https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F!125

1. I have added some range names , just for convenience.

2. The hour meter difference column is being filled in through a macro.

3. The three latest dates are being filled in through formulae.

Can you specify what should appear in the DAYS column ?

Narayan

Last edited by a moderator:

#### xcruc1at3r

##### Member
in AVG. DAYS? column?~~!!~~

well i have restricted internet connection in my office so i cannot download it from Skydrive...

can u load it on google doc!!

#### xcruc1at3r

##### Member
there are 800 product codes...and i have a inventory on the sheet1 and sheet2 is the database Okay...

for e.g. if apjh-1234 dated 15th jan, 11 is on the 1 row with hr meter 200

and the same product code comes randomly in the list could be anywhere in the list could be on the 2nd row or the 10000row!...when is appears in the row so the DIFFERENCE of The Newly added apjh-1234 27th march,11 Hr meter of 560

so the difference difference of the of current(560) - (200) previous =360 should appear in front of the Previous Entry..and in DATABASE SHEET the No. of Days that after how many days the news activity took place!...SO in INVENTORY sheet It reflects that apjh-1234's Avg. no of days based on the days to days activity.

i hope i made myself clear! if u understand hindi so i can try explaining in hindi!!

#### NARAYANK991

##### Excel Ninja
Hi ,

I have not understood you. Let us consider the code GCEG-1155 ; can you set a filter in your DATABASE tab , so that you can see only entries with this code ?

The dates pertaining to this code are as follows :

02/03/2011 , 07/03/2011 , 12/03/2011 , 21/03/2011 , 06/06/2011 , 14/06/2011 , 21/06/2011 , 03/10/2011 and 17/10/2011

Can you say what numbers should appear against each of these entries in the column labelled No. of Days , in the DATABASE tab , and also what should appear against the entry in the INVENTORY tab , in the column labelled Avg. Days ?

Narayan

#### xcruc1at3r

##### Member
GCEG-1155; Activity...in Column H in database sheet.. reflects the difference of days for eg..

1 activity : GCEG-1155.....02/03/2011......result > 5 is the difference b/w 1 & 2 activity

2 activity : GCEG-1155.....7/3/2011....... result > 5 is the difference b/w 2 & 3 activity

3 activity : GCEG-1155.....12/03/2011..... so on......i hope this time am bit clear!! bro... in the inventory sheet..in the column Avg. Days should result the over all Avg. Days based on the Avg of Column H again the GCEG-1155..

Last edited by a moderator:

#### NARAYANK991

##### Excel Ninja
Hi ,

Thanks for the clarifications. It's clear now. Hopefully , a solution will result ! I'll get back to you by this evening ( it's 9 in the morning now ).

Narayan