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

Find the 2nd last date

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

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