Hi this is my first time posting.
I'm having difficulty figuring out the following on excel
Issue:
1) Column A is filled w/ duplicate values 1,1,1,2,2,3,3,3,3 3,4 etc
2) Column B holds various dates correlating to column A.
Desired Result:
the oldest date to appear in column C on the same row where that oldest date is located.
For example:
Value Date Result
1 9/1/2010 9/1/2010
1 9/31/2010
2 10/15/2008
2 1/11/2008 1/11/2008
2 4/15/2010
3 6/23/1995 6/23/1995
Can you explain why 1/11/2008 is deemed older than 10/15/2008 for Value 2 ?
If the data is - as implied - sorted by Number and then Date it should simply be a case of testing if the current Number is new (ie <> prior number) and repeating the date.
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Hi DonkeyOte,
Excuse the confusion, when I indicated the oldest date, I mean the earliest date. In this case it would be 1/11/2008.
Pls let me know if that clarifies my posting. Thanks!
Value Date Results
1 9/1/2010 9/1/2010
1 9/31/2010
2 10/15/2008
2 1/11/2008 1/11/2008
2 4/15/2010
3 6/23/1995 6/23/1995
Last edited by kwiz_ok; 09-30-2010 at 03:04 PM. Reason: not abel to attach
I'm confused also hers my work around
Hi Charlie_Howell,
I'm unable to open the attachement due to my excel being a older version. Would be able to advise on the formula utilized?
Apoligies for the confusion.
after looking at the problem i don't think I have the answer yet either...still playin with it though
@kwiz_ok
Sorry - my bad - I didn't read the dates properly.
above uses an INDEX:INDEX construct so as to avoid need for Array (you could equally use an OFFSET construct).Please Login or Register to view this content.
this approach does warrant data to be sorted by Value - this appears to be the case based on the sample
I think Ive figured it out ..take a look. Update : I'm removing the workbook that I posted because the formulas are inconsistant.
Last edited by Charlie_Howell; 09-30-2010 at 04:45 PM.
@Charlie_Howell - I don't think that will work I'm afraid - for ex. change C1 to 1-Sep-1979
the MIN dates are specific/local to the given values.
I believe the suggestion in post # 7 does what has been requested but I could be wrong (often am)
@ DonkeyOte
Wow! THANK YOU SO MUCH... I've been banging my head for 6 hours trying to figure this out! You're the man! Love this forum!
Hi,
I seem to run into a new issue regarding my orginal post. If I don't have duplicate number values, but duplicate text, is there a way to still identify the earliest date between the duplicate text values?
As mentioned previously (post # 7) the method provided requires the data be sorted by Column A in ascending order (the data types aren't really important)
It might be easier if you post a sample file.
Hi DonkeyOte,
I realize that after, I submitted my post, sorry. But, my senario has changed slightly, where I still have duplicate values, but within the duplicate values there are unique data on a seperate column that needs to be extracted. I'm facing difficulty figuring out if it's possible to extract such data.
Name Fruit Fruit (Concatenated) Results Sought
John Apple Apple Apple
John Apple Apple Apple
John Grape Grape Grape
John Orange Orange
John Orange Orange, Orange Orange, Orange
John Banana Banana
John Bananna Bananna,Orange, Orange Bananna, Orange, Orange
Mary Pinapple Pinapple
Mary Cherry Pinapple, Cherry Pinapple, Cherry
Mary Kiwi Kiwi
Mary Kiwi Kiwi, Kiwi Kiwi, Kiwi
Mary Mango Mango Mango
Mary Watermelon Watermelon Watermelon
Mary Peach Peach Peach
I've attached a example. I hope it helps in explaining my situation. Thanks for the help.
this is to all intents and purposes a new question - please post to an entirely new thread.
will do. Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks