Re-loaded the file with more clarification. Also wrote a formula. Would appreciate your help.
Re-loaded the file with more clarification. Also wrote a formula. Would appreciate your help.
Last edited by ansridhar; 02-18-2014 at 01:44 AM.
A simple way could be using a helper and hidden column. Let's say E. So in E2 and copy down use this.
Formula:Please Login or Register to view this content.
Then use this one to get your result.
Formula:Please Login or Register to view this content.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Any of those
=LOOKUP(2,1/(D2:D22<>""),(D2:D22))
=LOOKUP(2,1/(MONTH(C2:C40)=MONTH(O14)*(D2:D40<>"")),(D2:D40))
Where O14 is 1/2/2014
If you are happy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Robert
Could you please see the file enclosed and revisit the reply given by you. I am not getting.
Fotis
I am not getting the way you have suggested. Please see the file which I had enclosed. Entering =INDEX(D2:D14,MATCH(TRUE,TEXT(B1497,"mmm")&TEXT(B1497,"yy"),0)) in D15 in the file does not get the result.
Can you assist further?
Fotis
It is not B1497, it is C15 in the formulae.
Hi
Robert/Fotis
If I use this in D15, I get error?
=INDEX(D2:D14,SMALL(MATCH("*"&TEXT(C15,"mmm")&TEXT(C15,"yy"),D2:D14,0),COUNTIF(D2:D14,"*"&TEXT(C15,"mmm")&TEXT(C15,"yy"))),1) + Ctrl+Alt+Enter
Would appreciate your help.
Take a look to the example,pls.
Got the result. However, I want to write the formula in cell D15 and roll it down. If I drag the formula given by you, it is giving an error. Please look into it. Thanks.
In D15??. Really i don't understand what are you trying to do... Perhaps to "lock" the formula?
Formula:Please Login or Register to view this content.
Sir, it is an ongoing data feed sheet. As the days pass, anywhere between 5 to 50 data gets added in column C and other columns of no relevance to what I am asking. So as soon as the data gets added, at the end of the day, I have to search above in column D (for every new data that has been added in a day) the last match of "mmmyy". I get it displayed. In fact I would use the substitute command and increase the middle part (number part) of the D column data by 1 unit. For the next row, this would be the last match and so on.. Hopefullly, I have conveyed my requirement properly.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks