LinkBack URL
About LinkBacks
A list is generated as per the sample workbook. There are purchase dates and respective payment dates. I want a formula to have the earliest payment date for each set of purchase dates as shown. The list can be of around 3000 rows.
A list is generated as per the sample workbook. There are purchase dates and respective payment dates. I want a formula to have the earliest payment date for each set of purchase dates as shown. The list can be of around 3000 rows.
Not sure if this is the best solution, but couldn't think a better alternative right now. So if anyone has a better option, please do share. Meanwhile I will also do some brain storming at my end to find a better alternative. Please let me know if the attached book solves your purpose or not.
Vikas B
Try this-attached
Yet another option
//Ola
Formula in cell E2 is an Array formula. It has to be confirmed with Ctrl+Shift+Enter not just Enter.
To all 3 responders in this thread:
A user recently raised the point that he (she) didn't have a newer version of Excel and, therefore, couldn't open any of the xlsx files being attached. So he was missing out on all the solutions being offered. Please keep that in mind (as well as the other reasons - restrictions on downloading, etc) when attaching files without mentioning what solution they contain. By showing your formulas (or describing your approach), in addition to attaching files, more users can benefit from your ideas.
As per moderator request:
=MIN($D11:INDEX(D11:$D$100,MATCH(1,IF(D11:$D$100="",1,""),0)-1))
CSE formula-confirm Control+Shift+Enter
Then just copy and paste to the cell you required
I will be careful next time I upload, it will be in els format.
I am thankful to everyone who spared their valuable time to help me. Getting cues from all of you, I managed to work out the solution exactly the way I wanted.
I am sharing the same here.
I used Column A as a helper column to number each set of purchases as 1, 2, 3 and so on. After putting 1 manually in A2,
I used the code in A3:
=IF(B3="","",IF(AND(B3<>"",B2=""),A1+1,A2))
and copied down to get numbered each set of purchases.
In G2, I used the code
=IF(OR(A2="",A1=A2),"",MIN(IF(ISNUMBER($D$2:$D$35)*($A$2:$A$35=A2),$D$2:D$35)))
Control+Shift+Enter, copied down to get the dates I wanted.
I am attaching the file herewith.
I am thankful to everyone once again.
sfshah
Last edited by sfshah; 07-09-2012 at 11:48 AM.
No, sorry sfshah, I wasn't referring to your file type. By all means upload the file type that you're using. And responders should also attach the same file type that you upload. My point was that responders should, in addition to attaching a file, mention in their posts what approach they used and, if it's formulas, show them. That way everyone can see the formulas and may have a better idea without relying on downloading.
I'm glad you got the solution you needed. And thanks for showing it. Sorry for the confusion I caused.
Please remember to mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Last edited by Cutter; 07-09-2012 at 11:14 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks