#### xcruc1at3r

##### Member

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

how can i find the 2nd last date!

- Thread starter xcruc1at3r
- Start date

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

how can i find the 2nd last date!

the items are in the A

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.

=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:

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

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

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

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

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

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

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!!

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

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

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

should i attach the worksheet?!!

Hi ,

Thanks. That would help.

Narayan

Thanks. That would help.

Narayan

the link https://docs.google.com/spreadsheet/ccc?key=0Ah3qQ3ZqPOsPdDRZMzlwSnpNVndKOWhjMHFxZTlHbkE

i have added a note as well...

thank u

i have added a note as well...

thank u

Last edited by a moderator:

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

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:

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

can u load it on google doc!!

Hi ,

The link in Google Docs is :

https://docs.google.com/open?id=0B0KMpuzr3MTVYjNjNTMwYzgtNThiNC00NDQ2LTg1ZmUtOTUyYmVjYTRlMWU3

Can you explain how the data in the AVG. Days column should be calculated ?

Narayan

The link in Google Docs is :

https://docs.google.com/open?id=0B0KMpuzr3MTVYjNjNTMwYzgtNThiNC00NDQ2LTg1ZmUtOTUyYmVjYTRlMWU3

Can you explain how the data in the AVG. Days column should be calculated ?

Narayan

Last edited by a moderator:

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!!

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

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

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:

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